locked
Another spatial query with no Index being used. RRS feed

  • Question

  • Hi folks,

       Just when I think I understand this stuff - I get stumped by a ReallySimpleQuestion(tm).

       I have a query and it's taking around 20 secs to run. No spatial index is getting used.

    Here's the query :-

    SELECT a.CountyId AS LocationId, @LocationTypeId AS LocationTypeId, a.Name, b.Name AS FullName,
      LargeReducedBoundary.STAsBinary() AS Boundary
    FROM [dbo].[Counties] a WITH(INDEX(SI_Counties_LargeReducedBoundary))
    INNER JOIN LocationNames b ON a.CountyId = b.LocationId AND LocationType = 3
    WHERE @BoundingBox.STIntersects(Boundary) = 1
    

    Now, what is the index?

    
    /****** Object: Index [SI_Counties_LargeReducedBoundary]  Script Date: 08/16/2010 16:41:18 ******/
    CREATE SPATIAL INDEX [SI_Counties_LargeReducedBoundary] ON [dbo].[Counties] 
    (
      [LargeReducedBoundary]
    )
    USING GEOGRAPHY_GRID 
    WITH (GRIDS =(LEVEL_1 = LOW,LEVEL_2 = LOW,LEVEL_3 = LOW,LEVEL_4 = LOW), 
       CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    

    and when I try and run my query....

     

    The query processor could not produce a query plan for a query with a spatial index hint.  Reason: The spatial object is not defined in the scope of the predicate.  Try removing the index hints or removing SET FORCEPLAN.

    /me cries.

    Now, this table has 3 spatial indexes on it? Why three? there's three GEOGRAPHY fields in this table. One high-poly-count-boundaries, one with the envelopcenter and a 3rd with a (reduced) low-poly-count-polygons. (High and Low poly-count polygons are the same data, just one has had Reduce executed against it).

    If I happen to use the index which is created against the high-poly-count-polygons on the query above (which is retuning the low poly-count polygons), it drops to 2 secs (still pretty slow :( but way better).

    So can someone suggest what I have done wrong?

     

     


    -Pure Krome-
    Monday, August 16, 2010 6:52 AM

Answers

  • Morning, Krome!

    Your Counties table contains a geography field called LargeReducedBoundary , and it is this field that is included in the SL_Counties_LargeReducedBoundary index, right?

    However, your query doesn't use the LargeReducedBoundary field (directly). Instead it uses LargeReducedBoundary.STAsBinary() AS Boundary , and in the WHERE clause (the "predicate" bit referred to in the error message), you're trying to limit the results to WHERE @BoundingBox.STIntersects(Boundary) = 1 .

    For this condition to work, you need to compare whether @BoundingBox intersects the LargeReducedBoundary field itself, not the result of LargeReducedBoundary.STAsBinary() , which is just a stream of bytes.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Pure Krome Monday, August 16, 2010 11:53 PM
    Monday, August 16, 2010 7:04 AM
    Answerer

All replies

  • Morning, Krome!

    Your Counties table contains a geography field called LargeReducedBoundary , and it is this field that is included in the SL_Counties_LargeReducedBoundary index, right?

    However, your query doesn't use the LargeReducedBoundary field (directly). Instead it uses LargeReducedBoundary.STAsBinary() AS Boundary , and in the WHERE clause (the "predicate" bit referred to in the error message), you're trying to limit the results to WHERE @BoundingBox.STIntersects(Boundary) = 1 .

    For this condition to work, you need to compare whether @BoundingBox intersects the LargeReducedBoundary field itself, not the result of LargeReducedBoundary.STAsBinary() , which is just a stream of bytes.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Pure Krome Monday, August 16, 2010 11:53 PM
    Monday, August 16, 2010 7:04 AM
    Answerer
  • Ha! wikid!! i was referencing the high-poly polygon (in the STIntersects(..)) instead of the low-poly polygon field, which is what I need to be returned. Works 100% :)

     

     

     


    -Pure Krome-
    Monday, August 16, 2010 11:53 PM