gis.road_graph

Parameters

Name	Type	Mode
_schema_name	text	IN
_table_name	text	IN
_ids	integer[]	IN

Definition

DECLARE
    primary_key_field text;
    geometry_column text;
    sql_text text;
    merged_geom geometry(MultiPolygon, 2154);
BEGIN
    -- Return NULL if no ids are passed
    IF _ids IS NULL OR _ids = ARRAY[]::integer[] THEN
        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 colmun 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;

-- Get multi geometry of all given features ids
    sql_text = format(
        $SQL$
        SELECT
            ST_Multi(ST_Buffer(ST_Union(%1$I), 10)) AS geom
            FROM %2$I.%3$I
            WHERE %4$I IN (%5$s)
        $SQL$,
        geometry_column,
        _schema_name,
        _table_name,
        primary_key_field,
        array_to_string(_ids::text[], ', ')
    );
    EXECUTE sql_text
    INTO merged_geom
    ;

RETURN merged_geom;
END;

get_merged_geom_from_table_and_ids(_schema_name text, _table_name text, _ids integer[])

Description

Parameters

Definition