Top 5 Recent Articles

ARTICLES CATEGORIES

STConvertToLineString: Extract LineStrings in GeometryCollection to create LineString

I had need today to create a function that extracts all linestring elements from a GeometryCollection and returns a LineString or a MultiLineString.

The function is called STConvertToLineString as is described as follows:

 CREATE FUNCTION [dbo].[STConvertToLineString]
 (
   @p_geometry_collection geometry
 )
 RETURNS geometry
 AS
 BEGIN
  ...
 END

Its documentation can be seen see here.

Here are some examples of how to use it:

 WITH gc AS (
   SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) AS geom
    UNION ALL
   SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0), POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) AS geom
    UNION ALL
   SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0), LINESTRING(1 1,2 2),LINESTRING(3 3,19 19),POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) AS geom
    UNION ALL
   SELECT geometry::STGeomFromText('GEOMETRYCOLLECTION(MULTILINESTRING((0 0,20 0),(20 20,0 20,0 0)), LINESTRING(1 1,2 2),LINESTRING(3 3,19 19),POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) AS geom
 )
 SELECT [dbo].[STConvertToLineString](geom).STAsText() AS geomWKT
   FROM gc AS a
 GO
 -- Result
 geomWKT
 LINESTRING EMPTY
 LINESTRING (0 0, 20 0, 20 20, 0 20, 0 0)
 MULTILINESTRING ((0 0, 20 0, 20 20, 0 20, 0 0), (1 1, 2 2), (3 3, 19 19))
 MULTILINESTRING ((0 0, 20 0), (20 20, 0 20, 0 0), (1 1, 2 2), (3 3, 19 19))

I hope this function is of interest to someone.