Combine (union?) And Simplify/reduce Dbgeometry Records For Geojson

I have a number of spatial entities in a table, with a geometry field called Boundaries. I want to produce a GeoJson file with the simplified shapes/geometries. This is my first at

Solution 1:

Looks like this is not possible in SQL Server.

You need to convert the geometries to topologies, then simplify, then match back to the original geometries to preserve the properties/attributes/id/etc.

See: https://trac.osgeo.org/postgis/wiki/UsersWikiSimplifyWithTopologyExt

SQL Server doesn't have support for Topologies.

---

EDIT

I'm working on the code below, which converts polygons (not multipolygons) to linestrings, unions the linestrings to effectively get a topology layer, then simplifies that. It works really well, but the difficulty is not in converting the multilinestrings to multipolygons, which might need a tool like this.

select
    geometry::STGeomFromText(replace(replace(e1.boundaries.STAsText(), 'POLYGON (', 'LINESTRING '), '))', ')'), 4326)
    .STUnion(geometry::STGeomFromText(replace(replace(e2.boundaries.STAsText(), 'POLYGON (', 'LINESTRING '), '))', ')'), 4326))
    .STUnion(geometry::STGeomFromText(replace(replace(e3.boundaries.STAsText(), 'POLYGON (', 'LINESTRING '), '))', ')'), 4326))
    .Reduce(0.1)
from entities e1
cross join entities e2 
cross join entities e3
where e1.code  = 'dc7'
and e2.code = 'dc6'
and e3.code = 'dc8'

---

EDIT

Using NetTopologySuite, it can be done. I've written it up here. Using the Polygonizer, you can convert the linestrings back to polygons. Then you have to match the polygons back to the originals by using a ratio of area intersection, and then (if matched) you can re-associate the properties.