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.


Make sure CLR Integration is enabled.
sp_configure 'show advanced options', 1; 
sp_configure 'clr enabled', 1; 

Supported Versions

The installer supports SQL Server 2008R2 through to SQL Server 2016.


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\


Execute the following two TSQL commands in the database
drop function dbo.SqlSimplifyGeometry
drop assembly [hej.SqlServer.SimplifySpatial]


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

Simplified polygon


Show Only Points inside Specific Polygon (Inner Join)

,       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)

,       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     =
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.


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


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 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.
The Software is provided "AS IS" and without warranties of any kind. You use it entirely at your own risk.