Le portail francophone de la géomatique

raster31 · Tue 03 October 2017 15:59

Bonjour à tous,

je ne suis pas un grand spécialiste en base de données postgresql/postgis mais j'essaie de développer un trigger pour remplir certains champs attributaires de manière automatique. Ma base de données Postgis est constituée d'un schéma "engagements_helicopteres" contenant 3 tables spatiales : hélicopteres, communes et departements.

Dans Qgis, j'ai une couche de ponctuelles (des hélicoptères de secours) qui seront positionnées sur deux couches surfaciques superposées à savoir les communes et les départements. Dans Qgis (puis Lizmap), un formulaire de saisie apparaitra alors lorsqu'un point hélico sera créé et il faudrait que certains champs de cette table/couche hélicoptères soient déjà préremplis avec le nom et le code insee de la commune, le code du département et la date du jour de la saisie.
J'ai fonctionné jusque là avec le duo d'extensions Autofields et refFunctions qui marchent très bien dans Qgis mais sur mon viewer Lizmap, cela ne peut pas marcher, je dois passer donc par un trigger via ma bdd.

Et je n'y comprends rien. Je sais que je dois déclarer une fonction au niveau de mon schéma "engagements_helicopteres" puis un trigger au niveau de ma table "helicopteres". J'ose pas vraiment vous montrer mes tentatives, de toute façons, aucune ne marche...

structure de mes tables :

"helicopteres"
- id_postgis (serial)
- geom (point, 2154)
- date_automatique (date) (date du jour de l'intervention)
- departement_automatique (character, 2) (code du département dans lequel se trouve la commune d'intervention = code_dep dans la table "departements")
- destination_automatique (character varying, 50) (nom de la commune sur laquelle l'hélicoptère va devoir faire son intervention = nom_com dans la table "communes")
- insee_destination_automatique (character varying, 5) (code insee de la commune sur laquelle l'hélicoptère va devoir faire son intervention = insee_com dans la table "communes")

"communes"
- id_postgis (serial)
- geom (multipolygon, 2154)
- nom_com (character varying, 50)
- insee_com (character varying, 5)

"departements"
- id_postgis (serial)
- geom
- code_dep (character varying, 3)

Quelqu'un pourrait m'aider svp à mettre en place ce trigger!?

Je vous remercie.

Dernière modification par raster31 (Tue 03 October 2017 16:08)

tumasgiu · Wed 04 October 2017 00:30

Salut,

si j'ai bien compris que chaque enregistrement de la table helicopteres
comporte l'insee et le code_dep des 2 autres tables qui contiennent le ponctuel ?

Pourquoi ne pas faire une vue ?

Code:

ALTER TABLE engagements_helicopteres.helicopteres
DROP insee;
ALTER TABLE engagements_helicopteres.helicopteres
DROP code_dep;
ALTER TABLE engagements_helicopteres.helicopteres
RENAME TO helicopteres_ponct;

Code:

CREATE VIEW engagements_helicopteres.helicopteres AS
(
SELECT h.*, insee, code_dep FROM engagements_helicopteres.helicopteres_ponct
JOIN engagements_helicopteres.communes c ON st_within(h.geom, c.geom)
JOIN engagements_helicopteres.departements d ON st_within(h.geom, d.geom)
)

Code:

CREATE FUNCTION engagements_helicopteres.insert_heli() RETURNS TRIGGER
AS $$ INSERT INTO engagements_helicopteres.helicopteres_ponct NEW.*; $$
LANGUAGE PLPGSQL

Code:

CREATE TRIGGER engagements_helicopteres.insert_heli_trigger INSTEAD OF INSERT
ON engagements_helicopteres.helicopteres 
EXECUTE PROCEDURE engagements_helicopteres.insert_heli()

Dernière modification par tumasgiu (Wed 04 October 2017 00:56)

raster31 · Wed 04 October 2017 11:08

tumasgiu a écrit:

Salut,

si j'ai bien compris que chaque enregistrement de la table helicopteres
comporte l'insee et le code_dep des 2 autres tables qui contiennent le ponctuel ?

Pourquoi ne pas faire une vue ?

Code:

ALTER TABLE engagements_helicopteres.helicopteres
DROP insee;
ALTER TABLE engagements_helicopteres.helicopteres
DROP code_dep;
ALTER TABLE engagements_helicopteres.helicopteres
RENAME TO helicopteres_ponct;

Code:

CREATE VIEW engagements_helicopteres.helicopteres AS
(
SELECT h.*, insee, code_dep FROM engagements_helicopteres.helicopteres_ponct
JOIN engagements_helicopteres.communes c ON st_within(h.geom, c.geom)
JOIN engagements_helicopteres.departements d ON st_within(h.geom, d.geom)
)

Code:

CREATE FUNCTION engagements_helicopteres.insert_heli() RETURNS TRIGGER
AS $$ INSERT INTO engagements_helicopteres.helicopteres_ponct NEW.*; $$
LANGUAGE PLPGSQL

Code:

CREATE TRIGGER engagements_helicopteres.insert_heli_trigger INSTEAD OF INSERT
ON engagements_helicopteres.helicopteres 
EXECUTE PROCEDURE engagements_helicopteres.insert_heli()

déjà merci beaucoup pour votre réponse,

donc si je comprends bien et si je décompose vos différentes actions, je dois :

- dans un premier temps, supprimer les colonnes dans ma table helicopteres qui reprennent en fait des données (nom_com, insee_com, code_dep) déjà présentes dans les autres tables communes et departements. Mais pourquoi finalement modifier la structure de ma table et la renommer!? Cette table hélico doit être disponible sur un viewer web lizmap et on doit pouvoir accéder à toutes les données attributaires de la table. Est ce que c'est pour permettre la jointure que vous mettez en oeuvre dans la fonction!? mais du coup, étant donné qu'on appelle une vue, qu'est que ça implique au niveau de la consultation des données attributaires dans la qgis ou lizmap? on aura bien une table attributaire complète avec tous les champs (notamment le nom et le code insee de la commune et le code du département)? désolé pour les questions un peu basiques mais je débute^^

- donc dans un deuxième temps, je crée ma vue qui reprend les données de la table hélicopteres à laquelle on associe les données d'autres tables par jointure spatiale via le st_within. Le "h.*" après le select c'est quoi !? une abréviation qui permet d'appeler tous les champs de la table hélicopteres (à part ceux qu'on a supprimé dans un premier temps évidemment)?

- la fonction ne fait appelle qu'à un insert... donc si on fait un update en déplaçant le point hélico créé sur la carte, la modification des données attributaires se fera ou pas ?

- et dernière question... comment se fait la transmission des données entre la vue hélicoptères et la table hélicopteres?
en fait j'essaie de m'expliquer : l'opérateur va créer un point sur le viewer web lizmap, ce point représente un hélicoptère de secours situé sur une commune, un formulaire s'ouvrira alors (comme dans qgis) et il devra saisir une dizaine d'informations attributaires à la main (je n'avais pas listé dans mon premier message tous les champs de la table hélicopteres qui ne seront pas tous automatisés) tout en ayant les 4 fameux champs préremplis (nom commune, insee commune, code département et date de la saisie). Une fois l'enregistrement validé, une ligne doit donc être créée dans la table hélicopteres qui doit pouvoir être consultable dans son intégralité sur qgis et lizmap et qui sera envoyée quotidiennement aux autorités compétetentes et qui permettra aussi de faire des analyses thématiques par communes et par départements. La vue permettra t-elle cela? A quoi servira ma table hélicoptere alors?

J'essaie de comprendre bien le processus avant de me lancer dans la mise en place de mes requêtes et fonctions. Désolé encore si je suis un peu lourd avec mes questions de néophyte et une nouvelle fois un grand merci pour votre aide, ça me rend déjà un fier service.

tumasgiu · Wed 04 October 2017 12:17

L'idée c'était d'éditer directement la vue et de rediriger les éditions dans la table via des triggers.

Mais en fait on pourrait simplifier.

Vous pourriez vous contenter d'une vue non modifiable (çàd sans les triggers de redirection vers la table),
qui joindra spatialement les tables helico, departement et commune,
qui vous servira à la consultation et à l'export et qui contiendra les champs calculés (préremplis pour reprendre vos termes).

L'opérateur lui utilisera la table hélicoptère pour créer des entités/mettre à jour les champs, il n'aura pas à se préoccuper
des champs pré-remplis qui n'apparaîtront plus dans le formulaire, mais qui seront bien disponibles dans la vue.

Sinon pour répondre à vos interrogations:

mais du coup, étant donné qu'on appelle une vue, qu'est que ça implique au niveau de la consultation des données attributaires dans la qgis ou lizmap? on aura bien une table attributaire complète avec tous les champs (notamment le nom et le code insee de la commune et le code du département)? désolé pour les questions un peu basiques mais je débute^^

Les vues se comportent de manière identique aux tables dans Qgis. Pour Lizmap je ne sais pas.

- la fonction ne fait appelle qu'à un insert... donc si on fait un update en déplaçant le point hélico créé sur la carte, la modification des données attributaires se fera ou pas ?

Elle ne se fera pas. Par contre rien n'empêche de créer un second couple de trigger/fonction qui s'occupera des modifications (idem pour les suppressions).

Le "h.*" après le select c'est quoi !? une abréviation qui permet d'appeler tous les champs de la table hélicopteres (à part ceux qu'on a supprimé dans un premier temps évidemment)?

Oui c'est un raccourci pour désigner tous les champs de la table h

Dernière modification par tumasgiu (Wed 04 October 2017 14:09)

raster31 · Wed 04 October 2017 20:23

tumasgiu a écrit:

L'idée c'était d'éditer directement la vue et de rediriger les éditions dans la table via des triggers.

Mais en fait on pourrait simplifier.

Vous pourriez vous contenter d'une vue non modifiable (çàd sans les triggers de redirection vers la table),
qui joindra spatialement les tables helico, departement et commune,
qui vous servira à la consultation et à l'export et qui contiendra les champs calculés (préremplis pour reprendre vos termes).

L'opérateur lui utilisera la table hélicoptère pour créer des entités/mettre à jour les champs, il n'aura pas à se préoccuper
des champs pré-remplis qui n'apparaîtront plus dans le formulaire, mais qui seront bien disponibles dans la vue.

Sinon pour répondre à vos interrogations:

mais du coup, étant donné qu'on appelle une vue, qu'est que ça implique au niveau de la consultation des données attributaires dans la qgis ou lizmap? on aura bien une table attributaire complète avec tous les champs (notamment le nom et le code insee de la commune et le code du département)? désolé pour les questions un peu basiques mais je débute^^

Les vues se comportent de manière identique aux tables dans Qgis. Pour Lizmap je ne sais pas.

- la fonction ne fait appelle qu'à un insert... donc si on fait un update en déplaçant le point hélico créé sur la carte, la modification des données attributaires se fera ou pas ?

Elle ne se fera pas. Par contre rien n'empêche de créer un second couple de trigger/fonction qui s'occupera des modifications (idem pour les suppressions).

Le "h.*" après le select c'est quoi !? une abréviation qui permet d'appeler tous les champs de la table hélicopteres (à part ceux qu'on a supprimé dans un premier temps évidemment)?

Oui c'est un raccourci pour désigner tous les champs de la table h

ok merci beaucoup pour votre aide, je suis arrivé à ce que je veux^^

il ne me manque que la date automatique (juste la date, pas l'heure) lors d'un insert ou d'un update.

Je dois aussi passer par un trigger j'imagine?

Peut-on me mettre sur la voie svp!?

Je vous remercie.

tumasgiu · Wed 04 October 2017 21:48

Vous pouvez plutot changer la définition de la colonne date_automatique :

Code:

ALTER TABLE engagements_helicopteres.helicopteres
ALTER date_automatique SET DEFAULT CURRENT_DATE;

Ainsi si lors d'un insert vous ne précisez pas la date
celle-ci prendra la date courante par défaut.

Pour la jointure des table de la vue, peut être devriez vous remplacer
les inner join par des left outer join, de façon à ce que vous n'ayez pas
d'hélicoptères qui "disparaissent" del la vue au cas leur points ne soient
contenue par aucune des géométries des tables département et commune.

Peut être aussi changer le prédicat st_within par st_coveredby, qui satisfera
le cas ou un point hélicoptère se situera sur la limite d'une géometrie.

Dernière modification par tumasgiu (Thu 05 October 2017 11:59)

raster31 · Fri 06 October 2017 00:03

Ma carte et la base de données sont prêtes, je vais pouvoir mettre tt ça en ligne.
Je vous remercie vivement pour votre réponse et les conseils.

Pascal Boulerie · Fri 06 October 2017 11:50

N'oubliez pas de récoler ou recoller les morceaux de réponses de ce fil avec ceux d'un autre là-bas s'ils y sont, le sujet initial en étant le même et même identique...
http://www.forumsig.org/showthread.php/ … post350522

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Tue 03 October 2017 15:59

trigger pour remplir 4 champs automatiquement lors d'un insert/update

#2 Wed 04 October 2017 00:30

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

Code:

Code:

Code:

Code:

#3 Wed 04 October 2017 11:08

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

Code:

Code:

Code:

Code:

#4 Wed 04 October 2017 12:17

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

#5 Wed 04 October 2017 20:23

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

#6 Wed 04 October 2017 21:48

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

Code:

#7 Fri 06 October 2017 00:03

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

#8 Fri 06 October 2017 11:50

Re: trigger pour remplir 4 champs automatiquement lors d'un insert/update

Pied de page des forums