Overlaying Polygons in SQL Server Spatial

Introduction

The OGC operators for geometry processing only support two polygon inputs.

geom.STIntersection ( other_geometry )

Additionally the Microsoft aggregate operators only include geometry union processing geometry::UnionAggregate and not overlay processing etc. UnionAggregate returns for all input polygons as the single Union (see above) does.

Concept: Resolving all overlapping areas, without dissolving boundaries, is called “planar enforcing”. Planar enforcement is a set of rules that specify that polygons cannot overlap. This means that boundary lines cannot overlap without an intersection being formed.

Slivers are allowed but normally are not wanted.

Concept: A sliver is an area of overlap, usually longer than it is wide, formed between two polygon boundaries that aren’t, but should be, shared. To resolve overlapping areas a method for sliver processing needs to be implemented.

The result we are after can be seen in this image.

Example Overlapping Polygons

Here are a collection of overlapping polygons before processing by a new function called STOverlay.
This may help our understanding.

select geometry::STGeomFromText(
'GEOMETRYCOLLECTION(
POLYGON((1 1,9 1,9 9,1 9,1 1)),
POLYGON((3 3,8 3,8 8,3 8,3 3)),
POLYGON((1 1,3 1,3 3,1 3,1 1)),
POLYGON((2 2,4 2,4 4,2 4,2 2)),
POLYGON((9 9,10 9,10 10,9 10,9 9)),
POLYGON((2 6,3 6,3 8,2 8,2 6)),
POLYGON((5 6,6 6,6 7,5 7,5 6)),
POLYGON((3.5 1.5,4.5 1.5,4.5 3.5,3.5 3.5,3.5 1.5)))',0) as geom

I think you can see that the polygons overlap.

Overlay Processing

This article is about creating a TSQL function that overlays, rather than unions, a collection of polygon.

The overlaying of polygons is not trivial.

NOTE: As at November 2020, the PostGIS develoment team was asked that consideration be given to creating an ST_Overlay function from its powerful line noding and polygonization functions. It will be a powerful addition to the PostGIS API function set.

The appearance, or not, of an ST_Overlay PostGIS function does not help SQL Server Spatial users!

Until now.

Issues

I will repeat myself: the construction and execution of an STOverlay function is non-trivial.

One of the non-trivial aspects is determining when boundaries are shared or not. Boundaries that are very, very close to others has to be identified and resolved. And this processing has to be done with polygons, not its constituent lines.

SQL Server Spatial does not have a precision model, so a method is needed to determine when two polygons are the same: if the lines/coordinates differ, even by the smallest amount, they are not the same. When overlaying these two polygons, slivers will occur.

In the STOverlay function all the processing is done only for polygons. STOverlay uses standard SQL Sever Spatial operations (STIntersection, STSymDifference, and STDifference) and, because one needs to be able to identify polygons that, within tolerance, the same, polygon similarity equality processing (migrated from Java Topology Suite’s code to TSQL) is implemented.

Here is what the similarity processing can do:

with data as (
  select [dbo].[STMakeEnvelope](0,0,1,1,12) as polygona,
         [dbo].[STMakeEnvelope](0.000001,0,0.99994,1,12) as polygonb
)
select polygona.STEquals(polygonb) as ms_equls,
       [dbo].[STDetermine](polygona,polygonb,0.99991) as sdba_equals
  from data as a;

ms_equals sdba_equals
0         EQUALS

Processing

The overlay processing of the above polygon collection is as follows:

with data as (
select geometry::STGeomFromText(
'GEOMETRYCOLLECTION(
POLYGON((1 1,9 1,9 9,1 9,1 1)),
POLYGON((3 3,8 3,8 8,3 8,3 3)),
POLYGON((1 1,3 1,3 3,1 3,1 1)),
POLYGON((2 2,4 2,4 4,2 4,2 2)),
POLYGON((9 9,10 9,10 10,9 10,9 9)),
POLYGON((2 6,3 6,3 8,2 8,2 6)),
POLYGON((5 6,6 6,6 7,5 7,5 6)),
POLYGON((3.5 1.5,4.5 1.5,4.5 3.5,3.5 3.5,3.5 1.5)))',0) as geom
)
select p.id, polygon.STAsText() as polygon
  from data as a cross apply [dbo].[STOverlay](a.geom,0.99999,3,5) as p;

Which looks like….

If you look hard, you can see that none of the polygons share any overlapping area.

A good result.

The processing took about 20 seconds on my desktop Windows 10 machine (Core i7, 16G Memory, SSD) which is not bad considering the processing the function does.

Function

The STOverlay function looks like this:

CREATE FUNCTION [dbo].[STOverlay] (
  @p_polygon_collection geometry,
  @p_similarity         float = 1.0,
  @p_round_xy           integer = 3,
  @p_loops              integer = 5
)
 Returns @polygons TABLE(
   id      integer,
   polygon geometry
)
AS
Begin
  ...
END
GO

Its full description is available in the documentation for SQL Server Spatial (General) Functions.

Testing And Conclusion

As you can see STOverlay works for the example above.

But a function is only as good as its testing and, so, what it processes.

The examples I have run through the function are all small. The speed is reasonable for these examples, with the results being correct.

Spatial data is Complex. So, if you want to run very large, dense polygons (many vertices in rings) through the function it simply may not complete in the time you hoped for. With that caveat emptor, let me know how you go with the function!