SQL Optimising a spatial index for localised geography points

Question

I have ~400k points of interest that are stored in GEOGRAPHY spatial sql.

I will be querying these points with PointOfInterest.STDistance(@CentralPoint) < @Radius to find PointOfInterest's within a certain radius of the @CentralPoint sent to the query.

I've read a bit about the layering of grids and would like someone whos knows their stuff to recommend the most sensible grid pattern. The default is

LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM

But my situation is such that I will ONLY have points of interest within theUK. Despite being awesome we only take up a relative spec of terra firma so I was wondering if there is a better grid pattern to use in the spatial index for this case.

Being Geography based I can't use the lovely looking geometry bounding boxes. Also I am using SQL Azure which doesn't seem to have the spatial help stored procs :(

Answer 1

As ever with Spatial Indexing, you end up finding that testing a variety of grid settings on your data set can yield different results to those of others. That said, I find setting Low at all levels, or Medium, Low, Low, Low yields great results with Points due their simplistic nature.

To make best use of the index however, consider optionally buffering the point and checking for an intersection. Again, I've found it often yields better consistently low result times but test it on your data.

DECLARE @point GEOGRAPHY = GEOGRAPHY::STPointFromText('POINT(<coords>)', 4326);
DECLARE @radius INT = 1000;

SELECT
*
FROM <table>
WHERE <GeographyColumn>.STIntersects(@point.STBuffer(@radius)) = 1;

Try to stay away from the urge to switch to Geometry, as whilst it'll yield ever-so-slightly faster queries, it has more chance to yield "incorrect" results due to working with a planar model. That said if the search distances are small enough, the difference will not be noticeable in most scenarios.