Go to content Go to navigation and search

Home

Current SQL Server Blogs

Search

RSS / Atom

Email me

textpattern

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

· Sep 28, 08:38 pm by Simon Greener

I have pointed out in another :blog”:http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/87/how-to-extract-elements-from-the-result-of-an-sdo_intersection-of-two-polygons/ that the result of an STIntersection() between two polygon objects could result in an object that is not a polygon or multipolygon.

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

USE [GISDB]  -- change this to your database
GO

CREATE FUNCTION dbo.ExtractGeom( @p_geom         geometry, 
                                 @p_geometryType varchar(1000) )
RETURNS @geoms Table (
  geom geometry
)
AS
Begin
  Declare
    @v_requiredType varchar(1000) = UPPER(@p_geometryType),
    @v_GeometryType varchar(1000),
    @geomn          int;
  Begin
    If ( @p_geom is NULL ) 
      return;

    If ( ( @v_requiredType is NULL ) 
           or
         ( @v_requiredType not in ('POINT','MULTIPOINT','LINESTRING','MULTILINESTRING','POLYGON','MULTIPOLYGON' ) )
       )
    Begin
      -- PRINT '==@==v_requiredType (' + @v_requiredType + ') is not one of POINT,MULTIPOINT,LINESTRING,MULTILINESTRING,POLYGON,MULTIPOLYGON.';
      return;
    End;

    -- If both geometry types are same, we can just return the geometry
    --

    SET @v_GeometryType = UPPER(@p_geom.STGeometryType());
    If ( @v_requiredType = @v_GeometryType )
    Begin
       INSERT INTO @Geoms ( [geom] ) VALUES ( @p_geom );
       RETURN;
    End;

    -- Are we wanting a single geometry type from a multi geometry?
    --
    If ( ( CHARINDEX('MULTI',==@==v_geometryType) <> 0 )
         AND
         ( CHARINDEX(@v_requiredType,==@==v_geometryType) <> 0 )
       )
    Begin
      SET @geomn  = 1;
      WHILE ( @geomn <= @p_geom.STNumGeometries() )
      BEGIN
        INSERT INTO @geoms ( [geom] ) VALUES ( @p_geom.STGeometryN(@geomn) );
        SET @geomn = @geomn + 1;
      END;
      RETURN;
    End;

    If ( @v_GeometryType <> UPPER('GeometryCollection') )
    Begin
        -- PRINT '==@==p_geometry'' type (' + @v_geometryType + ') is not a GeometryCollection.';
        return;
    End;

    SET @geomn  = 1;
    WHILE ( @geomn <= @p_geom.STNumGeometries() )
    BEGIN
      SET @v_geometryType = UPPER(@p_geom.STGeometryN(@geomn).STGeometryType());
      If ( @v_GeometryType = UPPER(@v_requiredType) )
      BEGIN
         INSERT INTO @Geoms ( [geom] ) VALUES ( @p_geom.STGeometryN(@geomn) );
      END;
      SET @geomn = @geomn + 1;
    END;

    RETURN;
  End;
End
Go

Testing

Testing it with the above intersection geometry gives us.

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

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



As expected.

Now some more tests.

with testCollection As
(
   select geometry::STGeomFromText('GEOMETRYCOLLECTION (
POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)), 
POINT (2 3), 
MULTIPOINT ((1 1), (2 2), (3 3)), 
LINESTRING (2 3, 3 4), 
MULTILINESTRING ((2 3, 3 4), (1 1, 2 2)), 
POINT (4 5), 
MULTIPOINT ((1 1), (2 2)), 
POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)), 
MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0) as geom
)
select 'POINT' as extractType, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractGeom(a.geom,'POINT') as e
union all
select 'LINESTRING' as extractType, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractGeom(a.geom,'LINESTRING') as e
union all
select 'POLYGON' as extractType, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractGeom(a.geom,'POLYGON') as e
union all
select 'MULTIPOINT' as extractType, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractGeom(a.geom,'MULTIPOINT') as e
union all
select 'MULTILINESTRING' as extractType, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractGeom(a.geom,'MULTILINESTRING') as e
union all
select 'MULTIPOLYGON' as extractType, e.geom.STAsText() as geomWKT
  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.

-- Now extra singles from multis...
--
select 'POINT' as extractType,e.geom.STAsText() as geomWKT
  from dbo.ExtractGeom(geometry::STGeomFromText('MULTIPOINT((1 1),(2 2),(3 3))',0),'POINT') as e
union all
select 'LINESTRING' as extractType,e.geom.STAsText() as geomWKT
  from dbo.ExtractGeom(geometry::STGeomFromText('MULTILINESTRING((2 3 4,3 4 5),(1 1,2 2))',0),'LINESTRING') as e
  union all
select 'POLYGON' as extractType,e.geom.STAsText() as geomWKT
  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.

Create Function dbo.ExtractPolyGeom( @p_polygon geometry )
Returns @geoms Table (
  geom geometry
)
AS
Begin
  insert into @geoms
  select a.geom
    from dbo.ExtractGeom(@p_polygon,'POLYGON') as a;
  Return;
End
Go

Create Function dbo.ExtractLineGeom( @p_linestring geometry )
Returns @geoms Table (
  geom geometry
)
AS
Begin
  insert into @geoms
  select a.geom
    from dbo.ExtractGeom(@p_linestring,'LINESTRING') as a;
  Return;
End
Go

Create Function dbo.ExtractPointGeom( @p_point geometry )
Returns @geoms Table (
  geom geometry
)
AS
Begin
  insert into @geoms
  select a.geom
    from dbo.ExtractGeom(@p_POINT,'POINT') as a;
  Return;
End
Go

Create Function dbo.ExtractMPolyGeom( @p_multipolygon geometry )
Returns @geoms Table (
  geom geometry
)
AS
Begin
  insert into @geoms
  select a.geom
    from dbo.ExtractGeom(@p_multipolygon,'MULTIPOLYGON') as a;
  Return;
End
Go

Create Function dbo.ExtractMLineGeom( @p_multilinestring geometry )
Returns @geoms Table (
  geom geometry
)
AS
Begin
  insert into @geoms
  select a.geom
    from dbo.ExtractGeom(@p_multilinestring,'MULTILINESTRING') as a;
  Return;
End
Go

Create Function dbo.ExtractMPointGeom( @p_multipoint geometry )
Returns @geoms Table (
  geom geometry
)
AS
Begin
  insert into @geoms
  select a.geom
    from dbo.ExtractGeom(@p_multipoint,'MULTIPOINT') as a;
  Return;
End
Go

Now, let’s test each of these functions.

-- Test Wrappers
--
with testCollection As
(
   select geometry::STGeomFromText('GEOMETRYCOLLECTION (
POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0)), 
POINT (2 3), 
MULTIPOINT ((1 1), (2 2), (3 3)), 
LINESTRING (2 3, 3 4), 
MULTILINESTRING ((2 3, 3 4), (1 1, 2 2)), 
POINT (4 5), 
MULTIPOINT ((1 1), (2 2)), 
POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)), 
MULTIPOLYGON (((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0))))',0) as geom
)
select 'ExtractPointGeom' as func, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractPointGeom(a.geom) as e
union all
select 'ExtractLineGeom' as func, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractLineGeom(a.geom) as e
union all
select 'ExtractPolyGeom' as func, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractPolyGeom(a.geom) as e
union all
select 'ExtractMPointGeom' as func, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractMPointGeom(a.geom) as e
union all
select 'ExtractMLineGeom' as func, e.geom.STAsText() as geomWKT
  from testCollection a cross apply dbo.ExtractMLineGeom(a.geom) as e
union all
select 'ExtractMPolyGeom' as func, e.geom.STAsText() as geomWKT
  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.

-- One single from multi
--
select e.geom.STAsText() as linestrings
  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.

select e.geom.STAsText() as geogWKT
  from dbo.ExtractGeom(
         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),
                        0),
         '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.

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Newsvinepost this at Ma.gnoliapost this at Furlpost this at Blinklistpost this at Spurlpost this at Wistspost this at Simpypost this at Redditpost this at Farkpost this at Blogmarkspost this at Yahoo! my webpost this at Mr. Wongpost this at Windows Livepost this at Google Bookmarkspost this to Twitter
  Textile Help