CODEWITHSUNDEEP
sqlsql-servert-sqlgis
bunkkin
bunkkin

Reputation: 109

Incorrect Distances When Comparing geography values in SQL

I have a SQL database set up containing a number of fields one of which is a geography field called Coordinates. I have a need to search for all rows that are within a certain radius and to do that I am using the query 

DECLARE @CurrentLocation geography; 
SET @CurrentLocation  = geography::Point(-84.505562, 39.137706, 4326)

SELECT * , Coordinates.STDistance(@CurrentLocation) AS Distance FROM tParkingLot
WHERE Coordinates.STDistance(@CurrentLocation )<= 200000

which gives me the following results enter image description here

the first row is returning as expected as I used those coordinates as my center. However, when measuring the other two lots on google maps I find that the results should be closer to 1133.246 for row 2 and 74673.56 for row 3

I can see from other stackoverflow results that a common mistake is to insert data as lat/lon as opposed to lon/lat however I have already made and corrected that mistake and I cannot determine why I am getting distance results that are so far off from the actual measurements.

Upvotes: 0

Views: 293

Answers (1)

Michael Entin
Michael Entin

Reputation: 7724

The cause of the problem is the order of latitude and longitude. Geography world traditionally uses (lat, lon) order, SQL world defined (x, y) order, which usually means (lon, lat) order.

Microsoft SQL Server Point constructor made confusing compromise: they use (x, y) order for Geometry, but (lat, lon) order for Geography:

Point ( Lat, Long, SRID )  [1]

Since your points are in Cincinnati rather than Antartica, swap argument order.

[1] https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/point-geography-data-type?view=sql-server-2017

Upvotes: 3

Related Questions