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  |
|
|
|