CODEWITHSUNDEEP
sql-server
Matt
Matt

Reputation: 5660

SQL Server: Calculate the Radius of a Lat/Long?

Say I have the latitude and longitude of a city and I need to find out all the airport that are within 100 miles of this location. How would I accomplish this? My data resides in SQL Server. 1 table has all the city info with lat and long and the other has the airport info with lat and long.

Upvotes: 2

Views: 2856

Answers (2)

swasheck
swasheck

Reputation: 4693

First ... convert city's data point

DECLARE @point geography;

SELECT geography::STPointFromText('POINT(' + CAST(@lat AS VARCHAR(20)) + ' ' + 
                    CAST(@lon AS VARCHAR(20)) + ')', 4326)

where @lat and @lon are the latitude and longitude of the city in question.

Then you can query the table ... 

SELECT [column1],[column2],[etc]
FROM [table]
WHERE @point.STBuffer(160934.4).STIntersects(geography::STPointFromText(
  'POINT(' + CAST([lat] AS VARCHAR(20)) + ' ' + 
  CAST([lon] AS VARCHAR(20)) + ')', 4326) );

where 160934.4 is the number of meters in 100 miles.

This will be slow, though. If you wanted to do even more spatial work, you could add a persisted computed column (because lat and lon points aren't really going to change) and then use a spatial index.

ALTER TABLE [table] 
  ADD geo_point AS geography::STPointFromText('POINT(' + CAST([lat] AS VARCHAR(20))
  + ' ' + CAST([lon] AS VARCHAR(20)) + ')', 4326) PERSISTED;

CREATE SPATIAL INDEX spix_table_geopt
   ON table(geo_point)
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) ); --you'd have to know your data

Upvotes: 4

E.J. Brennan
E.J. Brennan

Reputation: 46849

I used/wrote this several years ago, and it was close enough for what I needed. Part of the formula takes into account the curvature of the earth if I remember correctly, but it has been a while. I used zip codes, but you could easily adapt for cities instead - same logic.

ALTER PROCEDURE [dbo].[sp_StoresByZipArea] (@zip nvarchar(5), @Radius float)  AS

DECLARE @LatRange float
DECLARE @LongRange float
DECLARE @LowLatitude float
DECLARE @HighLatitude  float
DECLARE @LowLongitude  float
DECLARE @HighLongitude  float

DECLARE @istartlat  float
DECLARE @istartlong  float

SELECT @iStartlat=Latitude, @iStartLong=Longitude from zipcodes where zipcode=@ZIP

SELECT @LatRange = @Radius / ((6076 / 5280) * 60)
SELECT @LongRange = @Radius / (((cos((@iStartLat * 3.141592653589 / 180)) * 6076.) /  5280.) * 60)

SELECT @LowLatitude = @istartlat - @LatRange
SELECT @HighLatitude = @istartlat + @LatRange
SELECT @LowLongitude = @istartlong - @LongRange
SELECT @HighLongitude = @istartlong + @LongRange

/** Now you can create a SQL statement which limits the recordset of cities in this manner:  **/

SELECT * FROM ZipCodes
 WHERE (Latitude <= @HighLatitude) AND (Latitude >= @LowLatitude) AND (Longitude >= @LowLongitude) AND (Longitude <= @HighLongitude)

Upvotes: 3

Related Questions