Get the nearest longitude and latitude from MSSQL database table?

Ok I have searched SO and Google but haven’t really found a definitive answer so throwing it out there for the SO community.

Basically I have a table of longitudes and latitudes for specific points of interest, the sql query will know which location you are currently at (which would be one of those in the table, passed in as a parameter), therefore it then needs to calculate and return the one row that is the nearest latitude and longitude to the passed in one.

I require this to all be done in MSSQL (2012 / stored proc) rather than in the calling application (which is .NET) as I have heard that SQL is usually much quicker at processing such queries than .NET would be?

EDIT:

I have found the STDistance Function which gives the number of miles between locations such as :

DECLARE @NWI geography, @EDI geography
SET @NWI = geography::Point(52.675833,1.282778,4326)
SET @EDI = geography::Point(55.95,-3.3725,4326)
SELECT @NWI.STDistance(@EDI) / 1000

However I don’t want to have to iterate through all of the lat/lons in the table as surely this would be terrible for performance?

I also tried converting one of the examples pointed out in one of the below comment links (which was MYSQL not MSSQL) however I am getting an error, the code is as follows:

DECLARE @orig_lat decimal(6,2), @orig_long decimal(6,2), @bounding_distance int
set @orig_lat=52.056736; 
set @orig_long=1.14822; 
set @bounding_distance=1;

SELECT *,((ACOS(SIN(@orig_lat * PI() / 180) * SIN('lat' * PI() / 180) + COS(@orig_lat * PI() / 180) * COS('lat' * PI() / 180) * COS((@orig_long - 'lon') * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS 'distance' 
FROM [DB1].[dbo].[LatLons]
WHERE
(
  'lat' BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance)
  AND 'lon' BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance)
)
ORDER BY 'distance' ASC

The error received is:

Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to numeric.

Anyone able to work out the above code or come up with a better solution?

Answer

I wrote a blog a couple years ago that explains how this can be done without using the spatial data types. Since it appears as though you have a table of longitude/latitude values, this blog will likely help a lot.

SQL Server Zipcode Latitude Longitude Proximity Search

Same page saved from Archive.org

Advertisement

Answer