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…

Oracle Spatial for the thrifty developer- Part 3: The basics of dynamic segmentation…

In this post I will show you a function that will return a linear segment along a line between a start and end length. This function together with the PointAlong() function as discussed in the previous post could be used as the basic blocks for building dynamic segmentation or address geocoding routines. At the end of the day these two processes share the same basic principle: to create a geometry (point or line) for a feature (known as an “event”), based on descriptive information only such as start/end address or start/end linear reference plus the underlying linear geometry the feature is located against.

The code for the LineAlong function is shown below:


FUNCTION lineAlong (pi_geom IN mdsys.sdo_geometry
,pi_from IN NUMBER
,pi_to IN NUMBER
,pi_srid IN NUMBER DEFAULT NULL)
RETURN mdsys.sdo_geometry IS
--
last1 INTEGER;
st_set BOOLEAN := TRUE;
i INTEGER;
i_v INTEGER;
n_srid NUMBER;
l_segment mdsys.sdo_geometry;
x_current NUMBER;
y_current NUMBER;
x_next NUMBER;
y_next NUMBER;
l_geom_type NUMBER;
n_total_length NUMBER;
n_seg_length NUMBER;
n_addr_length1 NUMBER;
n_addr_length2 NUMBER;
n_run_length NUMBER;
n_dist NUMBER;
n_grad NUMBER;
found_start_pnt BOOLEAN;
n_dist_to_point NUMBER;
l_ords1 mdsys.sdo_ordinate_array; -- Holds the xy of the from address point
l_ords2 mdsys.sdo_ordinate_array; -- Holds the xy of the to address point
l_ords_all mdsys.sdo_ordinate_array; -- Holds the xy of the address range
n_ords_count NUMBER;
inval_geom_type EXCEPTION;
--
BEGIN
-- Do some validation
IF pi_from > pi_to THEN
raise_application_error(-20000,'[From] value must be greater than the [To] value');
END IF;
n_total_length := get_line_length(pi_geom); -- Get total length of shape
IF pi_to > n_total_length THEN
raise_application_error(-20000,'[To] value cannot be greater than the total line length');
END IF;
--
-- Initialise vars
n_grad := NULL;
n_dist := NULL;
l_geom_type := pi_geom.sdo_gtype;
n_srid := pi_srid;
found_start_pnt := FALSE;
--
--Check geometry type. Must be a 2d line
IF l_geom_type = 2002 THEN
-- Get number of vertices
last1 := pi_geom.sdo_ordinates.LAST;
--dbms_output.put_line('Total vertices:' || to_char(last1 / 2));
-- Initialise counter
i := 1;
-- Calculate absolute distances of from address point from segment start
n_addr_length1 := pi_from;
n_addr_length2 := pi_to;
--
-- Start looping through vertices
WHILE TRUE LOOP
IF st_set THEN
i_v := 1;
x_current := pi_geom.sdo_ordinates(i);
y_current := pi_geom.sdo_ordinates(i + 1);
x_next := pi_geom.sdo_ordinates(i + 2);
y_next := pi_geom.sdo_ordinates(i + 3);
-- Greate segment geometry
l_segment := get_line_from_xy(x_current
,y_current
,x_next
,y_next
,n_srid);
-- Get segment length
n_seg_length := get_line_length(l_segment);
n_run_length := n_seg_length;
--Find xy for start point address
IF found_start_pnt = FALSE THEN
IF n_run_length >= n_addr_length1 THEN
n_grad := get_grad_from_xy(x_current
,y_current
,x_next
,y_next);
n_dist := get_length_from_xy(x_current
,y_current
,x_next
,y_next);
n_dist_to_point := n_addr_length1;
l_ords1 := get_point_at_distance_as_ords(x_current
,y_current
,n_dist_to_point
,n_grad
,n_srid);
-- Insert into ordinate array
l_ords_all := mdsys.sdo_ordinate_array(l_ords1(1),
l_ords1(2));
found_start_pnt := TRUE;
END IF;
END IF;
--Find xy for end point address
IF n_run_length >= n_addr_length2 THEN
n_grad := get_grad_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist := get_length_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist_to_point := n_addr_length2;
l_ords2 := get_point_at_distance_as_ords(x_current
,y_current
,n_dist_to_point
,n_grad);
--
/*dbms_output.put_line('End X: ' || to_char(l_ords2(1)) ||
' End Y: ' || to_char(l_ords2(2)));
*/
IF found_start_pnt = FALSE THEN
raise_application_error(-20000,'Invalid start length');
END IF;
-- Insert into ordinate array
n_ords_count := l_ords_all.COUNT;
l_ords_all.EXTEND(2);
l_ords_all(n_ords_count + 1) := l_ords2(1);
l_ords_all(n_ords_count + 2) := l_ords2(2);
EXIT;
END IF;
--dbms_output.put_line('segment counter:' || to_char(i_v));
st_set := FALSE;
i := i + 2;
i_v := i_v + 1; --Increment segment counter
ELSE
-- Remaining segments
IF i + 1 >= last1 THEN
-- We are on the last pair
IF n_grad IS NULL OR n_dist IS NULL THEN
raise_application_error(-20000,'Could not create line');
END IF;
EXIT;
END IF;
--
x_current := pi_geom.sdo_ordinates(i);
y_current := pi_geom.sdo_ordinates(i + 1);
x_next := pi_geom.sdo_ordinates(i + 2);
y_next := pi_geom.sdo_ordinates(i + 3);
--
IF found_start_pnt = TRUE THEN
-- Insert into ordinate array
n_ords_count := l_ords_all.COUNT;
l_ords_all.EXTEND(2);
l_ords_all(n_ords_count + 1) := x_current;
l_ords_all(n_ords_count + 2) := y_current;
--l_ords_all(n_ords_count+3) := x_next;
--l_ords_all(n_ords_count+4) := y_next;
END IF;
-- Greate segment geometry
l_segment := get_line_from_xy(x_current
,y_current
,x_next
,y_next
,n_srid);
-- Get segment length
n_seg_length := get_line_length(l_segment);
n_run_length := n_run_length + n_seg_length;
--Find xy for start point address
IF found_start_pnt = FALSE THEN
IF n_run_length >= n_addr_length1 THEN
n_grad := get_grad_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist := get_length_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist_to_point := n_seg_length -
(n_run_length - n_addr_length1);
l_ords1 := get_point_at_distance_as_ords(x_current
,y_current
,n_dist_to_point
,n_grad);
-- Insert into ordinate array
l_ords_all := mdsys.sdo_ordinate_array(l_ords1(1),
l_ords1(2));
found_start_pnt := TRUE;
END IF;
END IF;
i := i + 2;
--dbms_output.put_line('segment counter:' || to_char(i_v));
END IF;
--
--Find xy for end point address
IF n_run_length >= n_addr_length2 THEN
n_grad := get_grad_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist := get_length_from_xy(x_current,
y_current,
x_next,
y_next);
n_dist_to_point := n_seg_length - (n_run_length - n_addr_length2);
l_ords2 := get_point_at_distance_as_ords(x_current,
y_current,
n_dist_to_point,
n_grad);
IF found_start_pnt = FALSE THEN
raise_application_error(-20000,'Invalid start length');
END IF;
-- Insert into ordinate array
n_ords_count := l_ords_all.COUNT;
l_ords_all.EXTEND(2);
l_ords_all(n_ords_count + 1) := l_ords2(1);
l_ords_all(n_ords_count + 2) := l_ords2(2);
END IF;
END LOOP;
/* -- DEBUG INFO
FOR i IN 1 .. l_ords_all.COUNT LOOP
dbms_output.put_line(to_char(l_ords_all(i)));
END LOOP;
*/
RETURN mdsys.sdo_geometry(2002,
n_srid,
NULL,
mdsys.sdo_elem_info_array(1,2,1),l_ords_all);
END IF;
EXCEPTION
WHEN inval_geom_type THEN
raise_application_error(-20003, 'Function does not support this spatial type (' || to_char(l_geom_type) || ')');
END lineAlong;

You can download the whole package with all the functions here.