Over the past two years I have been learning more and more about the power of SQL Server’s spatial functions. During this time a co-worker, and myself have developed server side geofence processes to track vehicles using GPS data. Some customers had over 12 geofences defined, and keeping the polygons accurate became important and sometimes time consuming.
One day a customer wanted to change three adjacent geofences and rename the geofence to just one merged geofence. At first thought I would just take the exterior cords and plug them in as the new shape. Then I thought, there has to be a better way. Since I was rather new to spatial geometry, I hit the msdn website to learn a better solution.
based on this information it was as easy as doing the following:
DECLARE @Geom TABLE
— The following table has a column of type geometry in the spatial_geofence table. So we are inserting the three geofences into our @Geom table
INSERT INTO @Geom(shape,shapeType)
WHERE COMPANY_ID=2 AND GEONAME IN (‘A’,’B’,’C’)
— Perform UnionAggregate on @Geom.shape column
SELECT geometry::UnionAggregate(shape).ToString() FROM @Geom;
using the output from the above query I could easily just insert the new polygon row and delete the previous three geofences.
After transposing the WKT to KML and checking it with Google Maps I discovered it was exactly the clean solution I was looking for.