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?