select closest point sql server spatial

Question

I have a table Points with a column Point of type GEOGRAPHY. I am running this code to obtain the closest point:

DECLARE @Wgs84Longitude FLOAT;
DECLARE @Wgs84Latitude FLOAT;

DECLARE @Point GEOGRAPHY = Geography::STPointFromText(N'POINT('
                                           + CAST(@Wgs84Longitude AS NVARCHAR(MAX))
                                           + N' '
                                           + CAST(@Wgs84Latitude AS NVARCHAR(MAX))
                                           + N')', 4326);

SELECT 
    TOP 1
    *
FROM Points
ORDER BY @Point.STDistance(Point) ASC;

The Points table has this index:

CREATE SPATIAL INDEX SpatialIndex ON Points (Point);

Unfortunately, the query is pretty slow. Is there anything I can improve to make it faster (index and/or query wise)?

PS:

I also played with some flavors of this:

CREATE SPATIAL INDEX SpatialIndex ON [Core].[InternationalPostcodeList](Point) 
USING GEOGRAPHY_GRID
WITH 
(
--BOUNDING_BOX =(-8.164229, 49.18581, 8.05384, 60.717093)
        GRIDS=(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH) 
     , CELLS_PER_OBJECT = 64 
     , PAD_INDEX = OFF 
     , SORT_IN_TEMPDB = OFF 
     , DROP_EXISTING = OFF 
     , ALLOW_ROW_LOCKS = ON 
     , ALLOW_PAGE_LOCKS = ON 
) ON [PRIMARY];

The performance is still unacceptable.

Jon Bellamy · Answer 1 · 2016-07-17T18:30:43.767

Microsoft's own article on MSDN suggests a few improvements you could make to ensure a "Nearest Neighbour" query is using the Spatial Index. The major one sticking out for me is the lack of using STDistance in the WHERE clause to limit the distance (without which it cannot filter in any way).

Try applying that and see if that improves performance. If not refer to the article itself for further tips.

MSDN Nearest Neighbor

EDIT

Firstly, you can simplify the process of creating the point in your query as follows:

DECLARE @Point GEOGRAPHY = GEOGRAPHY::Point(@latitude, @longitude, @srid);

Secondly, it's probably not going to make a difference, but you can declare your spatial index as HHHH with 16 cells max (you could go for one). At the end of the day, being a singular point, it'll only ever one record in the index at the lowest level, but it depends if you're going to mix spatial data types in the column.

Thirdly, I've ran several tests and you should easily be able to get under one second for a result. I used the following query:

SELECT TOP 1
*
FROM
Points P
WHERE P.Point.STDistance(@Point) < (50 * 1609.344) -- 50 miles
ORDER BY P.Point.STDistance(@Point)

My results were considerably faster than 1 second. If I omitted the WHERE clause, times were around 1500% slower (will increase / decrease with dataset size). But this was still far quicker than your results of 10-12 seconds.

Can you verify that your spatial index is working? If not, try using a WITH(INDEX(SpatialIndex)) hint. If that still doesn't work, can you upload your query execution plan?

Thanks. When I introduce the WHERE clause as suggested in the article, to apparently enforce the use of the spatial index, the query executes even slower. — cs0815, Jul 15 '16 at 08:19
@csetzkorn What is the size of the table (in rows) and what are the times you're currently getting please? — Jon Bellamy, Jul 15 '16 at 11:52

select closest point sql server spatial

1 Answers1