Find closest point to location

Find closest point to location

• Friday, April 13, 2012 11:27 AM

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?

All Replies

• Friday, April 13, 2012 12:10 PM

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?

• Friday, April 13, 2012 1:00 PM

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.

• Friday, April 13, 2012 4:56 PM