Create point geometries dynamically using dynamic segmentation in SQLServer 2008

UPDATED: Change trigger to allow for multiple linear geometries

I have started experimenting with the new spatial functionality in SQL Server 2008. Having used Oracle Spatial for a quite a few years this is my first attempt with SQL Server so my apologies in advance if there is an easier way of doing this!🙂

So the scenario is as follows:- Consider you have a layer containing A SINGLE multiple linear geometries with the following structure called ROUTES:

image

The structure can be different- for now all we care about is the [Shape] and [ID] fields.

We also have another table called EVENTS- this is the table we will be updating using the trigger. The table structure is:

image

Where ROUTE_ID is the ID the Route the event is  located along, (as a FK to ID field in the ROUTES table) and  START_MP is the distance from the start of the Route.

The task at hand was to create a trigger so any changes in the START_MP value would update the shape column in the EVENTS table, creating a Point geometry at that location.

That was easy enough using the SQL Server Spatial tools available from http://sqlspatialtools.codeplex.com. After registering the SQLSpatialTools.dll in my database I was able to use the LocateAlongGeom function in a trigger which would deliver the point geometries. The trigger source is:

ALTER TRIGGER [dbo].[upd_shape_trg]
   ON  [dbo].[EVENTS]
   AFTER UPDATE
AS

BEGIN 
  DECLARE @lineshape geometry
    DECLARE @routeid int
    --SELECT @lineshape = r.shape from marathonia_diadromi_spatial r
    --, events e
    --where r.id= e.route_id
    SELECT @routeid=route_id from deleted
    SELECT @lineshape = shape from marathonia_diadromi_spatial where id=@routeid
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   UPDATE [mpoint].[dbo].[EVENTS]
   SET [SHAPE] = [mpoint].[dbo].[LocateAlongGeom](@lineshape, start_mp).ToString()

END

Note that right now this would only work if the ROUTES table contains a single linear geometry. As said- first attempt only this! More later…

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