Spatial query - poor performance, not using index

Question

I'm trying to find locations within some distance from given coordinates. On a table with about 32k records query takes about 2 seconds - which is way too slow, imo.

It is doing a clustered index scan, which is logical - it has to calculate distance for every location.. However, I still think this should be faster over a data set this small. I do have spatial index defined, however it's not used, and query fails if I'm forcing it.

Most of the time (~86%) is spent on Filter that calculates the distance - so I'm looking for a ways to optimize that, and I need some help here..

The query I'm using is this:

SELECT Name
FROM Venue
WHERE (Coordinates.STDistance(geography::STPointFromText('POINT(-113.512245 51.498212)', 4326)) / 1000) <= 100

possible duplicate of Are SQL Server 2008 Spatial Data features useful for mapping queries? — TomTom
– TomTom, Commented Jul 3, 2012 at 13:05
Why are you dividing by 1000 in the StDistance clause? Can this be calculated elsewhere? — CatchingMonkey
– CatchingMonkey, Commented Jul 3, 2012 at 14:59
Changing to WHERE Coordinates.STDistance(geography::STPointFromText('POINT(-113.512245 51.498212)', 4326)) <= 100000 will allow the use of a spatial index as a primary filter rather than table scan. You might still require an explicit index hint if you're using SQL 2008 though. — Alastair Aitchison
– Alastair Aitchison, Commented Jul 3, 2012 at 19:41

Community · Accepted Answer · 2017-05-23 12:20:05Z

0

One old approach is to use a BOX firxt.

From your point, make two points on opposite ends of the box. +R/+R and -R/-R from the center.

Then you can filter - a point has to be in this box AND in the circle you describe.

The box check can run on the index and kills most elements.

Simple school geometry. You draw a rectangular box around the circle you describe.

Your current approach can not use the index because the index does not contain the fields.

ALTERNATIVELY: DRAW A CIRCLE - do not use a distance calculation. Draw a circle. With points.

Or read https://stackoverflow.com/questions/11311363/my-application-is-about-asp-net-using-linq-and-remote-mssql-my-connection-is-be which is the same issue you ask.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jul 3, 2012 at 13:03

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evgeni Over a year ago

I kind of did.. Using STBuffer.

Collectives™ on Stack Overflow

Spatial query - poor performance, not using index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related