Find closest point to location

# Find closest point to location

• 13. dubna 2012 11:27

Hi Folks,

I need help to optimize my algorithm to calculate the distance the nearest coast (within a range of 2km) to a given real estate location.

I have a table - t1- containing the real estates and locations and another table - t2- containing coast lines  and the 'STBuffer'ed value of the coast line called Coast2000. There is 1.2 mill records in table t1 and 1600 records in table t2 with spatial index on Coast2000 using the high level grid setting.

I am using the following sql:

select a.ID, min(a.Location.STDistance(b.Coast_Location)) CoastDistance
from t1 a
join t2 b
on b.Coast2000.Filter(a.Location)=1 with(index(spix_buffer))
group by a.ID

However, this takes ages to run. Any ideas how to optimize this?

### Všechny reakce

• 13. dubna 2012 12:10
Přispěvatel

Bit hard to say without more information - please post the query execution plan and DDL for the spatial index. Why are you using HIGH grid setting?

twitter: @alastaira blog: http://alastaira.wordpress.com/

• 13. dubna 2012 13:00

Hi,

I have attached the Explan below. The spatial index is defined as

```CREATE SPATIAL INDEX [spix_buffer] ON t2
(
[Coast2000]
)USING  GEOMETRY_GRID
WITH (
BOUNDING_BOX =(440000, 6048000, 750000, 6403000), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 8192, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO```

I am using data for Denmark which is a very small, but quite coast-dense country, and therefore the HIGH grid setting is needed.

• 13. dubna 2012 16:56
Přispěvatel

8192 cells per object?! At a guess, I'd say that was a major factor in why your query is slow - with that many cells per object your index is probably massive and cumbersome to use - probably not providing much benefit over a table scan. Did you try using the default M,M,M,M and 16 cells per object? What performance difference do you get?

Can you call sp_help_spatial_geometry_index to determine what primary and internal filter efficiency you're getting from that index?

twitter: @alastaira blog: http://alastaira.wordpress.com/