Top 5 Recent Articles

ARTICLES CATEGORIES

STExtract: Extract elements of a geometry object

Of the functions I have written for Oracle, those that allow me to extract (sub)elements of compound (objects consisting of linestrings and circular arcs) and multipart geometries have found use time and time again in such activities as:

  • Filtering Rings of Polygons;
  • Extracting homogeneous objects (eg point, line or polygon) from geometry collections.

This article presents a function call STExtract that implements the extraction of the elements and sub-elements of a geometry as independent geometries.

 USE [GISDB] -- Change this to your database
 GO
 DROP FUNCTION [dbo].[STExtract]
 GO
 CREATE FUNCTION [dbo].[STExtract](@p_geometry geometry,
                                 @p_sub_geom INT = 0 )
   RETURNS @geometries TABLE
    (gid  INTEGER,
     sid  INTEGER,
     geom geometry)
 AS
 BEGIN
   DECLARE
     @v_GeometryType VARCHAR(1000),
     @v_sub_n        INT,
     @v_geom_n       INT,
     @geom           geometry;
   BEGIN
     IF ( @p_geometry IS NULL )
       RETURN;
     SET @v_GeometryType = @p_geometry.STGeometryType();
     IF ( @v_GeometryType = 'Point' )
     BEGIN
       INSERT INTO @geometries ( [gid],[sid],[geom] )
            VALUES ( 1,
                     0,
                     @p_geometry );
       RETURN;
     END;
     IF ( @v_GeometryType = 'MultiPoint' )
     BEGIN
       SET @v_geom_n  = 1;
       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
       BEGIN
         INSERT INTO @geometries ( [gid],[sid],[geom] )
              VALUES ( @v_geom_n,
                       0,
                       @p_geometry.STGeometryN(@v_geom_n) );
         SET @v_geom_n = @v_geom_n + 1;
       END;
       RETURN;
     END;
     IF ( @v_GeometryType IN ('LineString') )
     BEGIN
       INSERT INTO @geometries ( [gid],[sid],[geom] )
            VALUES ( 1,
                     0,
                     @p_geometry );
       RETURN;
     END;
     IF ( @v_GeometryType IN ('CircularCurve','CompoundCurve') )
     BEGIN
       IF ( @p_sub_geom = 0 )
       BEGIN
         INSERT INTO @geometries ( [gid],[sid],[geom] )
              VALUES ( 1, 0, @p_geometry );
       RETURN;
       END
       ELSE
       BEGIN
         SET @v_geom_n  = 1;
         WHILE ( @v_geom_n <= @p_geometry.STNumCurves() )
         BEGIN
           INSERT INTO @geometries ( [gid],[sid],[geom] )
               VALUES ( 1,
                         @v_geom_n,
                         @p_geometry.STCurveN(@v_geom_n) ); 
           SET @v_geom_n = @v_geom_n + 1;
         END;
         RETURN;
      END
     END;
     IF ( @v_GeometryType IN ('MultiLineString') )
     BEGIN
       SET @v_geom_n  = 1;
       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
       BEGIN
         INSERT INTO @geometries ( [gid],[sid],[geom] )
              SELECT @v_geom_n,
                     [sid],
                     [geom]
                FROM dbo.STExtract(@p_geometry.STGeometryN(@v_geom_n),@p_sub_geom);
         SET @v_geom_n = @v_geom_n + 1;
       END;
       RETURN;
     END;
     IF ( @v_GeometryType IN ('Polygon','CurvePolygon') )
     BEGIN
       IF ( @p_sub_geom = 1 )
       BEGIN
         SET @v_sub_n  = 0;
         WHILE ( @v_sub_n < ( 1 + @p_geometry.STNumInteriorRing() ) )
         BEGIN
           IF ( @v_sub_n = 0 )
             SET @geom = @p_geometry.STExteriorRing()
           ELSE
             SET @geom = @p_geometry.STInteriorRingN(@v_sub_n);
           IF ( @v_geometryType = 'CurvePolygon' )
           BEGIN
              INSERT INTO @geometries ( [gid],[sid],[geom] )
              SELECT a.gid, a.sid, a.geom
                FROM dbo.STExtract(@geom,@p_sub_geom) AS a;
           END
           ELSE
           BEGIN
             INSERT INTO @geometries ( [gid],[sid],[geom] )
                VALUES ( 1,
                         @v_sub_n + 1,
                         geometry::STGeomFromText(
                                   CASE WHEN UPPER(@geom.STAsText()) LIKE 'LINESTRING%'
                                        THEN REPLACE(REPLACE(UPPER(@geom.STAsText()),'LINESTRING (','POLYGON (('),')','))')
                                        WHEN UPPER(@geom.STAsText()) LIKE 'COMPOUNDCURVE%'
                                        THEN REPLACE(UPPER(@geom.STAsText()),'COMPOUNDCURVE','CURVEPOLYGON(COMPOUNDCURVE') + ')'
                                        ELSE @geom.STAsText()
                                     END,
                                   @p_geometry.STSrid));
           END;
           SET @v_sub_n = @v_sub_n + 1;
         END;
       END
       ELSE
       BEGIN
         INSERT INTO @geometries ( [gid],[sid],[geom] )
              VALUES ( 1,
                       0,
                       @p_geometry);
       END;
       RETURN;
     END;
     IF ( @v_GeometryType = 'MultiPolygon' )
     BEGIN
       SET @v_geom_n  = 1;
       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
       BEGIN
          IF ( @p_sub_geom = 0 )
          BEGIN
              INSERT INTO @geometries ( [gid],[sid],[geom] )
                   VALUES ( @v_geom_n,
                            0,
                            @p_geometry.STGeometryN(@v_geom_n));
          END
          ELSE
          BEGIN
              INSERT INTO @geometries ( [gid],[sid],[geom] )
              SELECT @v_geom_n,
                     [sid],
                     [geom]
                FROM dbo.STExtract(@p_geometry.STGeometryN(@v_geom_n),@p_sub_geom);
          END;
          SET @v_geom_n = @v_geom_n + 1;
       END;
       RETURN;
     END;
     IF ( @v_GeometryType = 'GeometryCollection' )
     BEGIN
       SET @v_geom_n  = 1;
       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
       BEGIN
          INSERT INTO @geometries ( [gid],[sid],[geom] )
               SELECT @v_geom_n,
                      [sid],
                      [geom]
                 FROM dbo.STExtract(@p_geometry.STGeometryN(@v_geom_n),@p_sub_geom);
         SET @v_geom_n = @v_geom_n + 1;
       END;
       RETURN;
     END;
   END;
   RETURN;
 END
 GO

Testing this we get.

 SELECT 'POINT' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('POINT(0 0)',0),1) AS gElem UNION ALL
 SELECT 'MPONT' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('MULTIPOINT((0 0),(20 0))',0),1) AS gElem UNION ALL
 SELECT 'LINES' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)',0),1) AS gElem UNION ALL
 SELECT 'MLINE' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('MULTILINESTRING((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0),1) AS gElem UNION ALL
 SELECT 'POLYI' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0),1) AS gElem UNION ALL
 SELECT 'MPLYO' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('MULTIPOLYGON (((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0),1) AS gElem UNION ALL
 SELECT 'MPLYI' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)), ((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0),1) AS gElem UNION ALL
 SELECT 'CPLY0' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778)))',0),0) AS gElem UNION ALL
 SELECT 'CPLY1' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778)))',0),1) AS gElem UNION ALL
 SELECT 'GEOC0' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0),
                                                                                                                       CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))),
                                                                                                                       COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778)))',0),0) AS gElem UNION ALL
 SELECT 'GEOC1' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0),
                                                                                                                       CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))),
                                                                                                                       COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778)))',0),1) AS gElem;

Which results in:

GTYPE GID SID GEOM
POINT 1 0 POINT (0 0)
MPONT 1 0 POINT (0 0)
MPONT 2 0 POINT (20 0)
LINES 1 0 LINESTRING (0 0, 20 0, 20 20, 0 20, 0 0)
MLINE 1 0 LINESTRING (0 0, 20 0, 20 20, 0 20, 0 0)
MLINE 2 0 LINESTRING (10 10, 10 11, 11 11, 11 10, 10 10)
MLINE 3 0 LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)
POLYI 1 1 POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0))
POLYI 1 2 POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
POLYI 1 3 POLYGON ((5 5, 5 7, 7 7, 7 5, 5 5))
MPLYO 1 1 POLYGON ((80 80, 100 80, 100 100, 80 100, 80 80))
MPLYO 2 1 POLYGON ((110 110, 150 110, 150 150, 110 150, 110 110))
MPLYI 1 1 POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0))
MPLYI 1 2 POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
MPLYI 1 3 POLYGON ((5 5, 5 7, 7 7, 7 5, 5 5))
MPLYI 2 1 POLYGON ((80 80, 100 80, 100 100, 80 100, 80 80))
MPLYI 3 1 POLYGON ((110 110, 150 110, 150 150, 110 150, 110 110))
CPLY0 1 0 CURVEPOLYGON (COMPOUNDCURVE ((0 -23.43778, 0 23.43778), CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778), (-90 23.43778, -90 -23.43778), CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)))
CPLY1 1 1 LINESTRING (0 -23.43778, 0 23.43778)
CPLY1 1 2 CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778)
CPLY1 1 3 LINESTRING (-90 23.43778, -90 -23.43778)
CPLY1 1 4 CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)
GEOC0 1 0 LINESTRING (0 0, 20 0, 20 20, 0 20, 0 0)
GEOC0 2 0 CURVEPOLYGON (COMPOUNDCURVE ((0 -23.43778, 0 23.43778), CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778), (-90 23.43778, -90 -23.43778), CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)))
GEOC0 3 0 COMPOUNDCURVE (CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778))
GEOC1 1 0 LINESTRING (0 0, 20 0, 20 20, 0 20, 0 0)
GEOC1 2 1 LINESTRING (0 -23.43778, 0 23.43778)
GEOC1 2 2 CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778)
GEOC1 2 3 LINESTRING (-90 23.43778, -90 -23.43778)
GEOC1 2 4 CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)
GEOC1 3 1 CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)
GEOC1 3 2 LINESTRING (0 -23.43778, 0 23.43778)

On top of STExtract we can build a function that dumps the rings of polygon geometries as separate polygon objects (with one outer ring). The function is called STDumpRings and is as follows.

 DROP   FUNCTION STDumpRings;
 -- Function: STDumpRings
 --
 CREATE FUNCTION STDumpRings(@p_geometry geometry)
   RETURNS @rings TABLE
    (pid  INTEGER,
   rid  INTEGER,
   geom geometry)
 AS
 BEGIN
   BEGIN
     IF ( @p_geometry IS NULL )
       RETURN;
     IF ( @p_geometry.STGeometryType() NOT IN ('CurvePolygon','Polygon','MultiPolygon') )
     RETURN;
     INSERT INTO @rings ( [pid],[rid],[geom] )
   SELECT [gid],[sid],[geom]
     FROM dbo.STExtract(@p_geometry,1);
   RETURN;
   END;
 END
 GO

Testing we get:

 SELECT 'MLINE' AS gtype, pid,rid,geom.STAsText() AS geom FROM dbo.STDumpRings(geometry::STGeomFromText('MULTILINESTRING((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) AS pGeom UNION ALL
 SELECT 'POLYI' AS gtype, pid,rid,geom.STAsText() AS geom FROM dbo.STDumpRings(geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) AS pGeom UNION ALL
 SELECT 'MPOLI' AS gtype, pid,rid,geom.STAsText() AS geom FROM dbo.STDumpRings(geometry::STGeomFromText('MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)), ((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0)) AS pGeom UNION ALL
 SELECT 'GEOMC' AS gtype, pid,rid,geom.STAsText() AS geom FROM dbo.STDumpRings(geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0),
                                                                                                                           CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))),
                                                                                                                           COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778)))',0)) AS pGeom ;

Resulting in:

GTYPE PID RID GEOM
POLYI 1 1 POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0))
POLYI 1 2 POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
POLYI 1 3 POLYGON ((5 5, 5 7, 7 7, 7 5, 5 5))
MPOLI 1 1 POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0))
MPOLI 1 2 POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
MPOLI 1 3 POLYGON ((5 5, 5 7, 7 7, 7 5, 5 5))
MPOLI 2 1 POLYGON ((80 80, 100 80, 100 100, 80 100, 80 80))
MPOLI 3 1 POLYGON ((110 110, 150 110, 150 150, 110 150, 110 110))

Note that the LINESTRING produced no output as expected. However, neither did the last GEOMETRYCOLLECTION even though it contains polygon objects with rings. This is simply a matter of programming. Should STDumpRings look inside GeometryCollections? Or is it up to the programmer to feed in polygons via use of dbo.Extract()? If you don’t like the coding of STDumpRings change it to how you want it to behave (the code is open source after all).

In a related article I will show you how to process the result of STIntersection between two polygons to extract only the areas of intersection.

I hope this is of use to someone out there.