5

Update: Since writing this post we decided to migrate our geospatial data to an ElasticSearch database, which yielded better results.

I'm new to SQL and I need help to optimize a spatial query so that it runs under in under 2 seconds. We have already tried some suggestions found on various sites (More on that below).

Background

We have a table [Id, Geometry] of about 300,000 geometric shapes of varying sizes and complexity, stored as geometry data types. We have created a spatial index for the Geometry using the code snippet below.

CREATE SPATIAL INDEX [IX_Geometry_Spatial]
ON [dbo].[Geometries] ([Geometry]) USING  GEOMETRY_AUTO_GRID 
WITH  (
        BOUNDING_BOX = (XMAX = 24.43359375, XMIN = 10.810546875, YMAX = 69.2249968541159, YMIN = 55.2791152920156)
      );

What we want is to find all the geometric shapes that intersects with an input geometric shape. This is done by using the following query.

DECLARE @g geometry;  
SET @g = geometry::STGeomFromText('POLYGON ((x0 y0, ...))', 4326); -- A Worst Case Polygon containing 1442 data points

SELECT Count(Id)
FROM Geometries
WHERE Geometries.Geometry.Filter(@g.Reduce(.25)) = 1

For some worst case input geometric shapes (large, complex polygons) this execution takes about 7-10 seconds.

This is the execution plan for the query: enter image description here

We can see that we hit the spatial index, but the most expensive operation is the clustered index seek (Clustered)

Clustered index seek details:

enter image description here

Spatial index seek details:

enter image description here

Questions

Shouldn't the heavy lifting be done by the spatial index, not the clustered index?

Can the query be improved by changing settings for the spatial index? What settings should we use (for GRIDS, CELLS_PER_OBJECT, etc.)?

How can the execution time be shortened overall, or is 7-10 seconds what you can expect for this kind of query?

What we have tried, that helped

Each of these saved roughly a few seconds.

  • Checked for index fragmentation, and rebuilt indexes.
  • Switched intersection method from STIntersect() to Filter()
  • Reduced the input geometry with Reduce(.25). This Reduces the geometry from 1442 data points to 7. (If we decide to use this, it will have to be dynamic for different inputs, but that's another issue.)
  • Introduced a new table column SimpleGeometry which contains the bounding boxes of all the geometry objects from the Geometry column. Created a new SimpleGeometry spatial index and used SimpleGeometry for the lookup instead of Geometry. (Not in use in query example.)
Community
  • 1
  • 1
hittaKAFFE
  • 51
  • 3
  • I recommend to do more preprocessing. You started ok with bounding boxes. I would improve this by making a 3d grid and each grid cube would hold a list of its objects that interfere with it. The goal should be to improve the location of closest objects as fast as possible and then go to a detailed computation with as fewer objects as possible. – Honza P. Jan 02 '19 at 15:55
  • I believe the largest problem might be the table-valued function. Those things are slow as riding a turtle backwards. – Luis Cazares Mar 26 '19 at 13:22
  • I had a similar query where the spatial index was not being used for the search. Try enforce SQLServer to use the index by using WITH (INDEX([Index_Name])). `SELECT TOP (1) * FROM myTable ga WITH(INDEX([SpatialIndex_sptGeoPolygonBounds])) WHERE ga.sptGeoPolygonBounds.STIntersects(@point) = 1` – Dawood Awan Apr 29 '19 at 19:34

1 Answers1

0

What if you did the calculation each time you added a new shape, then stored just the intersections in a table? Now your query is instantaneous.

Roger
  • 261
  • 1
  • 7
  • Be careful not to be mistaken for a question-where-an-answer-should-be. Rephrasing without a rethorical question might help. – Yunnosch May 18 '19 at 08:18