Create homogenous road segments based on road characteristics (2 of 3)

In the previous post, I went through the main concepts of how to split a network into homogenous segments based on its properties which we created as linear events along the network.

We would now need to create the trigger functions to create the events and keep them in sync with any changes (reshapes) of the road network.

The trigger function for the event tables:

CREATE OR REPLACE FUNCTION trgfn_edit_event_loc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
------------------------------------------------------------------------------------------------------
--
-- The function that will run on the AFTER INSERT OR UPDATE trigger for any event
--
------------------------------------------------------------------------------------------------------
DECLARE
 v_event_type           varchar;
 v_start_offset         NUMERIC;
 v_end_offset           NUMERIC;
 v_tmp_offset           NUMERIC;
 v_event_id             int4;
 v_route_id             int8;
 v_route_geom           geometry;
 v_dist                 float8;
 new_asset_geom         geometry;
 v_radius               float:=20.0;
 v_geom_part            geometry;
BEGIN
    v_event_type := SUBSTRING(TG_TABLE_NAME, 3);
    IF NEW.road_id IS NULL THEN
        RAISE EXCEPTION 'Road id is mandatory';
    END IF;
    --Get the route's geometry
    SELECT id, geom INTO v_route_id, v_route_geom FROM roads WHERE id= NEW.road_id;
    -- Use the same route for start and end points
    SELECT ST_InterpolatePoint(ST_GeometryN(v_route_geom,1), ST_StartPoint(ST_GeometryN(NEW.geom,1))) INTO v_start_offset;
    SELECT ST_InterpolatePoint(ST_GeometryN(v_route_geom,1), ST_EndPoint(ST_GeometryN(NEW.geom,1))) INTO v_end_offset;
    
    -- Swap start/end offset if required
    IF v_start_offset > v_end_offset THEN
       v_tmp_offset:=  v_start_offset;
       v_start_offset:= v_end_offset;
       v_end_offset:= v_tmp_offset;
    END IF;
    -- Calculate new geometry based on route and start/end offsets
    SELECT ST_LocateBetween(v_route_geom, round(v_start_offset,0), round(v_end_offset,0)) INTO new_asset_geom;
    -- In some cases, you may also get a point and a line as a result of the ST_LocateBetween function
    -- so we nee to loop through each element in the collection and ensure we pick up the line geometry
    FOR v_geom_part IN SELECT (ST_Dump(geom)).geom FROM (SELECT (ST_Dump(new_asset_geom)).* AS geom) AS elements
    LOOP
        IF GeometryType(v_geom_part) = 'LINESTRINGM' THEN
            SELECT ST_Force2D(v_geom_part) INTO new_asset_geom;
            -- Found the linestring, exit loop
            EXIT;
        END IF;
    END LOOP;
    NEW.geom:= new_asset_geom;
    NEW.start_offset:= round(v_start_offset,0);
    NEW.end_offset:= round(v_end_offset,0);
    NEW.road_id:= v_route_id;
    RETURN NEW;
END;
$function$;

We would then need to create the BEFORE INSERT OR UPDATE triggers on the event tables:

CREATE TRIGGER biu_slope15  BEFORE INSERT OR UPDATE ON  slope15  
    FOR EACH ROW EXECUTE PROCEDURE trgfn_edit_event_loc(); 
CREATE TRIGGER biu_road_width BEFORE INSERT OR UPDATE ON road_width 
    FOR EACH ROW EXECUTE PROCEDURE trgfn_edit_event_loc();
CREATE TRIGGER biu_ortho50 BEFORE INSERT OR UPDATE   ON ortho50
    FOR EACH ROW EXECUTE PROCEDURE trgfn_edit_event_loc();

You should now be able to create events by simply clicking on the road and the event geometry will be created using dynamic segmentation. Since the code is using a fixed value for the tolerance (the v_radius variable highlighted in the code above), you should also need to define the road id you want the geometry to be created on, to make sure it does not pick a different segment for the start and end point:

We also need to cover cases where the road segment gets re-aligned. In this case, all event geometries will need to be recalculated. We can achieve this with another trigger function which we will then apply to the roads table:

CREATE OR REPLACE FUNCTION events.trgfn_update_event_locs()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
------------------------------------------------------------------------------------------------------
--
-- The function that will run on the AFTER INSERT OR UPDATE trigger for any road edits
--
------------------------------------------------------------------------------------------------------
DECLARE
  -- Array of event table names
  events text[] = ARRAY['slope11', 'road_width', 'ortho50'];
  event_name text;
  event_rec         record;
  v_start_offset  NUMERIC;
  v_end_offset    NUMERIC;
BEGIN
    FOREACH event_name IN ARRAY events
    LOOP
        FOR event_rec IN 
            EXECUTE format ('SELECT id, geom FROM %I WHERE road_id = $1.id', event_name) USING NEW
        LOOP 
            SELECT ST_InterpolatePoint(ST_GeometryN(NEW.geom,1), ST_StartPoint(ST_GeometryN(event_rec.geom, 1))) INTO v_start_offset; 
            SELECT ST_InterpolatePoint(ST_GeometryN(NEW.geom,1), ST_EndPoint(ST_GeometryN(event_rec.geom, 1))) INTO v_end_offset; 
            EXECUTE format ('UPDATE %I SET start_offset=round($2,0), end_offset= round($3,0)
                            WHERE id= $1', event_name) USING event_rec.id, v_start_offset, v_end_offset;
        END LOOP;
    END LOOP;
    RETURN NEW;
END;
$function$;

And now create the trigger:

CREATE TRIGGER aiu_roads AFTER INSERT OR UPDATE
    ON public.roads FOR EACH ROW EXECUTE FUNCTION trgfn_update_event_locs()

This means that if we reshape (say in QGIS) any of the network segments where events are located, the geometries of these events will get recalculated to match the segment’s geometry. Like so:

In the next and final post, we will bring all that together, splitting the network using the events’ geometries. See you there!