| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 11/25/2003 : 07:40:30
|
Richard writes "I have store proc that calculates distance in miles between 2 sets of latitudes and longitudes and I also have a table called [places] that contain zipcodes, lats, & longs. I need to combine the two in some way so I can pass 2 parameters such as zipcode and radius (let's say 11214, 3) and return a record set of all zipcodes within that given mile radius (range.) The output should be something like the following if I pass it 11214, 3:
11214 1
11228 3
11219 2
etc.... Please help!? I am running MS-SQL2000 and will be calling this code from ASP.
Thanks!
Richard" |
|
|
SamC
White Water Yakist
USA
3467 Posts |
Posted - 11/25/2003 : 08:26:26
|
| It's easier to show the modifications if you post some of your code. |
 |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 11/25/2003 : 08:40:24
|
just edit your stored proc (or create a new copy) to include a join to this table. but, as Sam says, some code would be helpful.
- Jeff |
Edited by - jsmith8858 on 11/25/2003 08:40:51 |
|
|
|
anteros511
Starting Member
USA
3 Posts |
Posted - 11/26/2003 : 15:25:48
|
--**************************************
-- Name: Procedure to calc miles distance from two points on earth
-- Description:This procedure is used to calc miles distance from
-- two points on earth. You can get latitude and longitude for places on
-- earth, then just use this procedure to calc how many miles are in between.
--
-- This code is copyrighted and has limited warranties.
-- Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.644/lngWId.5/qx/vb/scripts/ShowCode.htm
-- for details.
--**************************************
CREATE Proc dbo.sp_MilesLatLong (
@P_Lat1 decimal(8,4) =0 ,
@P_Long1 decimal(8,4) =0 ,
@P_Lat2 decimal(8,4) =0 ,
@P_Long2 decimal(8,4) =0 ,
@P_DiffMiles decimal(10,2) =0 output
)
As
Begin
/*************************************************************/
/* Routine to calc miles distance from two points on earth */
/* specified in latitude/longitude pairs.*/
/* Inputs are "degrees.minutes" ie: 33.0654 */
/*************************************************************/
set nocount on
declare @Lat1 decimal(28,10)
declare @Long1 decimal(28,10)
declare @Lat2 decimal(28,10)
declare @Long2 decimal(28,10)
declare @d decimal(28,10)
/* Default return value */
set @P_DiffMiles = 0
/* Convert to radians */
set @Lat1 = @P_Lat1 / 57.2958
set @Long1 = @P_Long1 / 57.2958
set @Lat2 = @P_Lat2 / 57.2958
set @Long2 = @P_Long2 / 57.2958
/* Calc distance */
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))
/* Convert to miles */
if @d <> 0
begin
set @P_DiffMiles = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
end
GO
/* DECLARE @miles int
/* EXEC @miles = sp_MilesLatLong 41.598994, -73.996119, 41.768420, -73.960450
/* SELECT miles = @miles
|
|
|
|
anteros511
Starting Member
USA
3 Posts |
Posted - 11/26/2003 : 15:26:46
|
Sample data from my 'Places' table:
AL 35085 32.965120 -86.744053
AL 35087 34.303718 -86.583234
AL 35089 32.941708 -86.060988
AL 35091 33.771090 -86.806727
AL 35094 33.530698 -86.555065
AL 35096 33.605233 -86.120796
AL 35097 33.896526 -86.630569
AL 35098 34.120006 -87.050758
AL 350HH 33.464476 -86.577571
AL 350XX 32.978292 -86.499261
AL 35111 33.279946 -87.101488
AL 35112 33.681899 -86.482375
AL 35114 33.225377 -86.864434 |
|
|
|
ehorn
Flowing Fount of Yak Knowledge
USA
1632 Posts |
Posted - 11/26/2003 : 19:51:48
|
CREATE TABLE places
(
statecode char(2),
zipcode char(5),
lat float,
long float
)
insert into places select 'AL', '35085' ,32.965120 ,-86.744053
union all select 'AL', '35087', 34.303718 ,-86.583234
union all select 'AL', '35089', 32.941708 ,-86.060988
union all select 'AL', '35091', 33.771090 ,-86.806727
union all select 'AL', '35094', 33.530698 ,-86.555065
union all select 'AL', '35096', 33.605233 ,-86.120796
union all select 'AL', '35097', 33.896526 ,-86.630569
union all select 'AL', '35098', 34.120006 ,-87.050758
union all select 'AL', '350HH', 33.464476 ,-86.577571
union all select 'AL', '350XX', 32.978292 ,-86.499261
union all select 'AL', '35111', 33.279946 ,-87.101488
union all select 'AL', '35112', 33.681899 ,-86.482375
union all select 'AL', '35114', 33.225377 ,-86.864434
GO
CREATE PROC up_FindZipCodesWithinRadius
@ZipCode char(5) ,
@GivenMileRadius int
AS
SET NOCOUNT ON
DECLARE @lat1 float,
@long1 float
SELECT @lat1= lat,
@long1 = long
FROM places
WHERE zipcode = @ZipCode
SELECT ZipCode ,MileRadius
FROM
(
SELECT ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))), 2)) /
((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))))) MileRadius
FROM Places
) a
WHERE a.MileRadius <= @GivenMileRadius
AND ZipCode <> @ZipCode
ORDER BY MileRadius
GO
EXEC up_FindZipCodesWithinRadius '35085',20
GO
DROP PROC up_FindZipCodesWithinRadius
--DROP TABLE places
|
Edited by - ehorn on 11/26/2003 20:04:35 |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 11/26/2003 : 20:06:28
|
quote:
This code is copyrighted and has limited warranties.
Oh please!!! Copywriting Maths now are we.. F$^%&ing morons....
Look out ehorn, Pythagoras is going to start suing!!!!
DavidM
"SQL-3 is an abomination.." |
|
|
|
anteros511
Starting Member
USA
3 Posts |
Posted - 11/28/2003 : 23:05:02
|
DUDE!! You rock-- thank you sssooo much!!!
quote:
Originally posted by ehorn
CREATE PROC up_FindZipCodesWithinRadius
@ZipCode char(5) ,
@GivenMileRadius int
...
|
|
|
|
SqlStar
Posting Yak Master
USA
121 Posts |
Posted - 11/29/2003 : 03:35:11
|
hi anteros511,
Dont give prefix as "sp_", when create stored procedure.Its not adviseable.
":-) IT Knowledge is power :-)" |
|
|
| |
Topic |
|
|
|
Return a result set from a table AND a stored procedure together?