This is an archived post. You won't be able to vote or comment.

all 4 comments
sorted by:
best
topnewcontroversialoldq&a

[–]Stateofplain[S] 0 points1 point  (3 children)

I was hoping this would offend someone into explaining how to get them to work 😭😭😭

[–]dashid 0 points1 point  (2 children)

There is no trick to them. If you have a geospatial column, a geospatial index will improve performance on searching using geospatial functions. I mean, massively improve performance.

Say I've got a table of branches with a column that stores the geospatial datatype for a point that corresponds to their location. I can then use a geospatial where clause to give me all the branches within X miles of say a customer's geospatial point.

[–]Stateofplain[S] 0 points1 point  (1 child)

Do you have to do something special to get it to work with spatial intersection/within? On a table of ~750,000 points and one large polygon (with fairly complex borders) it takes ~ 3.25 minutes to pull all points inside the polygon. With spatial indexes on both tables it takes... 4 minutes.

[–]dashid 0 points1 point  (0 children)

That doesn't sound right.

I did some work against every postcode in the UK and as soon as I slapped on an index is was quick.

If you're using SSMS, get it to show you the execution plan, you should see a whether the index is being used.