STIntersect query, single or multi polygon fastest? RRS feed

  • Question

  • Hi guys,

    I have a set of rather complex polygons which define the counties of the UK and I'm using spatial services in SQL 2008 to query if specified points/polygons are within the UK. My question is this; is it faster to combine all these boundaries and check the single resulting polygon, or is it faster to have a multi-polygon object and query against that?

    I'm figuring that the multi-polygon would be quicker if spatial functions do a simply boundary check on each polygon within the multi-polygon. My understanding is that "point in polygon" functions are quite expensive so I wonder if it's faster to chop out the majority of smaller polygons and then perform the point-in-polygon function on a single smaller polygon, rather than performing point-in-polygon on a large complex polygon.

    I hope i've explained this clearly enough. I'd appreciate any ideas/feedback.

    Thanks in advance

    Friday, December 19, 2008 9:46 AM


  • Hi Alan,

    Internally, while we keep track of whether the instance is a polygon or a multipolygon, they are really treated the same: as sets of rings.  I would expect absolutely no difference in the performance between two objects where one of them is a polygon and one is a multipolygon, so if that's your choice, don't sweat it.

    But you might get better performance if you treat the UK as a set of of separate polygons.  If you place them in a table, and build a spatial index, then the spatial index may be able to take care of a number of the polygons without ever going to the underlying geometry library.  For those objects that do make it to the library, the problem will be simpler, and therefore faster to solve.


    Isaac Kunen, Microsoft SQL Server
    • Marked as answer by Isaac Kunen Tuesday, January 6, 2009 6:47 PM
    Friday, December 19, 2008 3:30 PM