locked
Find closest point to location RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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?
     

    Friday, April 13, 2012 11:27 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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/

    • Marked as answer by amber zhang Monday, April 23, 2012 8:49 AM
    Friday, April 13, 2012 4:56 PM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    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/

    Friday, April 13, 2012 12:10 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 1:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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/

    • Marked as answer by amber zhang Monday, April 23, 2012 8:49 AM
    Friday, April 13, 2012 4:56 PM
    Answerer