SQL Server Query Spatial Data for Nearest Neighbor DISTINCT TOP N ORDER BY Distance

Question

I would like to know if there is a way to query the nearest N neighbors from a spatial data column by dropping duplicates.

For example, my query looks like this:

SELECT TOP (@N) Point.STDistance(@Point) AS Distance
FROM MyTable
WHERE Point.STDistance(@Point) IS NOT NULL --For Spatial Index usage
ORDER BY Distance;

This is very effective, but my results are:

Distance
3906,81969203873
3906,81969203873
5321,62614141754
5756,28719382942

I tried to put a DISTINCT clause, but then it does not use the spatial index.

Any ideas?

thank

Answer 1

According to the MSDN description on the nearest neighboring query and spatial indexes you need to use STDistance()in ORDER BY.

5. ORDER BY STDistance().

6. STDistance() ORDER BY ASC.

GROUP BY CTE subquery, .

Answer 2

- . , . , , . .