locked
Question about STIntersects() RRS feed

  • Question

  • Hi folks,

    i'm wondering if there is a best-practice, with regards to performance, to how one should use the STIntersects() method. I'll explain what i mean with an example.

    @Geog1 = Multipolygon: two polygons -> one the shape of the North America, the second one the shape of the Islands of Hawaii.
    @Geog2 = Point: a point representing a location inside of North Amercia (eg. the centre of Microsoft Hq in Redmond).

    Now, if i want to see if the point exists in the the multipoly, is it best to do

    a) @Geog1.STIntersects(@Geog2) = 1

    or

    b) @Geog2.STIntersects(@Geog1) = 1

    or it make no difference what so ever?

    -Pure Krome-
    Monday, January 19, 2009 12:13 AM

All replies

  • Hi there,

    If @Geog1 and @Geog2 are always local variables then I don't believe there's any significant difference to the order in which they are supplied to the STIntersects() method.
    However, if you are actually using the STIntersects() method to test for intersection against many rows of data held in a geography/geometry column then there could be significant difference based on the effect of any spatial indexes that are defined on that table.
    e.g. rather than
    SELECT @Geog1.STIntersects(@Geog2)
    you were doing:
    SELECT * FROM MyTable WHERE @Geog1.STIntersects(Geog2)

    When you specify the properties of a spatial  index (grid resolution, cells per object) on a table, these are not only applied to the base table rows but also are applied to the query sample used in a spatial predicate of any query against that table. In this case, whatever index is applied to the Geog2 column needs to be applied to @Geog1 for the primary filter to be executed.

    So, for example, let's say that your base table contained all of the points, and had a spatial index with HIGH resolution at all four levels of the grid and a CELLS PER OBJECT limit of 1. If you were to run a query against that table to find out which rows intersected a given multipolygon, the multipolygon would also need to be tesselated using same index parameters - HIGH resolution and 1 cell per object. This would probably not result in a very good tesselation since you'd blow the CELLS PER OBJECT limit so the high grid level cells would not be decomposed and the primary filter would be inefficient.

    In contrast, now let's say that your base table contained lots of polygons/multipolygons and you supplied the point as the query parameter. The table containing the polygons is more likely to have a higher CELLS PER OBJECT limit, but also a lower resolution for each level of the grid. When this is applied to the point supplied in the predicate this results in a very 'loose fit' leading to lots of false positive results from the primary filter - again leading to more work for the secondary filter.

    As ever, there's no right answer - the best approach is trial and error with your particular dataset!

    (At least, I think that is right - hopefully Isaac K will be able to confirm!)
    Monday, January 19, 2009 9:12 AM
    Answerer
  • Hi Folks,

    As far as the index is concerned, this shouldn't matter.  Essentially, the optimizer has a rule that X.STIntersects(Y) is the same thing as Y.STintersects(X), so we'll look at plans that have an index on either side.

    One thing that won't work is 1 = X.STIntersects(Y).  We just didn't get time to add the rule.

    As far as the method itself is concerned, order shouldn't matter either.

    Cheers,
    -Isaac

    Isaac Kunen, Microsoft SQL Server
    Monday, January 19, 2009 5:09 PM
  • Thanks for clarifying Isaac - just to be certain, can you confirm my assumption that when you specify a spatial predicate against a table that does have a spatial index, the query sample is tesselated using the same scheme as the index on the column?
    Monday, January 19, 2009 5:47 PM
    Answerer
  • Tanoshimi,

    Oh, yes---that's absolutely correct.  We have to put the query object in the same tessellation as the index to match the index.

    -Isaac

    Isaac Kunen, Microsoft SQL Server
    Monday, January 19, 2009 8:56 PM
  • /me inserts image of my head exploding.

    Ok. First to my question -> kewlies :) make sence guys. thanks heaps for the confirmation.

    Second - to the use of spatial index's. (this is the part where my head exploded). Is it worth me making a new topic specifically about indexes -> basically, i thought i understood them but i don't so i have a bunch of questions about it (inspired by Tanoshimi's post). Better to start a new topic than to quote from here and continute this thread?

    -Pure Krome-
    Tuesday, January 20, 2009 1:00 AM
  • Shoot: I hate it when heads do that.

    Maybe a new thread would be best, but I'm happy to try to answer them anywhere.  :)

    -Isaac

    Isaac Kunen, Microsoft SQL Server
    Tuesday, January 20, 2009 2:46 AM
  • rgr. on it now. cheers Isaac :)

    [i love this spatial stuff ... just need to figure how to maximize the toolset :P]

    -Pure Krome-
    Tuesday, January 20, 2009 3:02 AM