# lon/lat radius function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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