Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Extract elements of SQL Server Spatial geometry object

Tuesday January 03 2012 at 11:16

KeywordsSTExtract extract point linestring line polygon geometrycollection STDumpRings
Summary

This article presents a function called StExtract that implements the extraction of the elements and sub-elements of a geometry as independent geometries. StDumpRings is created for extracting the rings of polygon objects.

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.

  1. USE [GISDB] -- Change this to your database
  2. GO
  3. DROP FUNCTION [dbo].[STExtract]
  4. GO
  5. CREATE FUNCTION [dbo].[STExtract](@p_geometry geometry,
  6.                                 @p_sub_geom INT = 0 )
  7.   RETURNS @geometries TABLE
  8.    (gid  INTEGER,
  9.     sid  INTEGER,
  10.     geom geometry)  
  11. AS
  12. BEGIN
  13.   DECLARE
  14.     @v_GeometryType VARCHAR(1000),
  15.     @v_sub_n        INT,
  16.     @v_geom_n       INT,
  17.     @geom           geometry;
  18.   BEGIN
  19.     IF ( @p_geometry IS NULL )
  20.       RETURN;
  21.     SET @v_GeometryType = @p_geometry.STGeometryType();
  22.     IF ( @v_GeometryType = 'Point' )
  23.     BEGIN
  24.       INSERT INTO @geometries ( [gid],[sid],[geom] )
  25.            VALUES ( 1,
  26.                     0,
  27.                     @p_geometry );
  28.       RETURN;
  29.     END;
  30.     IF ( @v_GeometryType = 'MultiPoint' )
  31.     BEGIN
  32.       SET @v_geom_n  = 1;
  33.       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
  34.       BEGIN
  35.         INSERT INTO @geometries ( [gid],[sid],[geom] )
  36.              VALUES ( @v_geom_n,
  37.                       0,
  38.                       @p_geometry.STGeometryN(@v_geom_n) );
  39.         SET @v_geom_n = @v_geom_n + 1;
  40.       END;
  41.       RETURN;
  42.     END;
  43.     IF ( @v_GeometryType IN ('LineString') )
  44.     BEGIN
  45.       INSERT INTO @geometries ( [gid],[sid],[geom] )
  46.            VALUES ( 1,
  47.                     0,
  48.                     @p_geometry );
  49.       RETURN;
  50.     END;
  51.     IF ( @v_GeometryType IN ('CircularCurve','CompoundCurve') )
  52.     BEGIN
  53.       IF ( @p_sub_geom = 0 )
  54.       BEGIN
  55.         INSERT INTO @geometries ( [gid],[sid],[geom] )
  56.              VALUES ( 1, 0, @p_geometry );
  57.       RETURN;
  58.       END
  59.       ELSE
  60.       BEGIN
  61.         SET @v_geom_n  = 1;
  62.         WHILE ( @v_geom_n <= @p_geometry.STNumCurves() )
  63.         BEGIN
  64.           INSERT INTO @geometries ( [gid],[sid],[geom] )
  65.                VALUES ( 1,
  66.                         @v_geom_n,
  67.                         @p_geometry.STCurveN(@v_geom_n) );
  68.           SET @v_geom_n = @v_geom_n + 1;
  69.         END;
  70.         RETURN;
  71.      END
  72.     END;
  73.     IF ( @v_GeometryType IN ('MultiLineString') )
  74.     BEGIN
  75.       SET @v_geom_n  = 1;
  76.       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
  77.       BEGIN
  78.         INSERT INTO @geometries ( [gid],[sid],[geom] )
  79.              SELECT @v_geom_n,
  80.                     [sid],
  81.                     [geom]
  82.                FROM dbo.STExtract(@p_geometry.STGeometryN(@v_geom_n),@p_sub_geom);
  83.         SET @v_geom_n = @v_geom_n + 1;
  84.       END;
  85.       RETURN;
  86.     END;
  87.     IF ( @v_GeometryType IN ('Polygon','CurvePolygon') )
  88.     BEGIN
  89.       IF ( @p_sub_geom = 1 )
  90.       BEGIN
  91.         SET @v_sub_n  = 0;
  92.         WHILE ( @v_sub_n < ( 1 + @p_geometry.STNumInteriorRing() ) )
  93.         BEGIN
  94.           IF ( @v_sub_n = 0 )
  95.             SET @geom = @p_geometry.STExteriorRing()
  96.           ELSE
  97.             SET @geom = @p_geometry.STInteriorRingN(@v_sub_n);
  98.           IF ( @v_geometryType = 'CurvePolygon' )
  99.           BEGIN
  100.              INSERT INTO @geometries ( [gid],[sid],[geom] )
  101.              SELECT a.gid, a.sid, a.geom
  102.                FROM dbo.STExtract(@geom,@p_sub_geom) AS a;
  103.           END
  104.           ELSE
  105.           BEGIN
  106.             INSERT INTO @geometries ( [gid],[sid],[geom] )
  107.                VALUES ( 1,
  108.                         @v_sub_n + 1,
  109.                         geometry::STGeomFromText(
  110.                                   CASE WHEN UPPER(@geom.STAsText()) LIKE 'LINESTRING%'
  111.                                        THEN REPLACE(REPLACE(UPPER(@geom.STAsText()),'LINESTRING (','POLYGON (('),')','))')
  112.                                        WHEN UPPER(@geom.STAsText()) LIKE 'COMPOUNDCURVE%'
  113.                                        THEN REPLACE(UPPER(@geom.STAsText()),'COMPOUNDCURVE','CURVEPOLYGON(COMPOUNDCURVE') + ')'
  114.                                        ELSE @geom.STAsText()
  115.                                     END,
  116.                                   @p_geometry.STSrid));
  117.           END;
  118.           SET @v_sub_n = @v_sub_n + 1;
  119.         END;
  120.       END
  121.       ELSE
  122.       BEGIN
  123.         INSERT INTO @geometries ( [gid],[sid],[geom] )
  124.              VALUES ( 1,
  125.                       0,
  126.                       @p_geometry);
  127.       END;
  128.       RETURN;
  129.     END;
  130.     IF ( @v_GeometryType = 'MultiPolygon' )
  131.     BEGIN
  132.       SET @v_geom_n  = 1;
  133.       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
  134.       BEGIN
  135.          IF ( @p_sub_geom = 0 )
  136.          BEGIN
  137.              INSERT INTO @geometries ( [gid],[sid],[geom] )
  138.                   VALUES ( @v_geom_n,
  139.                            0,
  140.                            @p_geometry.STGeometryN(@v_geom_n));
  141.          END
  142.          ELSE
  143.          BEGIN
  144.              INSERT INTO @geometries ( [gid],[sid],[geom] )
  145.              SELECT @v_geom_n,
  146.                     [sid],
  147.                     [geom]
  148.                FROM dbo.STExtract(@p_geometry.STGeometryN(@v_geom_n),@p_sub_geom);
  149.          END;
  150.          SET @v_geom_n = @v_geom_n + 1;
  151.       END;
  152.       RETURN;
  153.     END;
  154.     IF ( @v_GeometryType = 'GeometryCollection' )
  155.     BEGIN
  156.       SET @v_geom_n  = 1;
  157.       WHILE ( @v_geom_n <= @p_geometry.STNumGeometries() )
  158.       BEGIN
  159.          INSERT INTO @geometries ( [gid],[sid],[geom] )
  160.               SELECT @v_geom_n,
  161.                      [sid],
  162.                      [geom]
  163.                 FROM dbo.STExtract(@p_geometry.STGeometryN(@v_geom_n),@p_sub_geom);
  164.         SET @v_geom_n = @v_geom_n + 1;
  165.       END;
  166.       RETURN;
  167.     END;
  168.   END;
  169.   RETURN;
  170. END
  171. GO

Testing this we get.

  1. SELECT 'POINT' AS gtype, gid,sid,geom.STAsText() AS geom FROM dbo.STExtract(geometry::STGeomFromText('POINT(0 0)',0),1) AS gElem UNION ALL
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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),
  11.                                                                                                                       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))),
  12.                                                                                                                       COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778)))',0),0) AS gElem UNION ALL
  13. 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),
  14.                                                                                                                       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))),
  15.                                                                                                                       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.

  1. DROP   FUNCTION STDumpRings;
  2. -- Function: STDumpRings
  3. --
  4. CREATE FUNCTION STDumpRings(@p_geometry geometry)
  5.   RETURNS @rings TABLE
  6.    (pid  INTEGER,
  7.   rid  INTEGER,
  8.   geom geometry)  
  9. AS
  10. BEGIN
  11.   BEGIN
  12.     IF ( @p_geometry IS NULL )
  13.       RETURN;
  14.     IF ( @p_geometry.STGeometryType() NOT IN ('CurvePolygon','Polygon','MultiPolygon') )
  15.     RETURN;
  16.     INSERT INTO @rings ( [pid],[rid],[geom] )
  17.   SELECT [gid],[sid],[geom]
  18.     FROM dbo.STExtract(@p_geometry,1);
  19.   RETURN;
  20.   END;
  21. END
  22. GO

Testing we get:

  1. 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
  2. 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
  3. 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
  4. 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),
  5.                                                                                                                           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))),
  6.                                                                                                                           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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment