gis.road_graph

Parameters

Name	Type	Mode
_schema_name	text	IN
_table_name	text	IN
_road_codes	text[]	IN
_update_offset_and_side	boolean	IN
_impacts	jsonb	IN

Definition

DECLARE
    road_code text;
    update_count integer;
    sql_text text;
    table_exists boolean;
    primary_key_field text;
    geometry_column text;
    needed_fields text[];
    managed_object record;
    table_cols text[];
    updated_stats record;
    cached_road_codes text[];
    cached_road_code text;
    impacts_filter text;
BEGIN

-- Get info on the managed object table
    sql_text = format(
        $SQL$
        SELECT *
        FROM road_graph.managed_objects
        WHERE
            schema_name = '%1$s'
            AND table_name = '%2$s'
        LIMIT 1;
        $SQL$,
        _schema_name,
        _table_name
    );
    EXECUTE sql_text
    INTO managed_object
    ;
    IF managed_object IS NULL THEN
        RAISE NOTICE 'The table "%"."%" is not registered as managed object in the road graph system !', _schema_name, _table_name;
        RETURN NULL;
    END IF;

-- Check if the table exists in the database
    sql_text = format(
        $SQL$
        SELECT to_regclass('%1$I.%2$I') IS NOT NULL AS exists
        $SQL$,
        _schema_name,
        _table_name
    );
    EXECUTE sql_text
    INTO table_exists
    ;
    IF NOT table_exists THEN
        RAISE NOTICE 'The table "%"."%" does not exist in the database !', _schema_name, _table_name;
        RETURN NULL;
    END IF;

-- Get the table primary key field
    sql_text = format(
        $SQL$
        SELECT a.attname
        FROM pg_index i
        JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
        WHERE i.indrelid = '%1$I.%2$I'::regclass AND i.indisprimary
        $SQL$,
        _schema_name,
        _table_name
    );
    EXECUTE sql_text
    INTO primary_key_field
    ;
    IF primary_key_field IS NULL THEN
        RAISE NOTICE 'The table "%"."%" does not have a primary key !', _schema_name, _table_name;
        RETURN NULL;
    END IF;

-- Get geometry column name
    sql_text = format(
        $SQL$
        SELECT f_geometry_column
        FROM geometry_columns
        WHERE f_table_schema = '%1$s' AND f_table_name = '%2$s';
        $SQL$,
        _schema_name,
        _table_name
    );
    EXECUTE sql_text
    INTO geometry_column
    ;
    IF geometry_column IS NULL THEN
        RAISE NOTICE 'The table "%"."%" does not have a geometry column !', _schema_name, _table_name;
        RETURN NULL;
    END IF;

-- Check the table contains the needed fields based on the geometry type of the managed object
    sql_text = format(
        $SQL$
        SELECT array_agg(column_name) AS cols
        FROM information_schema.columns
        WHERE table_schema = '%1$s' AND table_name = '%2$s';
        $SQL$,
        _schema_name,
        _table_name
    );
    EXECUTE sql_text
    INTO table_cols
    ;
    -- For point geometry type, we need road_code, marker_code and abscissa fields
    needed_fields = ARRAY[
        'road_code', 'marker_code', 'abscissa'
    ]::text[];
    -- For line geometry type, we also need start_marker_code, start_abscissa, end_marker_code and end_abscissa fields
    IF lower(managed_object.geometry_type) IN ('linestring', 'multilinestring')
    THEN
        needed_fields = ARRAY[
            'road_code',
            'start_marker_code', 'start_abscissa',
            'end_marker_code', 'end_abscissa'
        ]::text[];
    END IF;
    IF NOT (table_cols @> needed_fields)
        THEN
            RAISE NOTICE 'The table "%"."%" does not contain the necessary fields to update references from geometries !', _schema_name, _table_name;
            RETURN NULL;
    END IF;

-- Filter the objects which may be impacted by the modifications of the road graph, if given
    impacts_filter = CASE
        WHEN _impacts IS NULL THEN 'TRUE'
        ELSE road_graph.get_managed_objects_impacts_filter(managed_object.geometry_type, table_cols, geometry_column)
    END;

-- Build the road cache for the roads of the objects to update
    -- It is always built again here, even if it already exists for a road,
    -- since the road graph could have been modified since its last build in the same transaction
    sql_text = format(
        $SQL$
        SELECT array_agg(DISTINCT trim(mo.road_code)::text)
        FROM %1$I.%2$I AS mo
        WHERE Coalesce(trim(mo.road_code), '') != ''
        AND (
            mo.road_code::text = ANY(string_to_array('%3$s', ',')::text[])
            OR '%3$s' = ''
        )
        $SQL$,
        _schema_name,
        _table_name,
        Coalesce(array_to_string(_road_codes::text[], ','), '')
    );
    EXECUTE sql_text
    INTO cached_road_codes
    ;
    FOREACH cached_road_code IN ARRAY Coalesce(cached_road_codes, ARRAY[]::text[]) LOOP
        PERFORM road_graph.build_road_cached_objects(cached_road_code);
    END LOOP;

-- Get road information

-- Update references based on geometries
    -- and geometry type of the managed object
    -- We filter the objects to update based on the given road codes
    IF lower(managed_object.geometry_type) = 'point' THEN
        sql_text = format(
            $SQL$
                WITH
                objects AS (
                    SELECT
                        mo.%1$I AS id,
                        trim(mo.road_code)::text AS road_code,
                        mo.%9$I AS geom
                    FROM
                        %2$I.%3$I AS mo
                    WHERE (
                        mo.road_code::text = ANY(string_to_array('%4$s', ',')::text[])
                        OR '%4$s' = ''
                    )
                    AND (%10$s)
                ),
                refs AS (
                    SELECT
                        o.id,
                        road_graph.get_reference_from_point(
                            o.geom,
                            -- We need to pass the road code to force the calculation to keep references for this road
                            o.road_code::text,
                            -- NULL::text,
                            -- Use the road cache built before
                            TRUE
                        ) AS ref
                    FROM objects AS o
                ),
                run_update AS (
                    UPDATE %2$I.%3$I AS mo
                    SET
                        -- Do no change the road code if no references have been found
                        -- for the given road (meaning the object is too far)
                        road_code =
                        CASE
                            -- keep object road_code intact if it is not empty
                            WHEN Coalesce(mo.road_code, '') != '' THEN mo.road_code
                            ELSE r.ref->>'road_code'
                        END,
                        -- cumulative if present
                        %5$s
                        -- offset if present and if _update_offset_and_side is True
                        %6$s
                        -- side if present and if _update_offset_and_side is True
                        %7$s
                        -- marker code and abscissa put here to avoid errors with commas
                        marker_code = (r.ref->>'marker_code')::integer,
                        abscissa = (r.ref->>'abscissa')::real
                    FROM refs AS r
                    WHERE TRUE
                    AND mo.%1$I = r.id
                    --AND r.ref->'road_code' != 'null'::jsonb
                    AND (%8$s)
                    RETURNING mo.*
                )
                SELECT
                    count(r.*) AS nb,
                    array_agg(r.%1$I ORDER BY r.%1$I) AS last_updated_objects_ids
                FROM run_update AS r
            $SQL$,
            primary_key_field,
            _schema_name,
            _table_name,
            Coalesce(array_to_string(_road_codes::text[], ','), ''),
            -- 5 / add update for cumulative if the columns exists in the target table
            CASE WHEN 'cumulative' = ANY(table_cols) THEN $STR$cumulative = (r.ref->>'cumulative')::real, $STR$ ELSE '' END,
            -- 6 / add update for offset if the columns exists in the target table
            CASE
                WHEN 'offset' = ANY(table_cols) AND _update_offset_and_side IS True
                    THEN $STR$"offset" = Coalesce((r.ref->>'offset')::real, 0.0::real), $STR$
                ELSE ''
            END,
            -- 7 / add update for side if the columns exists in the target table
            CASE
                WHEN 'side' = ANY(table_cols) AND _update_offset_and_side IS True
                    THEN $STR$side = Coalesce((r.ref->>'side')::text, 'right'), $STR$
                ELSE ''
            END,
            -- 8 / Detect if we need to update or not
            -- A difference of 1 cm of the abscissa or cumulative is ignored: calculating the geometry from the references,
            -- then the references from this geometry rounded to 1 cm, can give a difference of 1 cm.
            -- Without this tolerance, the lines of the update policy geometry were shortened by 1 cm at each update
            concat(
                $STR$
                (
                    ( Coalesce(mo.road_code, '') != '' AND coalesce((r.ref->>'road_code'), '') = '' )
                    OR
                    ( Coalesce(mo.road_code, '') = '' AND coalesce((r.ref->>'road_code'), '') != '' )
                )
                OR (r.ref->>'marker_code')::integer != Coalesce(mo.marker_code, -1)::integer
                OR abs((r.ref->>'abscissa')::real - Coalesce(mo.abscissa, -1)::real) > 0.015
                $STR$,
                CASE WHEN 'cumulative' = ANY(table_cols)
                    THEN $STR$ OR abs((r.ref->>'cumulative')::real - Coalesce(mo.cumulative, -1)::real) > 0.015 $STR$ ELSE ''
                END,
                CASE
                    WHEN 'offset' = ANY(table_cols) AND _update_offset_and_side IS True
                        THEN $STR$ OR Coalesce((r.ref->>'offset')::real, 0.0::real) != Coalesce(mo.offset, 0.0)::real $STR$
                    ELSE ''
                END,
                CASE
                    WHEN 'side' = ANY(table_cols) AND _update_offset_and_side IS True
                        THEN $STR$ OR Coalesce((r.ref->>'side')::text, 'right') != Coalesce(mo.side, 'right')::text $STR$
                    ELSE ''
                END
            ),
            -- 9 / geometry_column
            geometry_column,
            -- 10 / filter of the impacted objects
            impacts_filter
        );

ELSIF lower(managed_object.geometry_type) IN ('linestring', 'multilinestring') THEN
        sql_text = format(
            $SQL$
                WITH
                objects AS (
                    SELECT
                        mo.%1$I AS id,
                        trim(mo.road_code)::text AS road_code,
                        mo.%10$I AS geom
                    FROM
                        %2$I.%3$I AS mo
                    WHERE (
                        mo.road_code::text = ANY(string_to_array('%4$s', ',')::text[])
                        OR '%4$s' = ''
                    )
                    AND (%11$s)
                ),
                road_info AS (
                    SELECT r.road_code, r.road_type,
                        min(e.start_marker) AS min_code,
                        max(e.end_marker) AS max_code,
                        min(e.start_cumulative) AS min_cumulative,
                        max(e.end_cumulative) AS max_cumulative,
                        min(e.start_abscissa) AS min_abscissa,
                        max(e.end_abscissa) AS max_abscissa
                    FROM road_graph.edges AS e
                    JOIN road_graph.roads AS r
                        USING (road_code)
                    WHERE e.road_code IN (
                        SELECT DISTINCT o.road_code
                        FROM objects AS o
                    )
                    GROUP BY r.road_code, r.road_type
                ),
                refs AS (
                    SELECT
                        o.id, o.road_code,
                        road_graph.get_reference_from_point(
                            -- ST_StartPoint could return NULL for a MULTILINESTRING
                            CASE
                                WHEN lower(GeometryType(o.geom)) = 'linestring'
                                    THEN ST_StartPoint(o.geom)
                                -- hopefully the last part is really the end part of the multilinestring
                                ELSE ST_StartPoint(ST_GeometryN(o.geom, 1))
                            END,
                            -- We need to pass the road code to force the calculation to keep references for this road
                            o.road_code,
                            -- NULL::text,
                            -- Use the road cache built before
                            TRUE
                        ) AS start_ref,
                        road_graph.get_reference_from_point(
                            -- ST_EndPoint could return NULL for a MULTILINESTRING
                            CASE
                                WHEN lower(GeometryType(o.geom)) = 'linestring'
                                    THEN ST_EndPoint(o.geom)
                                -- hopefully the last part is really the end part of the multilinestring
                                ELSE ST_EndPoint(ST_GeometryN(geom, ST_NumGeometries(geom)))
                            END,
                            -- We need to pass the road code to force the calculation to keep references for this road
                            o.road_code,
                            -- NULL::text,
                            -- Use the road cache built before
                            TRUE
                        ) AS end_ref
                    FROM objects AS o
                ),
                processed_refs AS (
                    SELECT
                        r.id,
                        r.road_code,
                        -- start_ref
                        CASE
                            -- For roundabout, if the start and end values are equal
                            -- use 0+0 for start & 0+max_cumulative for end
                            WHEN i.road_type = 'roundabout'
                            AND Coalesce((r.start_ref->>'abscissa')::real, 0) = Coalesce((r.end_ref->>'abscissa')::real, 0)
                                THEN jsonb_build_object(
                                    'road_code', r.start_ref->>'road_code',
                                    'marker_code', 0,
                                    'abscissa', 0.0,
                                    'cumulative', 0.0,
                                    'offset', r.start_ref->>'offset',
                                    'side', r.start_ref->'side'
                                )
                            -- for other roads, invert start and end if start < end
                            WHEN
                            Coalesce((r.start_ref->>'marker_code')::int * 10000 + (r.start_ref->>'abscissa')::real, 0)
                            >
                            Coalesce((r.end_ref->>'marker_code')::int * 10000 + (r.end_ref->>'abscissa')::real, 0)
                                THEN end_ref
                            ELSE r.start_ref
                        END AS start_ref,
                        -- end_ref
                        CASE
                            -- For roundabout, if the start and end values are equal
                            -- use 0+0 for start & 0+max_cumulative for end
                            WHEN i.road_type = 'roundabout'
                            AND Coalesce((r.start_ref->>'abscissa')::real, 0) = Coalesce((r.end_ref->>'abscissa')::real, 0)
                                THEN jsonb_build_object(
                                    'road_code', r.end_ref->>'road_code',
                                    'marker_code', 0,
                                    'abscissa', i.max_abscissa,
                                    'cumulative', i.max_cumulative,
                                    'offset', r.end_ref->>'offset',
                                    'side', r.end_ref->'side'
                                )
                            -- for other roads, invert start and end if start < end
                            WHEN
                            Coalesce((r.start_ref->>'marker_code')::int * 10000 + (r.start_ref->>'abscissa')::real, 0)
                            >
                            Coalesce((r.end_ref->>'marker_code')::int * 10000 + (r.end_ref->>'abscissa')::real, 0)
                                THEN start_ref
                            ELSE r.end_ref
                        END AS end_ref
                    FROM refs AS r
                    JOIN road_info AS i
                        USING (road_code)
                ),
                run_update AS (
                    UPDATE %2$I.%3$I AS mo
                    SET
                        -- Do no change the road code if no references have been found
                        -- for the given road (meaning the object is too far)
                        road_code =
                        CASE
                            -- keep object road_code intact if it is not empty
                            WHEN Coalesce(mo.road_code, '') != '' THEN mo.road_code
                            ELSE r.start_ref->>'road_code'
                        END,

-- start_cumulative if present
                        %5$s
                        -- offset if present
                        %6$s
                        -- side if present
                        %7$s
                        -- end_cumulative if present
                        %8$s
                        -- marker code and abscissa put here to avoid errors with commas
                        start_marker_code = (r.start_ref->>'marker_code')::integer,
                        start_abscissa = (r.start_ref->>'abscissa')::real,
                        end_marker_code = (r.end_ref->>'marker_code')::integer,
                        end_abscissa = (r.end_ref->>'abscissa')::real
                    FROM processed_refs AS r
                    WHERE TRUE
                    AND mo.%1$I = r.id
                    -- Do not UPDATE if no changes must be made (values already are the same)
                    AND (%9$s)
                    RETURNING mo.*
                )
                SELECT
                    count(r.*) AS nb,
                    array_agg(r.%1$I ORDER BY r.%1$I) AS last_updated_objects_ids
                FROM run_update AS r
            $SQL$,
            primary_key_field,
            _schema_name,
            _table_name,
            Coalesce(array_to_string(_road_codes::text[], ','), ''),
            -- 5 / add update for cumulative if the columns exists in the target table
            CASE WHEN 'start_cumulative' = ANY(table_cols) THEN $STR$"start_cumulative" = (r.start_ref->>'cumulative')::real, $STR$ ELSE '' END,
            -- 6 / add update for offset if the columns exists in the target table
            CASE
                WHEN 'offset' = ANY(table_cols) AND _update_offset_and_side IS True
                    THEN $STR$"offset" = Coalesce((r.start_ref->>'offset')::real, 0.0::real), $STR$
                ELSE ''
            END,
            -- 7 / add update for side if the columns exists in the target table
            CASE
                WHEN 'side' = ANY(table_cols) AND _update_offset_and_side IS True
                    THEN $STR$"side" = Coalesce((r.start_ref->>'side')::text, 'right'::text), $STR$
                ELSE ''
            END,
            -- 8 / add update for cumulative if the columns exists in the target table
            CASE WHEN 'end_cumulative' = ANY(table_cols) THEN $STR$"end_cumulative" = (r.end_ref->>'cumulative')::real, $STR$ ELSE '' END,
            -- 9 / Detect if we need to update or not
            -- A difference of 1 cm of the abscissa or cumulative is ignored: calculating the geometry from the references,
            -- then the references from this geometry rounded to 1 cm, can give a difference of 1 cm.
            -- Without this tolerance, the lines of the update policy geometry were shortened by 1 cm at each update
            concat(
                $STR$
                (
                    ( Coalesce(mo.road_code, '') != '' AND coalesce((r.start_ref->>'road_code'), '') = '' )
                    OR
                    ( Coalesce(mo.road_code, '') = '' AND coalesce((r.start_ref->>'road_code'), '') != '' )
                )
                OR (r.start_ref->>'marker_code')::integer != Coalesce(mo.start_marker_code, -1)::integer
                OR abs((r.start_ref->>'abscissa')::real - Coalesce(mo.start_abscissa, -1)::real) > 0.015
                OR (r.end_ref->>'marker_code')::integer != Coalesce(mo.end_marker_code, -1)::integer
                OR abs((r.end_ref->>'abscissa')::real - Coalesce(mo.end_abscissa, -1)::real) > 0.015
                $STR$,
                CASE WHEN 'start_cumulative' = ANY(table_cols)
                    THEN $STR$ OR abs((r.start_ref->>'cumulative')::real - Coalesce(mo.start_cumulative, -1)::real) > 0.015 $STR$ ELSE ''
                END,
                CASE
                    WHEN 'offset' = ANY(table_cols) AND _update_offset_and_side IS True
                        THEN $STR$ OR Coalesce((r.start_ref->>'offset')::real, 0.0::real) != Coalesce(mo.offset, 0.0)::real $STR$
                    ELSE ''
                END,
                CASE
                    WHEN 'side' = ANY(table_cols) AND _update_offset_and_side IS True
                        THEN $STR$ OR Coalesce((r.start_ref->>'side')::text, 'right') != Coalesce(mo.side, 'right')::text $STR$
                    ELSE ''
                END,
                CASE WHEN 'end_cumulative' = ANY(table_cols)
                    THEN $STR$ OR abs((r.end_ref->>'cumulative')::real - Coalesce(mo.end_cumulative, -1)::real) > 0.015 $STR$ ELSE ''
                END
            ),
            -- 10 / geometry_column
            geometry_column,
            -- 11 / filter of the impacted objects
            impacts_filter
        );
    END IF;

RAISE NOTICE 'sql = %', sql_text;
    EXECUTE sql_text
    INTO updated_stats
    USING _impacts;

RETURN to_jsonb(updated_stats);

END;

update_table_references_from_geometries(_schema_name text, _table_name text, _road_codes text[], _update_offset_and_side boolean DEFAULT true, _impacts jsonb DEFAULT NULL::jsonb)

Description

Parameters

Definition