gis Database

Parameters

Name	Type	Mode

Definition

DECLARE iti_id int; ids text;

BEGIN

--INSERT a new row in itineraire
    INSERT INTO veloroutes.itineraire(
        numero,
        nom_officiel,
        nom_usage,
        depart,
        arrivee,
        annee_inscription,
        site_web,
        annee_ouverture,
        niveau_schema,
        est_inscrit
    ) VALUES (
        NEW.numero,
        NEW.nom_officiel,
        NEW.nom_usage,
        NEW.depart,
        NEW.arrivee,
        NEW.annee_inscription,
        NEW.site_web,
        NEW.annee_ouverture,
        NEW.niveau_schema,
        NEW.est_inscrit
    ) RETURNING id_itineraire into iti_id;

--INSERT stages of the itineray in etape
    INSERT INTO veloroutes.etape(id_itineraire,id_portion)
    SELECT iti_id, vp.id_portion
    FROM veloroutes.v_portion vp
    --segments must be around the new geometry
    WHERE ST_DWithin(NEW.geom, vp.geom,0.01)
    --segments that share just one vertex with the new geom are eliminated
    AND ST_Within(vp.geom,ST_Buffer(NEW.geom,1));

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

END;

v_itineraire_insert

Description

Parameters

Definition