STExtractPolygon: Extract Polygons from result of STIntersection in SQL Server Spatial
Oracle customers have asked me for help in the past dealing with compound objects or collections that result from thinning or geoprocessing (intersection etc) of geometry data.
Normally this is the need to extract only polygons from the result of an intersection operation.
This problem applies to SQL Server Spatial users are the following example will show.
The solution to the problem is to process the result of an operation and extract out only those geometric elements that are required.
In a previous blog I created a function called STExtract that allows a user to extract the parts of elements of a multi or single geometry depending on what is required.
This function is the basis for a new function called STExtractPolygon that can be used to extract only the polygon elements of a GeometryCollection that could result from an STIntersection between two polygon geometries.
USE [GISDB] -- Change this to your database
GO
-- Drop if exists
--
DROP FUNCTION dbo.STExtractPolygon;
-- Now create
--
CREATE FUNCTION dbo.STExtractPolygon(@p_geometry geometry)
RETURNS geometry
AS
BEGIN
DECLARE
@v_geom geometry;
BEGIN
IF ( @p_geometry IS NULL )
RETURN NULL;
IF ( @p_geometry.STGeometryType() NOT IN ('CurvePolygon','Polygon','MultiPolygon','GeometryCollection') )
RETURN NULL;
IF ( @p_geometry.STGeometryType() IN ('CurvePolygon','Polygon','MultiPolygon') )
RETURN @p_geometry;
IF ( @p_geometry.STGeometryType() IN ('GeometryCollection') )
BEGIN
-- Need to extract and append any polygons that may be in the GeometryCollection
--
SELECT @v_geom = geometry::CollectionAggregate(e.[geom])
FROM dbo.STExtract(@p_geometry,0) AS e
WHERE e.[geom].STGeometryType() IN ('CurvePolygon','Polygon');
IF ( @v_geom IS NOT NULL AND @v_geom.STNumGeometries() > 0 )
BEGIN
IF ( @v_geom.STNumGeometries() = 1 )
BEGIN
-- Get rid of GeometryCollection WKT token wrapper
--
SET @v_geom = geometry::STGeomFromText(REPLACE(REPLACE(@v_geom.STAsText(),'GEOMETRYCOLLECTION (','')+'$',')$',''),@v_geom.STSrid);
END
ELSE
BEGIN
IF ( CHARINDEX('CURVEPOLYGON',@v_geom.STAsText()) = 0 )
BEGIN
-- Replace all internal POLYGON WKT tokens with nothing
-- Then replace starting GeometryCollection token with MultiPolygon
--
SET @v_geom = geometry::STGeomFromText(REPLACE(REPLACE(UPPER(@v_geom.STAsText()),'POLYGON',''),'GEOMETRYCOLLECTION','MULTIPOLYGON'),@v_geom.STSrid);
END;
END;
END;
END;
RETURN @v_geom;
END;
END
GO
Now some testing…
-- All these return null as they should
--
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('POINT(0 0)',0)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('MULTIPOINT((0 0),(20 0))',0)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)',0)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('MULTILINESTRING((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)).STAsText() AS ePoly;
As they do….
| ePoly |
|---|
| NULL |
| NULL |
| NULL |
| NULL |
-- These should return themselves
--
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('MULTIPOLYGON (((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)), ((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('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)))',0)).STAsText() AS ePoly;
As they do….
| ePoly |
|---|
| POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0), (10 10, 10 11, 11 11, 11 10, 10 10), (5 5, 5 7, 7 7, 7 5, 5 5)) |
| MULTIPOLYGON (((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110))) |
| MULTIPOLYGON (((0 0, 20 0, 20 20, 0 20, 0 0), (10 10, 10 11, 11 11, 11 10, 10 10), (5 5, 5 7, 7 7, 7 5, 5 5)), ((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110))) |
| 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))) |
-- These should extract only the polygons within the GeometryCollection
-- (The second is wrapped as a GeometryCollection as a MultiPolygon cannot be constructed that includes a CurvePolygon
--
SELECT dbo.STExtractPolygon(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)).STAsText() AS ePoly UNION ALL
SELECT dbo.STExtractPolygon(geometry::STGeomFromText('GEOMETRYCOLLECTION(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))), POLYGON ((100 200, 180.00 300.00, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0))',0)).STAsText() AS ePoly;
As they do…
| ePoly |
|---|
| 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))) |
| GEOMETRYCOLLECTION (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))), POLYGON ((100 200, 180 300, 100 300, 100 200))) |
Now, test the intersection between two polygons.
-- First, show the polygons
--
SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) AS geom
UNION ALL
SELECT geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) AS geom;
Which look like this:
-- Do the intersection and show the result as WKT
--
SELECT a.geom.STIntersection(b.geom).STAsText() AS intersectionWKT
FROM (SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) AS geom) a,
(SELECT geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) AS geom) b;
Note the non-polygon elements.
| intersectionWKT |
|---|
| GEOMETRYCOLLECTION (POLYGON ((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)), POLYGON ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0)) |
Using STExtract we can “explode” the GeometryCollection into its components for easier viewing.
SELECT d.gid, d.sid, d.geom.STAsText() AS geom
FROM (SELECT a.geom.STIntersection(b.geom) AS geom
FROM (SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) AS geom) a,
(SELECT geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) AS geom) b
) c
CROSS apply
dbo.STExtract(c.geom,1) AS d;
Which looks like this:
| gid | sid | geom |
|---|---|---|
| 1 | 1 | POLYGON ((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)) |
| 2 | 1 | POLYGON ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200)) |
| 3 | 0 | LINESTRING (100 200, 100 75) |
| 4 | 0 | POINT (100 0) |
Visually…..
Now, using our new STExtractPolygon function we can extract just the polygon intersection elements.
SELECT dbo.STExtractPolygon(a.geom.STIntersection(b.geom)).STAsText() AS geom
FROM (SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) AS geom) a,
(SELECT geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) AS geom) b;
Which, looks like:
| geom |
|---|
| MULTIPOLYGON (((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)), ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200))) |
Showing the result visually:
SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) AS geom
UNION ALL
SELECT geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) AS geom
UNION ALL
SELECT dbo.STExtractPolygon(a.geom.STIntersection(b.geom)) AS geom
FROM (SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) AS geom) a,
(SELECT geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) AS geom) b;
results in….
I hope this is of use to someone.
The function can be downloaded with the SQL Server Spatial functions at www.spdba.com.au