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.

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

Monday September 28 2009 at 09:38

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.

  1. SELECT
  2. geometry::STGeomFromText('POLYGON((100 0,400 0,400 300,100 300,100 0))',0).STIntersection(
  3. 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;

Intersection of Two Polygons can be of mixed type

Intersection
GEOMETRYCOLLECTION (POLYGON ((100 200, 180 300, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0))

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.

  1. USE [GISDB]  -- change this to your database
  2. GO
  3. -- Create function
  4. CREATE FUNCTION dbo.ExtractGeom( @p_geom         geometry,
  5.                                  @p_geometryType VARCHAR(1000) )
  6. RETURNS @geoms TABLE (
  7.   geom geometry
  8. )
  9. AS
  10. BEGIN
  11.   DECLARE
  12.     @v_requiredType VARCHAR(1000) = UPPER(@p_geometryType),
  13.     @v_GeometryType VARCHAR(1000),
  14.     @geomn          INT;
  15.   BEGIN
  16.     -- Test parameters
  17.     IF ( @p_geom IS NULL )
  18.       RETURN;
  19.     IF ( ( @v_requiredType IS NULL )
  20.            OR
  21.          ( @v_requiredType NOT IN ('POINT','MULTIPOINT','LINESTRING','MULTILINESTRING','POLYGON','MULTIPOLYGON' ) )
  22.        )
  23.     BEGIN
  24.       -- PRINT '@v_requiredType (' + @v_requiredType + ') is not one of POINT,MULTIPOINT,LINESTRING,MULTILINESTRING,POLYGON,MULTIPOLYGON.';
  25.       RETURN;
  26.     END;
  27.     -- If both geometry types are same, we can just return the geometry
  28.     --
  29.     SET @v_GeometryType = UPPER(@p_geom.STGeometryType());
  30.     IF ( @v_requiredType = @v_GeometryType )
  31.     BEGIN
  32.        INSERT INTO @Geoms ( [geom] ) VALUES ( @p_geom );
  33.        RETURN;
  34.     END;
  35.     -- Are we wanting a single geometry type from a multi geometry?
  36.     --
  37.     IF ( ( CHARINDEX('MULTI',@v_geometryType) <> 0 )
  38.          AND
  39.          ( CHARINDEX(@v_requiredType,@v_geometryType) <> 0 )
  40.        )
  41.     BEGIN
  42.       SET @geomn  = 1;
  43.       WHILE ( @geomn <= @p_geom.STNumGeometries() )
  44.       BEGIN
  45.         INSERT INTO @geoms ( [geom] ) VALUES ( @p_geom.STGeometryN(@geomn) );
  46.         SET @geomn = @geomn + 1;
  47.       END;
  48.       RETURN;
  49.     END;
  50.     -- We only process collections
  51.     --
  52.     IF ( @v_GeometryType <> UPPER('GeometryCollection') )
  53.     BEGIN
  54.         -- PRINT '@p_geometry'' type (' + @v_geometryType + ') is not a GeometryCollection.';
  55.         RETURN;
  56.     END;
  57.     -- Do Processing
  58.     --
  59.     SET @geomn  = 1;
  60.     WHILE ( @geomn <= @p_geom.STNumGeometries() )
  61.     BEGIN
  62.       SET @v_geometryType = UPPER(@p_geom.STGeometryN(@geomn).STGeometryType());
  63.       IF ( @v_GeometryType = UPPER(@v_requiredType) )
  64.       BEGIN
  65.          INSERT INTO @Geoms ( [geom] ) VALUES ( @p_geom.STGeometryN(@geomn) );
  66.       END;
  67.       SET @geomn = @geomn + 1;
  68.     END;
  69.     RETURN;
  70.   END;
  71. END
  72. GO

Testing

Testing it with the above intersection geometry gives us.

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

IntersectionGeom
POLYGON (( 100 200, 180 300, 100 300, 100 200 ))

As expected.

Now some more tests.

  1. WITH testCollection AS
  2. (
  3.    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION (
  4. POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)),
  5. POINT (2 3),
  6. MULTIPOINT ((1 1), (2 2), (3 3)),
  7. LINESTRING (2 3, 3 4),
  8. MULTILINESTRING ((2 3, 3 4), (1 1, 2 2)),
  9. POINT (4 5),
  10. MULTIPOINT ((1 1), (2 2)),
  11. POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)),
  12. MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0) AS geom
  13. )
  14. SELECT 'POINT' AS extractType, e.geom.STAsText() AS geomWKT
  15.   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'POINT') AS e
  16. UNION ALL
  17. SELECT 'LINESTRING' AS extractType, e.geom.STAsText() AS geomWKT
  18.   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'LINESTRING') AS e
  19. UNION ALL
  20. SELECT 'POLYGON' AS extractType, e.geom.STAsText() AS geomWKT
  21.   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'POLYGON') AS e
  22. UNION ALL
  23. SELECT 'MULTIPOINT' AS extractType, e.geom.STAsText() AS geomWKT
  24.   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'MULTIPOINT') AS e
  25. UNION ALL
  26. SELECT 'MULTILINESTRING' AS extractType, e.geom.STAsText() AS geomWKT
  27.   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'MULTILINESTRING') AS e
  28. UNION ALL
  29. SELECT 'MULTIPOLYGON' AS extractType, e.geom.STAsText() AS geomWKT
  30.   FROM testCollection a CROSS apply dbo.ExtractGeom(a.geom,'MULTIPOLYGON') AS e;

extractType geomWKT
POINT POINT (2 3)
POINT POINT (4 5)
LINESTRING LINESTRING (2 3, 3 4)
POLYGON POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0))
POLYGON POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000))
MULTIPOINT MULTIPOINT ((1 1), (2 2), (3 3))
MULTIPOINT MULTIPOINT ((1 1), (2 2))
MULTILINESTRING MULTILINESTRING ((2 3, 3 4), (1 1, 2 2))
MULTIPOLYGON MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0)))

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

  1. -- Now extra singles from multis...
  2. --
  3. SELECT 'POINT' AS extractType,e.geom.STAsText() AS geomWKT
  4.   FROM dbo.ExtractGeom(geometry::STGeomFromText('MULTIPOINT((1 1),(2 2),(3 3))',0),'POINT') AS e
  5. UNION ALL
  6. SELECT 'LINESTRING' AS extractType,e.geom.STAsText() AS geomWKT
  7.   FROM dbo.ExtractGeom(geometry::STGeomFromText('MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2))',0),'LINESTRING') AS e
  8.   UNION ALL
  9. SELECT 'POLYGON' AS extractType,e.geom.STAsText() AS geomWKT
  10.   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;

extractType geomWKT
POINT POINT (1 1)
POINT POINT (2 2)
POINT POINT (3 3)
LINESTRING LINESTRING (2 3, 3 4)
LINESTRING LINESTRING (1 1, 2 2)
POLYGON POLYGON ((200 200, 400 200, 400 400, 200 400, 200 200))
POLYGON POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0), (40 40, 40 60, 60 60, 60 40, 40 40))

Wrappers

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

  1. CREATE FUNCTION dbo.ExtractPolyGeom( @p_polygon geometry )
  2. RETURNS @geoms TABLE (
  3.   geom geometry
  4. )
  5. AS
  6. BEGIN
  7.   INSERT INTO @geoms
  8.   SELECT a.geom
  9.     FROM dbo.ExtractGeom(@p_polygon,'POLYGON') AS a;
  10.   RETURN;
  11. END
  12. GO
  13. -- Linestring
  14. CREATE FUNCTION dbo.ExtractLineGeom( @p_linestring geometry )
  15. RETURNS @geoms TABLE (
  16.   geom geometry
  17. )
  18. AS
  19. BEGIN
  20.   INSERT INTO @geoms
  21.   SELECT a.geom
  22.     FROM dbo.ExtractGeom(@p_linestring,'LINESTRING') AS a;
  23.   RETURN;
  24. END
  25. GO
  26. -- Point
  27. CREATE FUNCTION dbo.ExtractPointGeom( @p_point geometry )
  28. RETURNS @geoms TABLE (
  29.   geom geometry
  30. )
  31. AS
  32. BEGIN
  33.   INSERT INTO @geoms
  34.   SELECT a.geom
  35.     FROM dbo.ExtractGeom(@p_POINT,'POINT') AS a;
  36.   RETURN;
  37. END
  38. GO
  39. -- MultiPolyon
  40. CREATE FUNCTION dbo.ExtractMPolyGeom( @p_multipolygon geometry )
  41. RETURNS @geoms TABLE (
  42.   geom geometry
  43. )
  44. AS
  45. BEGIN
  46.   INSERT INTO @geoms
  47.   SELECT a.geom
  48.     FROM dbo.ExtractGeom(@p_multipolygon,'MULTIPOLYGON') AS a;
  49.   RETURN;
  50. END
  51. GO
  52. -- MultiLinestring
  53. CREATE FUNCTION dbo.ExtractMLineGeom( @p_multilinestring geometry )
  54. RETURNS @geoms TABLE (
  55.   geom geometry
  56. )
  57. AS
  58. BEGIN
  59.   INSERT INTO @geoms
  60.   SELECT a.geom
  61.     FROM dbo.ExtractGeom(@p_multilinestring,'MULTILINESTRING') AS a;
  62.   RETURN;
  63. END
  64. GO
  65. -- MultiPoint
  66. CREATE FUNCTION dbo.ExtractMPointGeom( @p_multipoint geometry )
  67. RETURNS @geoms TABLE (
  68.   geom geometry
  69. )
  70. AS
  71. BEGIN
  72.   INSERT INTO @geoms
  73.   SELECT a.geom
  74.     FROM dbo.ExtractGeom(@p_multipoint,'MULTIPOINT') AS a;
  75.   RETURN;
  76. END
  77. GO

Now, let’s test each of these functions.

  1. -- Test Wrappers
  2. --
  3. WITH testCollection AS
  4. (
  5.    SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION (
  6. POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)),
  7. POINT (2 3),
  8. MULTIPOINT ((1 1), (2 2), (3 3)),
  9. LINESTRING (2 3, 3 4),
  10. MULTILINESTRING ((2 3, 3 4), (1 1, 2 2)),
  11. POINT (4 5),
  12. MULTIPOINT ((1 1), (2 2)),
  13. POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)),
  14. MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0) AS geom
  15. )
  16. SELECT 'ExtractPointGeom' AS func, e.geom.STAsText() AS geomWKT
  17.   FROM testCollection a CROSS apply dbo.ExtractPointGeom(a.geom) AS e
  18. UNION ALL
  19. SELECT 'ExtractLineGeom' AS func, e.geom.STAsText() AS geomWKT
  20.   FROM testCollection a CROSS apply dbo.ExtractLineGeom(a.geom) AS e
  21. UNION ALL
  22. SELECT 'ExtractPolyGeom' AS func, e.geom.STAsText() AS geomWKT
  23.   FROM testCollection a CROSS apply dbo.ExtractPolyGeom(a.geom) AS e
  24. UNION ALL
  25. SELECT 'ExtractMPointGeom' AS func, e.geom.STAsText() AS geomWKT
  26.   FROM testCollection a CROSS apply dbo.ExtractMPointGeom(a.geom) AS e
  27. UNION ALL
  28. SELECT 'ExtractMLineGeom' AS func, e.geom.STAsText() AS geomWKT
  29.   FROM testCollection a CROSS apply dbo.ExtractMLineGeom(a.geom) AS e
  30. UNION ALL
  31. SELECT 'ExtractMPolyGeom' AS func, e.geom.STAsText() AS geomWKT
  32.   FROM testCollection a CROSS apply dbo.ExtractMPolyGeom(a.geom) AS e;

func geomWKT
ExtractPointGeom POINT (2 3)
ExtractPointGeom POINT (4 5)
ExtractLineGeom LINESTRING (2 3, 3 4)
ExtractPolyGeom POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0))
ExtractPolyGeom POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000))
ExtractMPointGeom MULTIPOINT ((1 1), (2 2), (3 3))
ExtractMPointGeom MULTIPOINT ((1 1), (2 2))
ExtractMLineGeom MULTILINESTRING ((2 3, 3 4), (1 1, 2 2))
ExtractMPolyGeom MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0)))

Singles from Multis

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

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

linestrings
LINESTRING (2 3, 3 4)
LINESTRING (1 1, 2 2)

Geography

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

  1. SELECT e.geom.STAsText() AS geogWKT
  2.   FROM dbo.ExtractGeom(
  3.          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),
  4.                         0),
  5.          'LINESTRING') AS e;

geogWKT
LINESTRING (148 -44, 148 -43, 147 -43)
LINESTRING (147 -44, 148 -44)
LINESTRING (147.4 -43.6, 147.2 -43.6, 147.2 -43.2, 147.4 -43.2, 147.4 -43.6)

I hope these functions are useful to someone.

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 [2]

Thanks for the post. I was looking for a solution to extract linestrings from multilinestring. It was very useful for me.

— MarcinW · 7 June 2015, 05:46 · #

Very useful in deed! great job

— SteveH · 30 November 2015, 19:49 · #