# STGeometryTypes: Extracting all geometry type string values from a complex geometry

Problem

Ever need to extract a textual description of all geometry types that appear in a single geometry/geography in SQL Server 2008 including all sub-elements?

Background

Recently I had cause to do so and created the following function that implements this need.

Function

```USE [GISDB]  -- Change this to your database
GO
--
-- Extracts all geometry type keywords from a geometry (and its sub-elements)
CREATE FUNCTION [\$(owner)].[STGeometryTypes](
@p_geometry geometry
)
Returns varchar(MAX)
As
Begin
Declare
@v_return varchar(MAX);
Begin
If ( @p_geometry is NULL )
return NULL;

SELECT @v_return = SUBSTRING(a.token,2,LEN(a.token))
FROM (SELECT (STUFF((SELECT ',' + a.token
FROM (select t.token
from [\$(owner)].[Tokenizer](@p_geometry.AsTextZM(),' ,)(') as t
) as a
WHERE a.token is not null
AND a.token not like '%[0-9]%'
FOR XML PATH(''), TYPE, ROOT
).value('root','nvarchar(max)'),1,1,'''')
) AS token
) as a;

RETURN @v_return;
End;
End
GO
```

Testing

Now let’s test it against a selection of all possible geometry types

```-- Simple geometry
select dbo.[STGeometryTypes](geometry::STGeomFromText('POINT(0 1 2)',0)) as gtypes;
go
gtypes
POINT
-- Single CurvePolygon with one interior ring
select [\$(owner)].[STGeometryTypes](geometry::STGeomFromText('CURVEPOLYGON(CIRCULARSTRING(0 5, 5 0, 0 -5, -5 0, 0 5), (-2 2, 2 2, 2 -2, -2 -2, -2 2))',0)) as gtypes;
GO
gtypes
CURVEPOLYGON,CIRCULARSTRING

-- GeometryCollection
select  [\$(owner)].[STGeometryTypes](
geometry::STGeomFromText(
'GEOMETRYCOLLECTION(
LINESTRING(0 0,20 0,20 20,0 20,0 0),
CURVEPOLYGON(
COMPOUNDCURVE(
(0 -23.43778, 0 23.43778),
CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),
(-90 23.43778, -90 -23.43778),
CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))),
COMPOUNDCURVE(
CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778),
(0 -23.43778, 0 23.43778)))',0)) as gTypes;
GO
gTypes
GEOMETRYCOLLECTION,LINESTRING,CURVEPOLYGON,COMPOUNDCURVE,CIRCULARSTRING,CIRCULARSTRING,COMPOUNDCURVE,CIRCULARSTRING
```

Farewell

I hope this function is useful to someone.