gis Database

Parameters

Name	Type	Mode

Definition

DECLARE pid int;
            geo geometry;
            ids text;
BEGIN
    --INSERT a new portion
    INSERT INTO veloroutes.portion(
        nom,
        description,
        type_portion,
        id_on3v,
        id_local,
        mont_subv,
        annee_subv,
        convention,
        type_pluriannuel
    ) VALUES (
        NEW.nom,
        NEW.description,
        NEW.type_portion,
        NEW.id_on3v,
        NEW.id_local,
        NEW.mont_subv,
        NEW.annee_subv,
        NEW.convention,
        NEW.type_pluriannuel
    ) RETURNING id_portion into pid;

--INSERT in element elements of the new portion
    INSERT INTO veloroutes.element(id_portion,id_segment)
    SELECT pid, veloroutes.segment.id_segment
    FROM veloroutes.segment
    --segments must be around the new geometry
    WHERE ST_DWithin(veloroutes.segment.geom,NEW.geom, 0.01)
    --segments that share just one vertex with the new geom are eliminated
    AND ST_Within(veloroutes.segment.geom,ST_Buffer(NEW.geom,1));

--Warning for the user if the selection includes a piece of segment
    --The selection should only be composed by full segments
    FOR ids IN
        SELECT veloroutes.segment.id_segment
        FROM veloroutes.segment
        --Optional
        WHERE ST_DWithin(veloroutes.segment.geom,NEW.geom, 0.01)
        --Segments whose geometry is only partially included in the selection
        AND ST_Overlaps(veloroutes.segment.geom,NEW.geom)
    LOOP
      RAISE NOTICE 'Le segment (%) ne peut pas être partiellement séléctionné',ids;
    END LOOP;

RETURN NEW;

END;

v_portion_insert

Description

Parameters

Definition