[GIS] Order sql server spatial geometry distance by the nearest

geographygeometryspatial-databasesql server

I have a geometry column in a table, I want to get the N nearest neighbours; here is a specific example with the geography type

DECLARE @PTO Geography = Geography::STPointFromText('POINT(-57.432546 -25.365799)', 4326)
DECLARE @PDV3893 Geography = Geography::STPointFromText('POINT(-57.441357 -25.355435)', 4326)
DECLARE @PDV570 Geography = Geography::STPointFromText('POINT(-57.419036 -25.368309)', 4326)
SELECT @PTO.STDistance(@PDV3893) as n3893, @PTO.STDistance(@PDV570) as n570

This is the result of the query:

n3893               n570
1450,74600838172    1387,89423301988

Because the distance is in meters, you can see the nearest point is the n570 one

But in my query using the table (and therefore the geometry type) I get degrees, not meters

DECLARE @PTO geometry = geometry::STPointFromText('POINT(-57.432546 -25.365799)', 4326)
DECLARE @PDV3893 geometry = geometry::STPointFromText('POINT(-57.441357 -25.355435)', 4326)
DECLARE @PDV570 geometry = geometry::STPointFromText('POINT(-57.419036 -25.368309)', 4326)
SELECT @PTO.STDistance(@PDV3893) as n3893, @PTO.STDistance(@PDV570) as n570

Result:

n3893               n570
0,0136031693733486  0,0137411862661163

As you can see I can't perform a "order by" statement in a query with geometry because obviously we are working with degrees

How can I get the nearest point in geometry and order the results by distance?

Best Answer

Further to Russell's Answer

It's unfortunate that you have you locations stored in the wrong data type. The easiest way to get an accurate distance between your is to cast the geometries as geographies.

You will need to confirm the the Lat is stored in the Y and the Long is stored in the X of the geometry, otherwise you will most likely end up with inaccurate distances.

If you are doing a Nearest Neighbour query you will need to take into account that 1º North/South is different than 1º East/West and the distance returned from a geometry containing Lat/Long will not reflect this. Generally this is unlike to cause an issue, but you may end up with nearest neighbours in the wrong order.

To demonstrate this

CREATE TABLE #TestGeomVsGeog (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Geom Geometry,
    Geog Geography
);
INSERT INTO #TestGeomVsGeog (Geom, Geog)
VALUES
(Geometry::Point(-57,-25,4326), Geography::Point(-25,-57,4326)), -- AnchorPoint
(Geometry::Point(-58,-25,4326), Geography::Point(-25,-58,4326)), -- 1 degree East
(Geometry::Point(-57,-26,4326), Geography::Point(-26,-57,4326)), -- 1 degree South
(Geometry::Point(-57,-25.9999,4326), Geography::Point(-25.9999,-57,4326)) -- Closest Point
;

SELECT a.ID FromID, b.ID ToID, 
    a.geom.STDistance(b.geom) GeomDegreeDistance, 
    a.geog.STDistance(b.geog) GeogMetreDistance,
    ROW_NUMBER() OVER (ORDER BY a.geom.STDistance(b.geom)) GeometryOrder,
    ROW_NUMBER() OVER (ORDER BY a.geog.STDistance(b.geog)) GeographyOrder
FROM (SELECT * FROM #TestGeomVsGeog WHERE ID = 1) a, 
    (SELECT * FROM #TestGeomVsGeog WHERE ID != 1) b

DROP TABLE #TestGeomVsGeog

This returns the following

FromID      ToID        GeomDegreeDistance     GeogMetreDistance  GeometryOrder    GeographyOrder
----------- ----------- ---------------------- ------------------ ---------------- ----------------
1           2           1                      100949.861402787   2                1
1           4           0.9999                 110769.324596193   1                2
1           3           1                      110780.403395342   3                3

As you can see the Geometry distance will end up with the wrong point as the nearest neighbour.

Depending on the amount of data you are dealing with I would suggest creating copy of the original into temporary table (using geography) to do the processing on, or lobby to have the original table datatype changed to a geography.

Related Question