locked
Geography STDistance and Filter searches are slower RRS feed

  • Question

  • I've got a GB_Road table with clustered index on ID (identity column) and spatial index on geography column. GB_Road column has million rows. Both SQL commands are taking much longer time (400 ms), infact 'Filter' clause is taking more time than STDistance clause. I'v read the book 'Beginning Spatial with SQL Server 2008', according to this Filter must be significantly faster than STDistance.

    Declare @GeogPos
    Declare @PostCodeSearchArea geography
    Set @PostCodeSearchArea = @GeogPos.STBuffer(100)

    Set @GeogPos = geography::STGeomFromText('POINT(' + Convert(varchar,CONVERT(decimal(20,6),@Long)) +  ' ' + Convert(varchar,CONVERT(decimal(20,6),@Lat)) + ')',4326)

    Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB_Road
    where GeoLoc.STDistance(@GeogPos) < 100

    Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB_Road
    where GeoLoc.Filter(@RoadSearchArea) = 1

     

    Friday, March 19, 2010 4:00 PM

All replies

  • Hi there,

    "Both SQL commands are taking much longer time (400 ms)" - much longer than what? longer than they used to? longer than you were expecting?

    Filter() is an approximation for STIntersection(), since it uses only the primary filter and not the secondary, refining filter. It is therefore generally quicker (but less accurate) than STIntersection(), but you cannot directly compare Filter() and STDistance() because they're doing different things. However, they can both be used in methods to find nearest-neighbour queries, which I think is what you're trying to do.

    Your code snippet above appears to be invalid for several reasons - you're declaring a variable called @GeogPos but not giving it a datatype, you're declaring a geography variable @PostCodeSearchArea that is never used, and you're using GeoLoc.Filter(@RoadSearchArea) = 1, when @RoadSearchArea is never declared... could you post the actual code you're using? Also could you confirm whether the geography index that you created on the GB_Road table is actually being used in the execution plan of either query? 

     

     

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, March 19, 2010 4:18 PM
    Answerer
  • Here is entire code. The current expmaple is using STDistance. You can simply uncomment line which reads Filter and comment STDistance line. The execution plan is using clustered index and spatial index both. The query which is using GB_PostCode table got 1.6 millions rows and filter cost is 17% (where clause using STDistance), Clustered index cost is 7% and spatial index cost is 0%. Table GB_PostCode got 3.7 millions rows and filter cost is 30%, clustered index cost is 31% and spatial index cost is 0%. Rest of the tables are smaller.

    Create function dbo.fn_ReverseGeoCode2(@Long float, @Lat float)
    Returns varchar(300)
    As

    Begin

    Declare @Region varchar(10)
    Declare @GeomPos geometry
    Declare @GeogPos geography
    Declare @Address varchar(300)
    Declare @RadiusPOI smallint
    Declare @RadiusRoad smallint
    Declare @RadiusTown smallint
    Declare @RadiusPostCode smallint
    Declare @Location Table(Name varchar(100), Distance smallint)

    Set @RadiusPOI = 500
    Set @RadiusRoad = 40
    Set @RadiusTown = 10000
    Set @RadiusPostCode = 500

    Set @GeomPos = geometry::STGeomFromText('POINT(' + Convert(varchar,CONVERT(decimal(20,6),@Long)) +  ' ' + Convert(varchar,CONVERT(decimal(20,6),@Lat)) + ')',4326)
    Set @GeogPos = geography::STGeomFromText('POINT(' + Convert(varchar,CONVERT(decimal(20,6),@Long)) +  ' ' + Convert(varchar,CONVERT(decimal(20,6),@Lat)) + ')',4326)

    Declare @RoadSearchArea geography
    Set @RoadSearchArea = @GeogPos.STBuffer(40)
    Declare @PostCodeSearchArea geography
    Set @PostCodeSearchArea = @GeogPos.STBuffer(500)

    --Select @Region = RegionID from Region where GeomLoc.STContains(@GeomPos) = 1

    Insert Into @Location
    Select strLocation, GeoLoc.STDistance(@GeogPos) Distance from GB_CustomPoints
    where GeoLoc.STDistance(@GeogPos) < @RadiusPOI

    /*If (@@ROWCOUNT > 0)
    Begin
     Select top 1 @Address = Name from @Location Order by Distance
     Delete from @Location
    End
    Else*/
      Delete from @Location
     Begin
      Insert Into @Location
      Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB_Road
      where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      --where GeoLoc.Filter(@RoadSearchArea) = 1
      
      if ((select count(*) from @Location) > 0)
      Select top 1 @Address = Name from @Location Order by Distance
      Delete from @Location
     End
     
    Insert Into @Location
    Select Name + ', ' + County, GeoLoc.STDistance(@GeogPos) Distance from GB_Town
    where GeoLoc.STDistance(@GeogPos) < @RadiusTown

    if ((select count(*) from @Location) > 0)
    Select top 1 @Address = @Address + ', ' + Name from @Location Order by Distance
    Delete from @Location

    Insert Into @Location
    Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB_PostCode
    where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    --where GeoLoc.Filter(@PostCodeSearchArea) = 1

    if ((select count(*) from @Location) > 0)
    Select top 1 @Address = @Address + ', ' + Name from @Location Order by Distance
    Delete from @Location

    Return @Address
    End
    Go

    select dbo.fn_ReverseGeoCode2(-2.640886, 53.706886)

    Friday, March 19, 2010 4:43 PM
  • @tanoshimi alluded to a spatial index: "Also could you confirm whether the geography index that you created on the GB_Road table is actually being used in the execution plan of either query?"

    If the Filter() method does not find/use the spatial index, it will do an ST_Intersects() as fallback which will be very slow without a spatial index...  I suspect that this is what is happening in this case.  Check your query plan.

    Friday, March 19, 2010 11:33 PM
  • The spatial index is being used. As I mentioned in previous the cost of using it 1% compare to clustered index which is in Identity column and cost is 31%. Also execution plan show that it is using spatial index as in extended index search.

    I've also created multiple tables for Road and PostCode for each region of UK. And depends on region it uses a particular table which contains around 1/2 million rows. But there is another problem with this function when I'm searching for a region in a polygon or multi-polygon which consists of thousands of points increases execution time.

    Saturday, March 20, 2010 9:28 AM
  • In terms of directly comparing the Filter clause and STDistance clause in the sample code above, this may be an apples to oranges scenario.  The STDistance clause is using a point as the other geometry while the Filter is using a polygon.  Does the Filter clause run longer when using @GeogPos instead of @RoadSearchArea?

    Although the following line is currently commented out:

    --Select @Region = RegionID from Region where GeomLoc.STContains(@GeomPos) = 1

    You may want to experiment with changing it to the following if uncommenting it:

    --Select @Region = RegionID from Region where GeomLoc.STIntersects(@GeomPos) = 1

     

    I have run into several situations where STContains and STIntersects yield the same result sets but do so with dramatically different execution times.  A bug for this issue has been submitted through Microsoft Connect:  STContains performs order of magnitude slower than STIntersects when identifying points within a polygon

     

    Saturday, March 20, 2010 1:27 PM
  • I've got some stats after reverse geocoding 100 locations.

    When using single Road and PostCode table scenario, it took 1:55 mins (See function in my reply above)

    When using multiple Road and PostCode tables and using following methods to determine region of location

    STContains - it took 2:29 mins   STIntersects - it took 3:27 mins (See following function)

    CREATE function [dbo].[fn_ReverseGeoCode](@Long float, @Lat float)
    Returns varchar(300)
    As
    Begin
    Declare @Region varchar(10)
    Declare @GeomPos geometry
    Declare @GeogPos geography
    Declare @Address varchar(300)
    Declare @RadiusPOI smallint
    Declare @RadiusRoad smallint
    Declare @RadiusTown smallint
    Declare @RadiusPostCode smallint
    Declare @Location Table(Name varchar(100), Distance smallint)
    Set @RadiusPOI = 500
    Set @RadiusRoad = 40
    Set @RadiusTown = 10000
    Set @RadiusPostCode = 500
    Set @GeomPos = geometry::STGeomFromText('POINT(' + Convert(varchar,CONVERT(decimal(20,6),@Long)) +  ' ' + Convert(varchar,CONVERT(decimal(20,6),@Lat)) + ')',4326)
    Set @GeogPos = geography::STGeomFromText('POINT(' + Convert(varchar,CONVERT(decimal(20,6),@Long)) +  ' ' + Convert(varchar,CONVERT(decimal(20,6),@Lat)) + ')',4326)
    --Select @Region = RegionID from Region where GeomLoc.STIntersects(@GeomPos) = 1
    Select @Region = RegionID from Region where GeomLoc.STContains(@GeomPos) = 1
    --set @Region = 'GB30'
    Declare @RoadSearchArea geography
    Set @RoadSearchArea = @GeogPos.STBuffer(40)
    Declare @PostCodeSearchArea geography
    Set @PostCodeSearchArea = @GeogPos.STBuffer(500)
    Insert Into @Location
    Select strLocation, GeoLoc.STDistance(@GeogPos) Distance from GB_CustomPoints
    where GeoLoc.STDistance(@GeogPos) < @RadiusPOI
    /*If (@@ROWCOUNT > 0)
    Begin
     Select top 1 @Address = Name from @Location Order by Distance
     Delete from @Location
    End
    Else*/
     Delete from @Location
     Begin
      if (@Region = 'GB21')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB21_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB23')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB23_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB24')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB24_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB26')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB26_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB27')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB27_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB28')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB28_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB29')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB29_Road
       where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      if (@Region = 'GB30')
       Insert Into @Location
       Select Name, GeoLoc.STDistance(@GeogPos) Distance from GB30_Road
       where GeoLoc.Filter(@RoadSearchArea) = 1
       --where GeoLoc.STDistance(@GeogPos) < @RadiusRoad
      
      Select top 1 @Address = Name from @Location Order by Distance
      Delete from @Location
     End
    Insert Into @Location
    Select Name + ', ' + County, GeoLoc.STDistance(@GeogPos) Distance from GB_Town 
    where GeoLoc.STDistance(@GeogPos) < @RadiusTown
    Select top 1 @Address = @Address + ', ' + Name from @Location Order by Distance
    Delete from @Location
    if (@Region = 'GB21')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB21_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB23')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB23_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB24')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB24_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB26')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB26_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB27')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB27_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB28')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB28_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB29')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB29_PostCode 
     where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    if (@Region = 'GB30')
     Insert Into @Location
     Select PostCode, GeoLoc.STDistance(@GeogPos) Distance from GB30_PostCode 
     where GeoLoc.Filter(@PostCodeSearchArea) = 1
     --where GeoLoc.STDistance(@GeogPos) < @RadiusPostCode
    Select top 1 @Address = @Address + ', ' + Name from @Location Order by Distance
    Delete from @Location
    Return @Address
    End

     

    Monday, March 22, 2010 9:09 AM