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 :(

Arteritis

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.

Lalonde

Thanks! Please can you explain briefly the advantages of your suggestion of low level grids as I would have thought the more precise 'high' sounded the best when reading about them – Arteritis

@BritishDeveloper, Well using low level grids keeps the index relatively light and fast - LLLL only has a maximum of 65536 level-4 cells within it. Testing on several sets of information (inc UK data) generally yielded better than, or equal to performance than other combinations (I tried them all). However, when dealing with Polygons a higher level is needed to better deal with the complexity, as well as experimenting with the cells per object value. There were some instances were I found MLLL or HLLL better, so I also recommend trying them but with 400k rows we're talking 10's of ms tops – Lalonde