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:


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:


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 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]

  DECLARE @lineshape geometry
    DECLARE @routeid int
    --SELECT @lineshape = r.shape from marathonia_diadromi_spatial r
    --, events e
    --where 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.
   UPDATE [mpoint].[dbo].[EVENTS]
   SET [SHAPE] = [mpoint].[dbo].[LocateAlongGeom](@lineshape, start_mp).ToString()


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…