locked
Multipolygon results split into Polygons RRS feed

  • Question

  • Hi all,

    I'm looking for a way of spliting the 'multipolygon' spatial results (from an STIntersection query between two polygon datasets) into individual 'polygon' results only. I've found this similar post on the forum which describes methods for splitting a single multipolygon entry into seperate polygons

    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/c2096879-ebf8-4245-8f59-cbe853d00069

    However my results typically have several rows of multipolygon and polygon results. Does anyone know a way to split all of the multipolygons into seperate polygon rows?

    Thanks in advance
    Thursday, July 9, 2009 2:15 PM

Answers

  • Hi there,

    You can put the code in the previous post into a table valued function and then cross-apply that to your table that has multipolygons in it. To demonstrate, first create some sample data:
    CREATE TABLE MultiPolygonsAndPolygons (
      id int IDENTITY,
      geom geometry
      );
    GO
    
    INSERT INTO MultiPolygonsAndPolygons (geom) VALUES
    (geometry::STPolyFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0)),
    (geometry::STMPolyFromText('MULTIPOLYGON(((6 20, 8 20, 8 22, 6 22, 6 20)),((3 6, 6 7, 5 9, 3 6)))', 0)),
    (geometry::STPolyFromText('POLYGON((10 0, 12 0, 12 2, 10 2, 10 0))', 0)),
    (geometry::STMPolyFromText('MULTIPOLYGON(((0 10, 2 10, 2 12, 0 12, 0 10)),((15 10, 25 12, 23 20, 15 20, 15 10)))', 0));
    GO
    

    This table contains 4 rows - two polygons and two multipolygons (each containing two polygons). They look like this (notice that the large irregular purple polygon and the small orange triangle are the same colour as the purple square and the orange square respectively, because they are part of the same multipolygon):



    Now create a table-valued function that splits each multipolygon as follows:
    CREATE FUNCTION dbo.SplitMultiPolygons (@MultiPolygon geometry)
    RETURNS @Results TABLE (result geometry) AS
    BEGIN
      DECLARE @i int = 1;
      WHILE @i <= @MultiPolygon.STNumGeometries()  
      BEGIN 
        INSERT INTO @Results VALUES (@MultiPolygon.STGeometryN(@i))  
        SET @i = @i + 1  
      END
      RETURN
    END;
    GO

    You call the function like this:
    SELECT
      mp.id,
      p.result
    FROM 
      MultiPolygonsAndPolygons mp
    CROSS APPLY
      dbo.SplitMultiPolygons (geom) AS p
    GO
    
     
    The results give one row for each polygon, together with the id of the multipolygon of which they were originally part:
    id    result
    1    0x0000000001040...
    2    0x0000000001040...
    2    0x00000000010404...
    3    0x00000000010405...
    4    0x00000000010405...
    4    0x00000000010405...


    And they look like this (notice now all different colours):




    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, July 9, 2009 2:59 PM
    Answerer
  • At the moment, this query splits the source data set into individual polygons first, then calculates the intersection with the @intersect polygon. Seeing as the result of STIntersection() gives the simplest geometry type that is capable of representing all of the points contained in the intersection, this is perfectly possible to return a multipolygon.

    If what you want to do is to ensure that, in the final output, you only have polygon instances, you need to do the intersection first, and then do the multipolygon split. It's probably easiest to demonstrate this explicitly by doing the query in two steps:

    DECLARE @intersect geometry
    SET @intersect = geometry::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326)
    
    -- Work out the intersection first
    DECLARE @Intermediate TABLE (geom geometry, geomtype varchar(32));
    INSERT INTO @Intermediate
    SELECT 
    @intersect.STIntersection(geom), 
    @intersect.STIntersection(geom).STGeometryType()
    FROM country
    
    
    -- At this point, @Intermediate may contain a mixture of polygons and multipolygons, so now split it into individual rows
    SELECT
    @intersect.STIntersection(p.result) As data,
    @intersect.STIntersection(p.result).STGeometryType() AS geometrytype
    FROM @Intermediate c
    CROSS APPLY
    dbo.SplitMultiPolygons (c.geom) AS p
    WHERE (@intersect.STIntersects(c.geom)=1)
    
    -- Results are now all polygons

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, July 10, 2009 1:36 PM
    Answerer

All replies

  • Hi there,

    You can put the code in the previous post into a table valued function and then cross-apply that to your table that has multipolygons in it. To demonstrate, first create some sample data:
    CREATE TABLE MultiPolygonsAndPolygons (
      id int IDENTITY,
      geom geometry
      );
    GO
    
    INSERT INTO MultiPolygonsAndPolygons (geom) VALUES
    (geometry::STPolyFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0)),
    (geometry::STMPolyFromText('MULTIPOLYGON(((6 20, 8 20, 8 22, 6 22, 6 20)),((3 6, 6 7, 5 9, 3 6)))', 0)),
    (geometry::STPolyFromText('POLYGON((10 0, 12 0, 12 2, 10 2, 10 0))', 0)),
    (geometry::STMPolyFromText('MULTIPOLYGON(((0 10, 2 10, 2 12, 0 12, 0 10)),((15 10, 25 12, 23 20, 15 20, 15 10)))', 0));
    GO
    

    This table contains 4 rows - two polygons and two multipolygons (each containing two polygons). They look like this (notice that the large irregular purple polygon and the small orange triangle are the same colour as the purple square and the orange square respectively, because they are part of the same multipolygon):



    Now create a table-valued function that splits each multipolygon as follows:
    CREATE FUNCTION dbo.SplitMultiPolygons (@MultiPolygon geometry)
    RETURNS @Results TABLE (result geometry) AS
    BEGIN
      DECLARE @i int = 1;
      WHILE @i <= @MultiPolygon.STNumGeometries()  
      BEGIN 
        INSERT INTO @Results VALUES (@MultiPolygon.STGeometryN(@i))  
        SET @i = @i + 1  
      END
      RETURN
    END;
    GO

    You call the function like this:
    SELECT
      mp.id,
      p.result
    FROM 
      MultiPolygonsAndPolygons mp
    CROSS APPLY
      dbo.SplitMultiPolygons (geom) AS p
    GO
    
     
    The results give one row for each polygon, together with the id of the multipolygon of which they were originally part:
    id    result
    1    0x0000000001040...
    2    0x0000000001040...
    2    0x00000000010404...
    3    0x00000000010405...
    4    0x00000000010405...
    4    0x00000000010405...


    And they look like this (notice now all different colours):




    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, July 9, 2009 2:59 PM
    Answerer
  • Thanks v.much for the help and quick reply to my question!

    Now im trying to understand how to use the geometry returned from this function in combination with other spatial options. To test this, i am trying to write a query to return the intersection between one polygon dataset (world country borders) with another polygon shape. I want to return single polygons for areas where the two areas overlap 

    I've set up the dbo.SplitMultiPolygons function (as above but as a geography rather than geometry type), and my SQL looks like this ...

    DECLARE @intersect geography
    SET
    @intersect = geography::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326
    )
    SELECT c.name AS country
    ,
    @intersect.STIntersection(p.result) As data
    ,
    @intersect.STIntersection(p.result).STGeometryType() As shapetype
    FROM countries c
    CROSS
    APPLY
    dbo.SplitMultiPolygons (c.geom) AS p
    WHERE (@intersect.STIntersects(c.geom)=1
    )

    The result seems to be splitting most of the multipolygons into single polygons as i wanted, but there are still several multipolygons left over, as well as several empty Geometrycollection items. I think i'm placing the 'p.result' in the wrong part of the SQL but im not sure how to correct this. Are you able to suggest improvements to this query?

    Thanks!



    Friday, July 10, 2009 12:12 PM
  • At the moment, this query splits the source data set into individual polygons first, then calculates the intersection with the @intersect polygon. Seeing as the result of STIntersection() gives the simplest geometry type that is capable of representing all of the points contained in the intersection, this is perfectly possible to return a multipolygon.

    If what you want to do is to ensure that, in the final output, you only have polygon instances, you need to do the intersection first, and then do the multipolygon split. It's probably easiest to demonstrate this explicitly by doing the query in two steps:

    DECLARE @intersect geometry
    SET @intersect = geometry::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326)
    
    -- Work out the intersection first
    DECLARE @Intermediate TABLE (geom geometry, geomtype varchar(32));
    INSERT INTO @Intermediate
    SELECT 
    @intersect.STIntersection(geom), 
    @intersect.STIntersection(geom).STGeometryType()
    FROM country
    
    
    -- At this point, @Intermediate may contain a mixture of polygons and multipolygons, so now split it into individual rows
    SELECT
    @intersect.STIntersection(p.result) As data,
    @intersect.STIntersection(p.result).STGeometryType() AS geometrytype
    FROM @Intermediate c
    CROSS APPLY
    dbo.SplitMultiPolygons (c.geom) AS p
    WHERE (@intersect.STIntersects(c.geom)=1)
    
    -- Results are now all polygons

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, July 10, 2009 1:34 PM
    Answerer
  • At the moment, this query splits the source data set into individual polygons first, then calculates the intersection with the @intersect polygon. Seeing as the result of STIntersection() gives the simplest geometry type that is capable of representing all of the points contained in the intersection, this is perfectly possible to return a multipolygon.

    If what you want to do is to ensure that, in the final output, you only have polygon instances, you need to do the intersection first, and then do the multipolygon split. It's probably easiest to demonstrate this explicitly by doing the query in two steps:

    DECLARE @intersect geometry
    SET @intersect = geometry::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326)
    
    -- Work out the intersection first
    DECLARE @Intermediate TABLE (geom geometry, geomtype varchar(32));
    INSERT INTO @Intermediate
    SELECT 
    @intersect.STIntersection(geom), 
    @intersect.STIntersection(geom).STGeometryType()
    FROM country
    
    
    -- At this point, @Intermediate may contain a mixture of polygons and multipolygons, so now split it into individual rows
    SELECT
    @intersect.STIntersection(p.result) As data,
    @intersect.STIntersection(p.result).STGeometryType() AS geometrytype
    FROM @Intermediate c
    CROSS APPLY
    dbo.SplitMultiPolygons (c.geom) AS p
    WHERE (@intersect.STIntersects(c.geom)=1)
    
    -- Results are now all polygons

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, July 10, 2009 1:36 PM
    Answerer
  • Thanks Tanoshimi, its working perfectly now!
    Friday, July 10, 2009 4:08 PM
  • Brilliant answer by Tanoshimi, saved me lots of time and made my app awesome, thanks
    Thursday, July 9, 2015 12:39 PM