Go to content Go to navigation and search

Home

Current SQL Server Blog Articles

    SQL Server Spatial: STFlipVectors
    SQL Server Spatial: Extract LineStrings in GeometryCollection to create LineString
    SQL Server Spatial: Converting a LineString to COGO XML
    SQL Server Spatial: Creating (Multi)LineStrings geometries from COGO XML instructions
    New Version of Package of TSQL Spatial Functions
    Vectorize/Segmentize SQL Server 2012
    Coordinate Editing Functions for SQL Server Spatial
    Function to Scale a geometry object for SQL Server Spatial
    TSQL String Tokenizer Function for SQL Server
    geography/geometry to MBR helper functions
    generate_series for SQL Server 2008
    Extract Polygons from result of STIntersection in SQL Server Spatial
    Function to round ordinates of a SQL Server Spatial geometry object
    Extract elements of SQL Server Spatial geometry object
    Counting number of polygon rings SQL Server Spatial
    Filtering Polygon Rings in SQL Server Spatial
    Function to Move a geometry object in SQL Server Spatial
    Alternate Centroid Functions for SQL Server Spatial
    Function to Rotate geometry objects in SQL Server Spatial
    A GetVertices wrapper for DumpPoints in SQL Server 2008 Spatial
    Creating a Morton number Space Key generator for SQL Server
    Gridding a geometry or geography object (SQL Server Denali)
    On hinting spatial indexes
    Random Search Procedure (SQL Server 2008 Spatial)
    COGO: Converting (Google Earth) Formatted Longitude/Latitude points to decimal degrees (SQL Server)
    COGO: Convert Degrees, Minutes and Seconds values to Decimal Degrees
    COGO: DD2DMS Formatting a latitude/longitude decimal degree value
    COGO: Create point from bearing and distance
    COGO: Compute number of vertices required to stroke circle or circular arc
    COGO: Calculating the bearing between two points (SQL Server 2008 Spatial)
    COGO: Compute arc length subtended by angle centre of circle
    COGO: Compute chord length of segment of a circle (arc)
    COGO: Computing Arc To Chord (Arc2Chord) Separation
    COGO: Creating a stroked polygon from a circle's centre x,y and radius
    COGO: Finding centre and radius of a curve defined by three points: FindCircle function
    Identifying Tight Radius Curves sections within LineString geometry data
    Generating random point data for SQL Server 2008 Spatial
    New Presentation on Active (Searchable) Spatial Metadata for SQL Server 2008 Spatial and FreeText
    Checking is a column in a table or a view is of type geometry or geography
    Extracting geometry type string values from geometry/geography objects in SQL Server 2008 Spatial
    Vectorising geometry objects in SQL Server 2008
    MBR to geography/geometry helper functions
    Extracting geometry types from geometry/geography objects in SQL Server 2008
    Getting the number of coordinate dimensions of a geometry/geography object in SQL Server 2008
    A Dump Points Function for SQL Server 2008 Spatial
    toGeography and toGeography Conversion Functions for SQL Server 2008 Spatial
    Write text file with spatial data from SQL Server 2008
    Loading Shapefiles into Geography type column in SQL Server 2008
    Tip #6: Correcting invalid geometries
    Tip #5: Where or where has my little column gone
    Tip #4: What Coordinate System is that?
    Tip #3: What object is that?
    Tip #2: Spatial Indexing and Primary Keys
    Tip #1: SQL Server 2008 "Katmai" - Setting SRIDs

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 · #