SQL Server Join tables on Geometry attributes is very slow - Is this most efficient way?

Question

I have two MS SQL Server (v11.0) tables with Geometry datatypes. One has ~8000 point records, the other ~100 polygon records.

Both tables have spatial indexes on the appropriate geometry column and both use the same SRID.

For a specified reporting purpose, I need to extract a list of names of all the points with the name of the polygon within which it lies. All points are within one of the polygons. The polygons do not overlap.

I use this query:

SELECT points.point_label, polygons.polygon_label
FROM 
points WITH (NOLOCK) INNER JOIN
polygons WITH (NOLOCK) ON 
polygons.polygon_geometry.STContains(points.point_geometry) = 1

It works, but is very slow to return all ~8000 points rows with the containing polygon label.

Is there a more efficient way of joining them to achieve my desired outcome? Is SQL Server just not very good at calculating geometric stuff against datasets? Most of the code examples I see just involve a geometry type variable with a single value being operated on.

Please define "very slow". Please provide details of hardware set-up you are using. — Alex
"Very Slow" is defined as a comparison to joins that are not spatial. Joining the points table to other tables that involve matching text columns returns all 8000 rows in less than a second (i.e. our hardware is fine). Doing this spatial join takes nearly 5 minutes to return. — EvanYatesNZ
Please check if your spatial index is actually being used and edit your question with what you find out — AakashM
Good advice AakashM. Found some versioning control VIEW joins that were preventing the spatial index from being utilised in the query. Stripped those back and the query now runs in 20 seconds. Thanks for the prod in the right direction. If you put in an answer of "make sure spatial index is actually being used" I'll mark it as the answer. — EvanYatesNZ

Ben Thul Ben Thul · Accepted Answer · 2018-09-11T13:24:11

This is totally a SWAG, but based on your description of your data, I'd try this:

SELECT points.point_label, p.polygon_label
FROM points 
outer apply (
    select top(1) polygon_label
    from polygons
    where polygons.polygon_geometry.STContains(points.point_geometry) = 1
) as p

You're giving SQL permission to stop looking after it's found the at most one polygon that subsumes the point.

NB: the only reason I'm posting this as an answer instead of a comment is formatting and length of post. This may not work!

SQL Server Join tables on Geometry attributes is very slow - Is this most efficient way?

1 Answers