SQL Server Performance Forum – Threads Archive

lon/lat radius function

I have a table that hold the lon/lat and I need to determine 1, 5, mile radius. What would be the best way to go about this? I have a function, but not sure how I should use it. Thanks Susan
/*
* History: 14-Nov-05 CNH Simplified to resolve Divide By Zero problems
* 29-Mar-02 DKS Created by Deepak K Srinivasan
*
* Test Data:
*
* City Latt Long
* —- —- —-
* NYC 40.77 74
* SF 37.75 122.68
* Oakland 37.73 122.22
* Burbank 34.2 118.37
*/
CREATE FUNCTION dbo.CalculateDistance(@LatDeg1 FLOAT, @LonDeg1 FLOAT, @LatDeg2 FLOAT, @LonDeg2 FLOAT)
RETURNS FLOAT AS BEGIN
DECLARE @EarthRadiusMiles AS FLOAT,
@LatRad1 AS FLOAT,
@LonRad1 AS FLOAT,
@LatRad2 AS FLOAT,
@LonRad2 AS FLOAT,
@DotProd AS FLOAT SET @EarthRadiusMiles = 3958.755 — Volumic Radius of the Earth in Miles SET @LatRad1 = RADIANS(ISNULL(@LatDeg1, 0.0))
SET @LonRad1 = RADIANS(ISNULL(@LonDeg1, 0.0))
SET @LatRad2 = RADIANS(ISNULL(@LatDeg2, 0.0))
SET @LonRad2 = RADIANS(ISNULL(@LonDeg2, 0.0)) SET @DotProd = SIN(@LatRad1) * SIN(@LatRad2) + COS(@LatRad1) * COS(@LatRad2) * COS(@LonRad1 – @LonRad2) — T-SQL provides ACOS. So, there is no need to implement it via ATAN: RETURN @EarthRadiusMiles * ACOS(@DotProd)
END

---

Check this SP
CREATE PROCEDURE Distance
@Lat1 float,@lon1 float,@lat2 float , @lon2 float , @dt float OUTPUT
AS
declare @theta float, @dist float
set @theta = @lon1 – @lon2 set @dt = (sin(@Lat1 * pi() / 180)) * (sin(@Lat2 * pi() / 180)) + (cos(@Lat1 * pi() / 180)) * (cos(@Lat2 * pi() / 180)) * (cos(@theta * pi() / 180)) If Abs(@dt) <> 1
begin
set @dt = pi()/ 2 – atan( @dt / SQRT(1 – @dt * @dt))
end
Else
begin
if @dt = -1
begin
set @dt = pi()
end end if @dt <> 1
begin
set @dt = @dt * 180 / pi()
set @dt = @dt * 60 * 1.1515
end
else
set @dt = 0 GO
—————————————-

---

have a table with only one column each for longitude and latitude. They will be passing the sa_property_id which has a long and lat listed. I need to show what else is withing 1 mile or 5 miles of that properties long/lat –Table
CREATE TABLE [dbo].[PropertyDetails] (
[SA_PROPERTY_ID] [int] NOT NULL ,
[SA_X_COORD] [float] NULL ,
[SA_Y_COORD] [float] NULL ,
[Longitude] [float] NULL ,
[Latitude] [float] NULL ,
) ON [PRIMARY]
GO Data output from above table SASA_PROPERTY_ID Longitude Latitude
————– —————————————————– —————————————————–
23790208 -120.619821 39.568587999999998 (1 row(s) affected) Passed parameter = SA_PROPERTY_ID
Need list of matching records within 1 mile of above record Would this be the best query for it? how do I define the radius? I also have a function, but am not sure it can do what I need it to.
/*
* History: 14-Nov-05 CNH Simplified to resolve Divide By Zero problems
* 29-Mar-02 DKS Created by Deepak K Srinivasan
*
* Test Data:
*
* City Latt Long
* —- —- —-
* NYC 40.77 74
* SF 37.75 122.68
* Oakland 37.73 122.22
* Burbank 34.2 118.37
*/
CREATE FUNCTION dbo.CalculateDistance(@LatDeg1 FLOAT, @LonDeg1 FLOAT, @LatDeg2 FLOAT, @LonDeg2 FLOAT)
RETURNS FLOAT AS BEGIN
DECLARE @EarthRadiusMiles AS FLOAT,
@LatRad1 AS FLOAT,
@LonRad1 AS FLOAT,
@LatRad2 AS FLOAT,
@LonRad2 AS FLOAT,
@DotProd AS FLOAT SET @EarthRadiusMiles = 3958.755 — Volumic Radius of the Earth in Miles SET @LatRad1 = RADIANS(ISNULL(@LatDeg1, 0.0))
SET @LonRad1 = RADIANS(ISNULL(@LonDeg1, 0.0))
SET @LatRad2 = RADIANS(ISNULL(@LatDeg2, 0.0))
SET @LonRad2 = RADIANS(ISNULL(@LonDeg2, 0.0)) SET @DotProd = SIN(@LatRad1) * SIN(@LatRad2) + COS(@LatRad1) * COS(@LatRad2) * COS(@LonRad1 – @LonRad2) — T-SQL provides ACOS. So, there is no need to implement it via ATAN: RETURN @EarthRadiusMiles * ACOS(@DotProd)
END

---

Seehttp://mathforum.org/library/drmath/view/51879.html Keith Payne
Technical Marketing Solutions
www.tms-us.com

---

i suggest that for the sake of SQL search efficiency, the sarg specifies the lat & long fitting in a square box containing your location
in addition to the range,
then atleast SQL can eliminate a large chunk before applying the range formula

---

]]>