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.