gis.road_graph

Parameters

Name	Type	Mode
		IN

Definition

DECLARE
    new_jsonb jsonb;
    old_jsonb jsonb;
    managed_object record;
    primary_key_field text;
    old_end_points geometry;
    linked record;
    linked_jsonb jsonb;
    linked_start record;
    linked_end record;
    start_marker_code integer;
    start_abscissa real;
    end_marker_code integer;
    end_abscissa real;
    cumulative_sql text;
    substring_jsonb jsonb;
    linked_geom geometry;
    sql_text text;
    updated_number integer = 0;
    -- Maximum distance between the end points of two lines considered as connected
    tolerance constant real = 0.05;
BEGIN
    -- Trigger disabled by session variable, for example when merging the editing session,
    -- when the managed objects are updated from the road graph, when splitting or merging lines,
    -- or when this trigger updates the linked lines (to avoid a cascade of updates)
    IF road_graph.get_current_setting('road.graph.disable.trigger', '0') = '1'
        OR road_graph.get_current_setting('road.graph.disable.linked.geometries', '0') = '1'
    THEN
        RETURN NEW;
    END IF;

-- Only for the updates of the geometry
    IF TG_OP != 'UPDATE' OR NEW.geom IS NULL OR OLD.geom IS NULL
        OR ST_Equals(NEW.geom, OLD.geom)
    THEN
        RETURN NEW;
    END IF;

new_jsonb = to_jsonb(NEW);
    old_jsonb = to_jsonb(OLD);

-- Check the table has the needed reference fields
    IF NOT new_jsonb ?& ARRAY['road_code', 'start_marker_code', 'start_abscissa', 'end_marker_code', 'end_abscissa'] THEN
        RAISE NOTICE 'The table "%"."%" does not have the reference fields needed to update the linked lines !',
            TG_TABLE_SCHEMA, TG_TABLE_NAME;
        RETURN NEW;
    END IF;

-- Only for the lines registered as managed objects
    SELECT o.*
    INTO managed_object
    FROM road_graph.managed_objects AS o
    WHERE o.schema_name = TG_TABLE_SCHEMA
    AND o.table_name = TG_TABLE_NAME
    LIMIT 1
    ;
    IF managed_object IS NULL THEN
        RAISE NOTICE 'The table "%"."%" is not registered as managed object in the road graph system !',
            TG_TABLE_SCHEMA, TG_TABLE_NAME;
        RETURN NEW;
    END IF;
    IF lower(managed_object.geometry_type) NOT IN ('linestring', 'multilinestring') THEN
        RETURN NEW;
    END IF;

-- The linked lines follow the edited line only if it stays on the same road
    IF Coalesce(trim(new_jsonb->>'road_code'), '') = ''
        OR new_jsonb->>'road_code' IS DISTINCT FROM old_jsonb->>'road_code'
    THEN
        RETURN NEW;
    END IF;

-- Nothing to do if the references have not changed.
    -- The references of NEW must have been calculated before, for example by the trigger
    -- trg_calculate_managed_object_references (BEFORE trigger, run before this AFTER trigger)
    IF (new_jsonb->>'start_marker_code', new_jsonb->>'start_abscissa')
        IS NOT DISTINCT FROM (old_jsonb->>'start_marker_code', old_jsonb->>'start_abscissa')
        AND (new_jsonb->>'end_marker_code', new_jsonb->>'end_abscissa')
        IS NOT DISTINCT FROM (old_jsonb->>'end_marker_code', old_jsonb->>'end_abscissa')
    THEN
        RETURN NEW;
    END IF;

-- Get the table primary key field
    SELECT a.attname
    INTO primary_key_field
    FROM pg_index AS i
    JOIN pg_attribute AS a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
    WHERE i.indrelid = TG_RELID AND i.indisprimary
    LIMIT 1
    ;
    IF primary_key_field IS NULL THEN
        RAISE NOTICE 'The table "%"."%" does not have a primary key !', TG_TABLE_SCHEMA, TG_TABLE_NAME;
        RETURN NEW;
    END IF;

-- Start and end points of the line before its modification
    old_end_points = ST_Collect(
        ST_StartPoint(ST_GeometryN(ST_Multi(OLD.geom), 1)),
        ST_EndPoint(ST_GeometryN(ST_Multi(OLD.geom), ST_NumGeometries(ST_Multi(OLD.geom))))
    );

-- Disable the triggers on the updated linked lines:
    -- their references are set here, and they must not update their own linked lines
    PERFORM set_config('road.graph.disable.trigger', '1', true);
    PERFORM set_config('road.graph.disable.linked.geometries', '1', true);

-- Loop over the other lines of the same road
    -- whose start or end point touched the start or end point of the line before its modification
    sql_text = format(
        $SQL$
        SELECT to_jsonb(t) AS feature
        FROM %1$I.%2$I AS t
        WHERE t.%3$I::text != $1
        AND t.road_code = $2
        AND ST_DWithin(t.geom, $3, $4)
        AND ST_DWithin(
            ST_Collect(
                ST_StartPoint(ST_GeometryN(ST_Multi(t.geom), 1)),
                ST_EndPoint(ST_GeometryN(ST_Multi(t.geom), ST_NumGeometries(ST_Multi(t.geom))))
            ),
            $3, $4
        )
        $SQL$,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME,
        primary_key_field
    );
    FOR linked IN EXECUTE sql_text
        USING old_jsonb->>primary_key_field, new_jsonb->>'road_code', old_end_points, tolerance
    LOOP
        linked_jsonb = linked.feature;

-- Current references of the linked line, in the road direction
        SELECT
            (linked_jsonb->>'start_marker_code')::integer AS marker_code,
            (linked_jsonb->>'start_abscissa')::real AS abscissa
        INTO linked_start;
        SELECT
            (linked_jsonb->>'end_marker_code')::integer AS marker_code,
            (linked_jsonb->>'end_abscissa')::real AS abscissa
        INTO linked_end;
        IF linked_start.marker_code IS NULL OR linked_start.abscissa IS NULL
            OR linked_end.marker_code IS NULL OR linked_end.abscissa IS NULL
        THEN
            CONTINUE;
        END IF;

start_marker_code = linked_start.marker_code;
        start_abscissa = linked_start.abscissa;
        end_marker_code = linked_end.marker_code;
        end_abscissa = linked_end.abscissa;
        cumulative_sql = '';

IF (linked_start.marker_code, linked_start.abscissa) < (
            (old_jsonb->>'start_marker_code')::integer, (old_jsonb->>'start_abscissa')::real
        ) THEN
            -- The linked line is upstream: it ended at the start of the line.
            -- Its end must follow the new start of the line
            IF (new_jsonb->>'start_marker_code', new_jsonb->>'start_abscissa')
                IS NOT DISTINCT FROM (old_jsonb->>'start_marker_code', old_jsonb->>'start_abscissa')
            THEN
                CONTINUE;
            END IF;
            end_marker_code = (new_jsonb->>'start_marker_code')::integer;
            end_abscissa = (new_jsonb->>'start_abscissa')::real;
            IF linked_jsonb ? 'end_cumulative' AND new_jsonb ? 'start_cumulative' THEN
                cumulative_sql = format(', end_cumulative = %L::real', new_jsonb->>'start_cumulative');
            END IF;
        ELSIF (linked_end.marker_code, linked_end.abscissa) > (
            (old_jsonb->>'end_marker_code')::integer, (old_jsonb->>'end_abscissa')::real
        ) THEN
            -- The linked line is downstream: it started at the end of the line.
            -- Its start must follow the new end of the line
            IF (new_jsonb->>'end_marker_code', new_jsonb->>'end_abscissa')
                IS NOT DISTINCT FROM (old_jsonb->>'end_marker_code', old_jsonb->>'end_abscissa')
            THEN
                CONTINUE;
            END IF;
            start_marker_code = (new_jsonb->>'end_marker_code')::integer;
            start_abscissa = (new_jsonb->>'end_abscissa')::real;
            IF linked_jsonb ? 'start_cumulative' AND new_jsonb ? 'end_cumulative' THEN
                cumulative_sql = format(', start_cumulative = %L::real', new_jsonb->>'end_cumulative');
            END IF;
        ELSE
            -- The linked line overlaps the line: it is not modified
            CONTINUE;
        END IF;

-- The line has been extended over the whole linked line: it cannot be calculated
        IF (start_marker_code, start_abscissa) >= (end_marker_code, end_abscissa) THEN
            RAISE NOTICE 'The linked line "%"."%" % = % is not modified: its new start references % + % are not before its new end references % + %',
                TG_TABLE_SCHEMA, TG_TABLE_NAME, primary_key_field, linked_jsonb->>primary_key_field,
                start_marker_code, start_abscissa, end_marker_code, end_abscissa
            ;
            CONTINUE;
        END IF;

-- Build the new geometry of the linked line from its new references,
        -- keeping its offset and side
        BEGIN
            substring_jsonb = road_graph.get_road_substring_from_references(
                linked_jsonb->>'road_code',
                start_marker_code, start_abscissa,
                end_marker_code, end_abscissa,
                Coalesce((linked_jsonb->>'offset')::real, 0.0::real),
                Coalesce(linked_jsonb->>'side', 'right')
            );
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'The geometry of the linked line "%"."%" % = % cannot be calculated: %',
                TG_TABLE_SCHEMA, TG_TABLE_NAME, primary_key_field, linked_jsonb->>primary_key_field, SQLERRM
            ;
            CONTINUE;
        END;
        linked_geom = ST_GeomFromGeoJSON(substring_jsonb->>'geom');
        IF linked_geom IS NULL OR ST_IsEmpty(linked_geom) THEN
            CONTINUE;
        END IF;
        linked_geom = ST_SetSRID(linked_geom, 2154);

-- Adapt the geometry to the type of the table
        IF lower(managed_object.geometry_type) = 'linestring' THEN
            linked_geom = ST_LineMerge(linked_geom);
            IF GeometryType(linked_geom) != 'LINESTRING' THEN
                RAISE NOTICE 'The geometry of the linked line "%"."%" % = % is not a single linestring',
                    TG_TABLE_SCHEMA, TG_TABLE_NAME, primary_key_field, linked_jsonb->>primary_key_field
                ;
                CONTINUE;
            END IF;
        ELSE
            linked_geom = ST_Multi(linked_geom);
        END IF;

-- Update the geometry and the references of the linked line
        EXECUTE format(
            $SQL$
            UPDATE %1$I.%2$I
            SET
                geom = $1,
                start_marker_code = $2, start_abscissa = $3,
                end_marker_code = $4, end_abscissa = $5
                %4$s
            WHERE %3$I::text = $6
            $SQL$,
            TG_TABLE_SCHEMA,
            TG_TABLE_NAME,
            primary_key_field,
            cumulative_sql
        )
        USING
            linked_geom,
            start_marker_code, start_abscissa,
            end_marker_code, end_abscissa,
            linked_jsonb->>primary_key_field
        ;
        updated_number = updated_number + 1;
    END LOOP;

-- Re-enable the triggers
    PERFORM set_config('road.graph.disable.linked.geometries', '0', true);
    PERFORM set_config('road.graph.disable.trigger', '0', true);

IF updated_number > 0 THEN
        RAISE NOTICE 'Number of linked lines updated in "%"."%": %', TG_TABLE_SCHEMA, TG_TABLE_NAME, updated_number;
    END IF;

RETURN NEW;
END;

update_managed_object_linked_geometries()

Description

Parameters

Definition