locked
How to STUnion Multiple Geographies RRS feed

  • Question

  • I have a table of polygons and I'd like to union some of them. The eventual aim is to get the envelope (that bit I can do).

    Intuitively, I'm thinking of a recursive CTE, but it's not obvious to me how to construct it.

    Can anyone suggest a template for using STUnion to recursively join polygons?

    TIA

    Charles

    Thursday, October 27, 2011 1:49 PM

Answers

  • This is available as a built-in in SQL Server 2012. http://blogs.msdn.com/b/edkatibah/archive/2011/04/27/spatial-aggregates-in-sql-server-denali.aspx. Until then, there’s almost equivalent code available as part of the SQL Server Spatial Tools library on CodePlex http://sqlspatialtools.codeplex.com/.
     
    Cheers, Bob
     
    "ckl42" wrote in message news:729fbfdb-f920-41b8-822b-7a8a18fc6875...

    I have a table of polygons and I'd like to union some of them. The eventual aim is to get the envelope (that bit I can do).

    Intuitively, I'm thinking of a recursive CTE, but it's not obvious to me how to construct it.

    Can anyone suggest a template for using STUnion to recursively join polygons?

    TIA

    Charles

    • Marked as answer by ckl42 Friday, October 28, 2011 8:33 AM
    Friday, October 28, 2011 4:53 AM

All replies

  • This is available as a built-in in SQL Server 2012. http://blogs.msdn.com/b/edkatibah/archive/2011/04/27/spatial-aggregates-in-sql-server-denali.aspx. Until then, there’s almost equivalent code available as part of the SQL Server Spatial Tools library on CodePlex http://sqlspatialtools.codeplex.com/.
     
    Cheers, Bob
     
    "ckl42" wrote in message news:729fbfdb-f920-41b8-822b-7a8a18fc6875...

    I have a table of polygons and I'd like to union some of them. The eventual aim is to get the envelope (that bit I can do).

    Intuitively, I'm thinking of a recursive CTE, but it's not obvious to me how to construct it.

    Can anyone suggest a template for using STUnion to recursively join polygons?

    TIA

    Charles

    • Marked as answer by ckl42 Friday, October 28, 2011 8:33 AM
    Friday, October 28, 2011 4:53 AM
  • Thanks Bob. It looks just the ticket.

    Charles

    [As an exercise, I'd still be interested to hear from anyone who can suggest a solution just using 2008 T-SQL]

    Friday, October 28, 2011 8:33 AM
  • Use the STUnion() method with a cursor.

    - Ed


    Ed Katibah Spatial Ed Blog
    Friday, October 28, 2011 3:38 PM
  • Hi Ed, any chance of a set-based method?

    Charles

    Friday, October 28, 2011 3:50 PM
  • Here is an example of a cte I used to combine multiple segments of a stream/river  into one:

    -- The id of the stream we are looking for
    DECLARE @GNIS_ID nvarchar(10) = '01469788';

    -- Get all stream segements with the target id that make up the whole stream
    WITH streamSegments AS (
    SELECT  row_number() OVER(ORDER BY OBJECTID) AS rowNum, Shape
    FROM 
    basegeo.dbo.nhdflowline f
    WHERE GNIS_ID = @GNIS_ID
    ),

    -- Union each segment to the last starting with the first row (rowNum = 1)
    streams AS (
    SELECT rowNum, Shape FROM streamSegments WHERE rowNum = 1

    UNION ALL

    -- Get the next stream segment from streamSegments
    SELECT rowNum = s.rowNum + 1, s.Shape.STUnion(a.Shape)
    FROM streams s
    JOIN  streamSegments a
    ON a.rowNum = s.rowNum + 1
    )
    -- Get the last segment that has been merged with the other segments
    SELECT TOP  1 Shape FROM streams
    ORDER BY rowNum DESC



    • Edited by Max Banas Friday, October 7, 2016 2:54 AM
    Thursday, October 6, 2016 9:20 PM