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 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] BEGIN END
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;
SET [SHAPE] = [mpoint].[dbo].[LocateAlongGeom](@lineshape, start_mp).ToString()
ALTER TRIGGER [dbo].[upd_shape_trg]
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…