Sql Server UnionAggregate to merge geofences

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.

https://msdn.microsoft.com/en-us/library/ff929310.aspx

based on this information it was as easy as doing the following:

DECLARE @Geom TABLE

(

shape geometry,

shapeType nvarchar(50)

);

— 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)
select  [Geofence].ToString(),’POLYGON’
  FROM [LoJack_CMS].[Lojack].[SPATIAL_GEOFENCE]
  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.

This entry was posted in Sql Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s