We'd like to do an effective "real-time dissolve" on an Esri feature class with a SQL GEOMETRY shape where a parcel layer is "dissolved" into products using an attribute (ProductID) of the parcel layer. The SQL Server UnionAggregate() function allows for this, using a query like this:
CREATE VIEW Product
,GEOMETRY::UnionAggregate(PL.SHAPE) as Shape
FROM ParcelProductInfo PPI
JOIN ParcelLayer PL ON PPI.ParcelID = PL.ParcelID
GROUP BY PPI.ProductID
This creates the desired result, but it has a side-effect where dangling artifacts are rendered in the Product layer where nodes in the parcel polygons may not have been exactly coincident, as shown here:
where the blue polygons are parcels and the orange polygons are the "dissolved" products.
Esri has a dissolve tool (arcpy.Dissolve_management())that seems to have an implied tolerance between polygon nodes since we don't see the same artifacts with that tool, but it's not a viable "real-time" solution. However we're hoping to find something that performs a similar function in conjunction with GEOMETRY::UnionAggregate().
Any thoughts or ideas out there?