Extracting geometry types from geometry/geography objects in SQL Server 2008

I have pointed out in another blog that the result of an STIntersection() between two polygon objects could result in an object that is not a polygon or multipolygon.

 SELECT
 geometry::STGeomFromText('POLYGON((100 0,400 0,400 300,100 300,100 0))',0).STIntersection(
 geometry::STGeomFromText('POLYGON((-175 0,100 0,0 75,100 75,100 200,200 325,200 525,-175 525,-175 0))',0)).STAsText() AS INTERSECTION;

Note, the the result is a geometry collection object, composed of a line (1,2,1), a point (5,1,1) and a polygon with a single outer shell.

But if we wanted to write the resultant geometry to a table that contains only polygons, we would need to implement a function that will allow us to extract only the polygons from the resultant collection.

What I have done is write an ExtractGeom function that will allow a user to extract any geometry type from a geometry collection. The function will also allow a user to extract single geometry types (eg POINT) from their multi-geometry (eg MULTIPOINT) equivalents.

Here is the function.

  USE [GISDB] -- change this to your database
  GO
  -- Create function
  CREATE FUNCTION dbo.ExtractGeom( @p_geom     geometry,
                                     @p_geometryType VARCHAR(1000) )
  RETURNS @geoms TABLE (
    geom geometry
  )
  AS
  BEGIN
    DECLARE
      @v_requiredType VARCHAR(1000) = UPPER(@p_geometryType),
      @v_GeometryType VARCHAR(1000),
      @geomn          INT;
    BEGIN
      -- Test parameters
      IF ( @p_geom IS NULL )
        RETURN;
      IF ( ( @v_requiredType IS NULL )
             OR
           ( @v_requiredType NOT IN ('POINT','MULTIPOINT','LINESTRING','MULTILINESTRI NG','POLYGON','MULTIPOLYGON' ) )	
         )
      BEGIN
        -- PRINT '@v_requiredType (' + @v_requiredType + ') is not one of POINT,MULTIPOINT,LINESTRING,MULTILINESTRING,POLYGON,MULTIPOLYGON.';
        RETURN;
      END;
      -- If both geometry types are same, we can just return the geometry
      --
      SET @v_GeometryType = UPPER(@p_geom.STGeometryType());
      IF ( @v_requiredType = @v_GeometryType )
      BEGIN
        INSERT INTO @Geoms ( [geom] ) VALUES ( @p_geom );
        RETURN;
     END;
     -- Are we wanting a single geometry type from a multi geometry?
     --
     IF ( ( CHARINDEX('MULTI',@v_geometryType) <> 0 )
          AND
          ( CHARINDEX(@v_requiredType,@v_geometryType) <> 0 )
        )
     BEGIN
       SET @geomn = 1;
       WHILE ( @geomn <= @p_geom.STNumGeometries() )
       BEGIN
         INSERT INTO @geoms ( [geom] ) VALUES ( @p_geom.STGeometryN(@geomn) );
         SET @geomn = @geomn + 1;
       END;
       RETURN;
     END;
     -- We only process collections
     --
     IF ( @v_GeometryType <> UPPER('GeometryCollection') )
     BEGIN
         -- PRINT '@p_geometry'' type (' + @v_geometryType + ') is not a GeometryCollection.';
         RETURN;
     END;
     -- Do Processing
     --
     SET @geomn = 1;
     WHILE ( @geomn <= @p_geom.STNumGeometries() )
     BEGIN
       SET @v_geometryType = UPPER(@p_geom.STGeometryN(@geomn).STGeometryType());
       IF ( @v_GeometryType = UPPER(@v_requiredType) )
       BEGIN
          INSERT INTO @Geoms ( [geom] ) VALUES ( @p_geom.STGeometryN(@geomn) );
       END;
       SET @geomn = @geomn + 1;
     END;
     RETURN;
   END;
 END
 GO

Testing

Testing it with the above intersection geometry gives us.

 SELECT e.geom.STAsText() AS IntersectionGeom
   FROM dbo.ExtractGeom(
 geometry::STGeomFromText('POLYGON((100 0,400 0,400 300,100 300,100 0))',0).STIntersection(
 geometry::STGeomFromText('POLYGON((-175 0,100 0,0 75,100 75,100 200,200 325,200 525,-175 525,-175 0))',0)),
 'POLYGON') AS e;

As expected.

Now some more tests.

 WITH testCollection AS
 (
    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION (
 POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)),
 POINT (2 3),
 MULTIPOINT ((1 1), (2 2), (3 3)),
 LINESTRING (2 3, 3 4),
 MULTILINESTRING ((2 3, 3 4), (1 1, 2 2)),
 POINT (4 5),
 MULTIPOINT ((1 1), (2 2)),
 POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)),
 MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0) AS geom
 )
 SELECT 'POINT' AS extractType, e.geom.STAsText() AS geomWKT
   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'POINT') AS e
 UNION ALL
 SELECT 'LINESTRING' AS extractType, e.geom.STAsText() AS geomWKT
   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'LINESTRING') AS e
 UNION ALL
 SELECT 'POLYGON' AS extractType, e.geom.STAsText() AS geomWKT
   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'POLYGON') AS e
 UNION ALL
 SELECT 'MULTIPOINT' AS extractType, e.geom.STAsText() AS geomWKT
   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'MULTIPOINT') AS e
 UNION ALL
 SELECT 'MULTILINESTRING' AS extractType, e.geom.STAsText() AS geomWKT
   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'MULTILINESTRING') AS e
 UNION ALL
 SELECT 'MULTIPOLYGON' AS extractType, e.geom.STAsText() AS geomWKT
   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'MULTIPOLYGON') AS e;

Let’s now extract single objects from their multi equivalients.

 -- Now extra singles from multis...
 --
 SELECT 'POINT' AS extractType,e.geom.STAsText() AS geomWKT
   FROM dbo.ExtractGeom(geometry::STGeomFromText('MULTIPOINT((1 1),(2 2),(3 3))',0),'POINT') AS e
 UNION ALL
 SELECT 'LINESTRING' AS extractType,e.geom.STAsText() AS geomWKT
   FROM dbo.ExtractGeom(geometry::STGeomFromText('MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2))',0),'LINESTRING') AS e
   UNION ALL
 SELECT 'POLYGON' AS extractType,e.geom.STAsText() AS geomWKT
   FROM dbo.ExtractGeom(geometry::STGeomFromText('MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0), (40 40, 40 60, 60 60, 60 40, 40 40)))',0),'POLYGON') AS e;

Wrappers

Sometimes, having a set of “wrapper” functions is rather useful. Here are a bunch over the top of our ExtractGeom function.

 CREATE FUNCTION dbo.ExtractPolyGeom( @p_polygon geometry )
 RETURNS @geoms TABLE (
   geom geometry
 )
 AS
 BEGIN
   INSERT INTO @geoms
   SELECT a.geom
     FROM dbo.ExtractGeom(@p_polygon,'POLYGON') AS a;
   RETURN;
 END
 GO
 -- Linestring
 CREATE FUNCTION dbo.ExtractLineGeom( @p_linestring geometry )
 RETURNS @geoms TABLE (
   geom geometry
 )
 AS
 BEGIN
   INSERT INTO @geoms
   SELECT a.geom
     FROM dbo.ExtractGeom(@p_linestring,'LINESTRING') AS a;
   RETURN;
 END
 GO
 -- Point
 CREATE FUNCTION dbo.ExtractPointGeom( @p_point geometry )
 RETURNS @geoms TABLE (
   geom geometry
 )
 AS
 BEGIN
   INSERT INTO @geoms
   SELECT a.geom
     FROM dbo.ExtractGeom(@p_POINT,'POINT') AS a;
   RETURN;
 END
 GO
 -- MultiPolyon
 CREATE FUNCTION dbo.ExtractMPolyGeom( @p_multipolygon geometry )
 RETURNS @geoms TABLE (
   geom geometry
 )
 AS
 BEGIN
   INSERT INTO @geoms
   SELECT a.geom
     FROM dbo.ExtractGeom(@p_multipolygon,'MULTIPOLYGON') AS a;
   RETURN;
 END
 GO
 -- MultiLinestring
 CREATE FUNCTION dbo.ExtractMLineGeom( @p_multilinestring geometry )
 RETURNS @geoms TABLE (
   geom geometry
 )
 AS
 BEGIN
   INSERT INTO @geoms
   SELECT a.geom
     FROM dbo.ExtractGeom(@p_multilinestring,'MULTILINESTRING') AS a;
   RETURN;
 END
 GO
 -- MultiPoint
 CREATE FUNCTION dbo.ExtractMPointGeom( @p_multipoint geometry )
 RETURNS @geoms TABLE (
   geom geometry
 )
 AS
 BEGIN
   INSERT INTO @geoms
   SELECT a.geom
     FROM dbo.ExtractGeom(@p_multipoint,'MULTIPOINT') AS a;
   RETURN;
 END
 GO

Now, let’s test each of these functions.

 -- Test Wrappers
 -- 
 WITH testCollection AS
 (
    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION (
 POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)),
 POINT (2 3),
 MULTIPOINT ((1 1), (2 2), (3 3)),
  LINESTRING (2 3, 3 4),
  MULTILINESTRING ((2 3, 3 4), (1 1, 2 2)),
  POINT (4 5),
  MULTIPOINT ((1 1), (2 2)),
  POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)),
  MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0) AS geom
  )
  SELECT 'ExtractPointGeom' AS func, e.geom.STAsText() AS geomWKT
    FROM testCollection a CROSS apply dbo.ExtractPointGeom(a.geom) AS e
  UNION ALL
  SELECT 'ExtractLineGeom' AS func, e.geom.STAsText() AS geomWKT
    FROM testCollection a CROSS apply dbo.ExtractLineGeom(a.geom) AS e
  UNION ALL
  SELECT 'ExtractPolyGeom' AS func, e.geom.STAsText() AS geomWKT
    FROM testCollection a CROSS apply dbo.ExtractPolyGeom(a.geom) AS e
  UNION ALL
  SELECT 'ExtractMPointGeom' AS func, e.geom.STAsText() AS geomWKT
    FROM testCollection a CROSS apply dbo.ExtractMPointGeom(a.geom) AS e
  UNION ALL
  SELECT 'ExtractMLineGeom' AS func, e.geom.STAsText() AS geomWKT
    FROM testCollection a CROSS apply dbo.ExtractMLineGeom(a.geom) AS e
  UNION ALL
  SELECT 'ExtractMPolyGeom' AS func, e.geom.STAsText() AS geomWKT
    FROM testCollection a CROSS apply dbo.ExtractMPolyGeom(a.geom) AS e;

Singles from Multis

Here is how we can use one of these wrapper functions to extract a single geometry type from its multi-geometry.

 -- One single from multi
 --
 SELECT e.geom.STAsText() AS linestrings
   FROM dbo.ExtractLineGeom(geometry::STGeomFromText('MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2))',0)) AS e;

Geography

This function is for the geometry object type but we can use it for geography objects via use of the toGeometry/toGeography functions.

 SELECT e.geom.STAsText() AS geogWKT
   FROM dbo.ExtractGeom(
          dbo.toGeometry(geography::STGeomFromText('MULTILINESTRING((148.0 -44.0, 148.0 -43.0, 147.0 -43.0),(147.0 -44.0, 148.0 -44.0), (147.4 -43.6, 147.2 -43.6, 147.2 -43.2, 147.4 -43.2, 147.4 -43.6))',4326),
                         0),
          'LINESTRING') AS e;

I hope these functions are useful to someone.