CODEWITHSUNDEEP
sql-server-2008spatial-indexspatial-querysqlgeography
CianM
CianM

Reputation: 397

SQL Server 2008 Spatial Query performance

I have an application where user's store their commute routes in our database.

The routes are stored as polylines (linestrings). The database also stores incidents, traffic accidents that kind of thing. Periodically we need to query a route to see if there is any incident within a 1k radius of the route.

The join on the query is structured as follows:

    Route r left outer join Incident i on
    r.PolyLine.STDistance(i.Location) < 1000

Now I also tried something like this:

Route r left outer join Incident i on   
r.PolyLine.STBuffer(1000).STIntersects(i.Location) = 1

Things we have tried so far to improve the speed are:

  1. Reduce the number of points along the linestring
  2. Add a spatial index (though I don't know how to tweak it)

1) above worked but not well enough and leads me to believe that the incident was being compared to every point along the route which seems really inefficient.

We are considering strong the long lats as geometry vs geography so we get access to the Bounding Box and also to get STContains.

Also considering calling reduce on the PolyLine prior to checking for incidents.

Upvotes: 2

Views: 443

Answers (1)

Geobility
Geobility

Reputation: 178

I would suggest geometry storage. The benefits of going to geography in this scenario don't seem to outweigh the costs.

Spatial Indexes are very important. One process I used spatial queries in went from ~15 min to ~1 min by using a properly tuned spatial index. However, I haven't found documentation on a good way to automatically obtain optimal settings for them. I have answered a similar question about spatial index tuning. The stored procedure I provided there takes a while for each data set but can be run in the background while you do other work.

As far as your query goes, I set up a different query and compared its performance with the two you provided above. It appears that performance improves by putting a buffer of your route into a geometry variable and using the variable in your spatial comparison. My reason for this is that it only has to create the buffer (or evaluate distance) once instead of once for each row it compares against. You could try this and see what results you get.

DECLARE @routeBuff geometry
SET @routeBuff = (SELECT r.PolyLine.STBuffer(1000) FROM route r WHERE recordID = 2778) --how ever you select the particular route

SELECT
    *
FROM
    incident i
WHERE
    i.location.STIntersects(@routeBuff) = 1

Upvotes: 2

Related Questions