Purpose
The dbo.SqlSimplifyGeometry() CLR function greatly increases
the performance of spatial queries that involve complex polygons (e.g. polygons
that represent geographical areas such as states/provinces).
The function dynamically tessellates the supplied complex
polygon into simpler shapes. The collection of simpler, smaller shapes reduce
the amount of work the SQL Engine needs to do by removing more false positives,
and the simpler shapes themselves are much easier for the SQL Server engine to
process when performing spatial joins.
Prerequisites
Make sure CLR Integration is enabled.
Enabling CLR Integration: https://msdn.microsoft.com/en-us/library/ms131048.aspx
sp_configure 'show advanced
options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled',
1;
GO
RECONFIGURE;
GO
Supported Versions
The installer supports SQL Server 2008R2 through to SQL
Server 2016.
Installation
The setup file installs the following objects in the target
database:
The hej.SqlServer.SimplifySpatial assembly is
installed in \Programmability\Assemblies\
The dbo.SqlSimplifyGeometry table valued function is installed in \Programmability\Table-valued Functions\
The dbo.SqlSimplifyGeometry table valued function is installed in \Programmability\Table-valued Functions\
Uninstallation
Execute the following two TSQL commands in the database
drop
function dbo.SqlSimplifyGeometry
drop assembly [hej.SqlServer.SimplifySpatial]
drop assembly [hej.SqlServer.SimplifySpatial]
Usage
dbo.SqlSimplifyGeometry
(@Polygon geometry)
The CLR function is accessed via a table valued function.
The output can be generated either by passing a single geometry data type into
the function or by CROSS APPLYing it against a table holding geometry data. See
the examples section below.
Result Set
Name
|
Type
|
Description
|
Shape
|
geometry
|
Simplified polygon
|
Examples
Show Only Points inside Specific Polygon (Inner Join)
SELECT
pt.ID
, poly.ID
FROM dbo.polygon poly
CROSS APPLY dbo.SqlSimplifyGeometry (poly.shape) s_simple
INNER JOIN dbo.points pt
ON s_simple.Shape.STIntersects(pt.Shape) = 1
, poly.ID
FROM dbo.polygon poly
CROSS APPLY dbo.SqlSimplifyGeometry (poly.shape) s_simple
INNER JOIN dbo.points pt
ON s_simple.Shape.STIntersects(pt.Shape) = 1
Show All Points and any Intersecting Polygons (Points Left Join Polygon)
SELECT
pt2.ID
, poly.ID
FROM dbo.polygon poly
CROSS APPLY dbo.SqlSimplifyGeometry (poly.shape) s_simple
INNER JOIN dbo.points pt
ON s_simple.Shape.STIntersects(pt.Shape) = 1
RIGHT JOIN dbo.points pt2
ON pt.id = pt2.id
, poly.ID
FROM dbo.polygon poly
CROSS APPLY dbo.SqlSimplifyGeometry (poly.shape) s_simple
INNER JOIN dbo.points pt
ON s_simple.Shape.STIntersects(pt.Shape) = 1
RIGHT JOIN dbo.points pt2
ON pt.id = pt2.id
The right join is required because my investigations show
that spatial indexes are only utilised for INNER joins.
Trying to force a spatial index on any other kind of join
will result in an error. This means a double join of the point data is
required. Once as an inner join to the tessellated polygon which will utilise
the spatial index, and then again to itself on some unique key to get all the
points that don’t fall inside the polygons.
Spatial Index Considerations
As mentioned above, the point geometry needs to have a
spatial index defined for best performance. Verify the spatial index is being
used for any slow queries. Adding any other predicate to the query that acts on
a column from the points might result in the spatial index being ignored.
Feedback
I’m very interested in any situations where the .NET CLR
function is wrong. That is, situations where spatial joins between the original
polygon and point spatial data returns a different dataset to that returned by
joining the points to the Shape geometry output by sqlSimplifyGeometry.
I’d ideally need the geometry data as create table + insert
statements and the offending query.
Copyright 2016 HEJ Solutions http://simplifyspatial.hejsolutions.com
License
See the License.rtf that’s included in this zip (and must be
agreed to when installing) for the full version. But the summary is:
You can use the .NET CLR function in any way
in your database as long as it is installed using the setup file available at
http://simplifyspatial.hejsolutions.com and is queried within SQL server.
You cannot reproduce or redistribute the
software.
You can use and distribute the software
internally within your organisation.
You may:
· install the software and use it for any
purpose (personal or commercial) not explicitly prohibited by this software
license agreement
You may not:
· extract the software from SQL Server and reference
the DLL directly or outside of SQL Server. The software must only be used by
calling the supplied CLR function(s)
· repackage the software in any way. The
Software must be installed using the supplied setup file
· reverse engineer, decompile, disassemble, or
reflect the Software
· modify, or create derivative works based upon,
the Software in whole or in part
· distribute copies of the Software
· remove any proprietary notices or labels on
the Software
· resell, lease, rent, transfer, sublicense, or
otherwise transfer rights
to the Software.