gis.gobs

Parameters

Name	Type	Mode
spatial_layer_id	integer	IN
validity_date	date	IN
id	integer	TABLE
code	text	TABLE
label	text	TABLE
uid	text	TABLE
valid_from	date	TABLE
valid_to	date	TABLE
id_actor	integer	TABLE
geom	geometry	TABLE

Definition

DECLARE
    _spatial_layer record;
    _sql_text text;
BEGIN
    -- Get spatial layer data
    SELECT INTO _spatial_layer
        *
    FROM gobs.spatial_layer AS sl
    WHERE sl.id = spatial_layer_id;

IF _spatial_layer.id IS NULL THEN
        RAISE EXCEPTION 'G-Obs - No spatial layer found for the given ID %',
            spatial_layer_id
        ;
    END IF;

-- Build SQL
    _sql_text = format(
        $sql$
        SELECT
            so.id,
            so.so_unique_id AS code,
            so.so_unique_label AS label,
            so.so_uid::text AS uid,
            so.so_valid_from AS valid_from,
            so.so_valid_to AS valid_to,
            so.fk_id_actor AS id_actor,
            so.geom::public.geometry(%1$s, 4326) AS geom
        FROM gobs.spatial_object AS so
        WHERE so.fk_id_spatial_layer = %2$s
        $sql$,
        _spatial_layer.sl_geometry_type,
        _spatial_layer.id
    )
    ;

-- Add optionnal date filter
    IF validity_date IS NOT NULL THEN
        _sql_text = _sql_text || format(
            $sql$
            AND
                (
                    (so_valid_from IS NULL OR so_valid_from <= '%1$s'::date)
                    AND
                    (so_valid_to IS NULL OR so_valid_to > '%1$s'::date)
                )
            $sql$,
            validity_date::text
        );

END IF;

RETURN QUERY
    EXECUTE _sql_text
    ;
END;

get_spatial_layer_vector_data(spatial_layer_id integer, validity_date date)

Description

Parameters

Definition