SQLGeography Simplification - SQL Server CLR implementation of Visvalingam. Supports simplifying native Geography / SqlGeography types via Microsoft.SqlServer.Types, using Visvalingam's algorithm, and choosing to either retain a minimum percentage of points, or limit by the minimum area acceptable to remove. Should in theory build on Windows, Linux, and MacOs.
$ dotnet add package SqlSpatial.SimplifyDROP ASSEMBLY IF EXISTS SqlSpatialClr;
CREATE ASSEMBLY SqlSpatialClr FROM '/where/uploaded/to/mssql/server/SqlSpatial.Simplify.dll' WITH PERMISSION_SET = SAFE;
CREATE FUNCTION dbo.SimplifyByArea(@polygon GEOGRAPHY, @tolerance FLOAT) RETURNS GEOGRAPHY AS EXTERNAL NAME SqlSpatialClr.[SqlSpatial.Simplify.SqlClrWrapper].SimplifyByMinimumArea; GO
CREATE FUNCTION dbo.SimplifyByPercentage(@polygon GEOGRAPHY, @perentage FLOAT) RETURNS GEOGRAPHY AS EXTERNAL NAME SqlSpatialClr.[SqlSpatial.Simplify.SqlClrWrapper].SimplifyByPercentagePointsRetained; GO
GO
You may need to DROP the functions first if the code's C# signatures have changed.
ALTER ASSEMBLY SqlSpatialClr FROM '/where/uploaded/to/mssql/server/SqlSpatial.Simplify.dll';
DROP FUNCTION IF EXISTS dbo.SimplifyByArea; GO
DROP FUNCTION IF EXISTS dbo.SimplifyByPercentage; GO
DROP ASSEMBLY IF EXISTS SqlSpatialClr;
-- simplify by retaining 5% of the largest-area points
DECLARE @percentage FLOAT = 5.0;
DECLARE @geography GEOGRAPHY = (SELECT SomeHugeBoundary FROM lovely_big_city_table WHERE id = 31415);
SELECT dbo.SimplifyByPercentage(@geography, @percentage) AS simplified;
-- simplify by dropping points with a triangular area under 200m
DECLARE @tolerance FLOAT = 200.0;
DECLARE @geography GEOGRAPHY = (SELECT SomeHugeBoundary FROM lovely_big_city_table WHERE id = 31415);
SELECT dbo.SimplifyByArea(@geography, @tolerance) AS simplified;