locked
Extracting boundary of a MultiPolygon (Geometry) RRS feed

  • Question

  • Have had a search and so far coming up with nothing, so I'll turn it over to the professionals . . . 

    Is there a SQL Spatial function that will return only the enveloping boundary of a multi polygon? I have many small regions stored in SQL 2008 as Geometry type (they also exist as Geography but I've had trouble in other areas using Geog, so prefer to keep them as Geom)

    I've tried Reduce, and ConvexHull but this turns it into a simple "blob" for want of a better term.

     

    To illustrate what I'm trying to do, imagine I have the mainland US States as Geometry types. I create a MultiPolygon of "USA" and add all the states to this MultiPolygon. 

    When I render it to BingMaps I don't want all the internal state border shown, just one big polygon that follows the coasts / Mex/Can borders. 

    I'm hoping there's a simple SQL function that I've overlooked in my searching! 

     

    All help appreciated, thanks

     

    Joel

    Tuesday, September 20, 2011 8:36 PM

Answers

  • Functions that operate on MultiXXX geometries tend to act on each geometry in the collection in isolation, so you won't find a function that returns only the "exteriormost" boundary from a collection.

    What you need to do instead is to create a single Polygon by union'ing all the contiguous states together. This is easily done in SQL Server Denali using the geometry::UnionAggregate function, but you can do it in SQL Server 2008 too by iteratively STUnion()ing each element from your table to the next. Once you've created a single element, calling STBoundary() should give you the result you're after.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Marked as answer by JGallagher Tuesday, September 20, 2011 9:19 PM
    Tuesday, September 20, 2011 9:13 PM
    Answerer

All replies

  • Functions that operate on MultiXXX geometries tend to act on each geometry in the collection in isolation, so you won't find a function that returns only the "exteriormost" boundary from a collection.

    What you need to do instead is to create a single Polygon by union'ing all the contiguous states together. This is easily done in SQL Server Denali using the geometry::UnionAggregate function, but you can do it in SQL Server 2008 too by iteratively STUnion()ing each element from your table to the next. Once you've created a single element, calling STBoundary() should give you the result you're after.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    • Marked as answer by JGallagher Tuesday, September 20, 2011 9:19 PM
    Tuesday, September 20, 2011 9:13 PM
    Answerer
  • thanks, worked a treat!

    Tuesday, September 20, 2011 9:20 PM