How to calculate distance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to calculate distance

Author

Topic

erikkl2003
Starting Member

14 Posts

Posted - 2005-12-14 : 10:56:40

I got these two procedures from another portion of the sqlteam fourm.. from a post about three years ago..

I am in need to BIG TIME help on this one...

1. I need to find the distance between two given zip codes ( with the supplied latitude and longitude)

2. I need to find all of the zipcodes with in a given radius..

=======================================================
Then i thought i was dreaming and i found this little snippet..Only one problem.... i do not have time to go to college and learn trig in order to see if these two procedures are valid. What i am in need of is two procedures.... i do have the latitude and longitude fields in my zip table in the database.

My question here is.. for someone who understands trig.. to see if these two procedures or correct? or if i should or should not user them.

help,,,help!!!

erik

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

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

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

If you wait untill tomorrow; you will have no future.

erikkl2003
Starting Member

14 Posts

Posted - 2005-12-15 : 00:57:52

I am getting closer on my distance from one zip to another.. but the mileage does not seem to be right

can someone please help me with this..?
================================================================
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure ZipCode_GetDistanceBetween

@ZipCode_Start as VARCHAR (10),
@ZipCode_End as VARCHAR (10)

AS

DECLARE @LatStart FLOAT, @LongStart FLOAT, @LatEnd FLOAT, @LongEnd FLOAT

SET @LatStart = (SELECT Latitude FROM Zips WHERE ZipCode = @ZipCode_Start)
SET @LongStart = (SELECT Longitude FROM Zips WHERE ZipCode = @ZipCode_Start)
SET @LatEnd = (SELECT Latitude FROM Zips WHERE ZipCode = @ZipCode_End)
SET @LongEnd = (SELECT Longitude FROM Zips WHERE ZipCode = @ZipCode_End)

exec Distance @LatStart,@LongStart,@LatEnd,@LongEnd

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------------------------------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float

as

begin

declare @DegToRad as float
declare @Ans as float
declare @Miles as float

set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If you wait untill tomorrow; you will have no future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources