Ce problème est remonté par QGIS avec le message d’erreur suivant : ERROR: cannot perform INSERT RETURNING on relation xxx
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
CREATE TRIGGER saisie_habitat_ON_INSERT
INSTEAD OF INSERT ON habitats_naturels.saisie_habitats_avec_ref
FOR EACH ROW EXECUTE PROCEDURE habitats_naturels.saisie_habitat_insert();
Un collègue bien embarrassé nous a posé la question suivante : comment sélectionner tous les points d’une couche A qui se trouvent à moins de 4km d’une autoroute.
Le postulat de départ est que nous travaillons sur des fichiers shp (donc on oublie tout de suite la requête sql et l’opérateur st_dwithin() )
Nous avons tous répondu « tu crées un tampon de 4km de rayon autour de l’autoroute puis tu fais une recherche par localisation de tous les objets de la couche de points qui intersectent le tampon créé« .
Il nous a répondu « ok ça je sais le faire mais la personne qui m’a posé cette question m’a tendu un piège pour me dire que c’était fou de devoir passer par un tampon, alors que d’autre outils font ça en un clic…« .
L’objectif sous-jacent et l’effet attendu auprés de l’assistance -non « géomaticienne »- semble être de vouloir déprécier QGIS.
Considérons donc que cette personne n’est pas fan de SQL et qu’elle affectionne le format shp.
Tout d’abord, quel problème y-a-t-il à devoir créer un tampon ?
Aucun… Si les géomaticiens en avaient assez de créer des fichiers à tout va, tous travailleraient en SQL dans une base de données spatiales. Par ailleurs QGIS permet de générer le tampon en mémoire, sans passer par l’écriture d’un fichier sur le disque. Le souci n’est donc pas là.
Peut-être réside-t-il dans le fait de ne pas disposer d’un clique-bouton pour faire le travail et de devoir passer par deux étapes ?
Une des qualités de tout bon géomaticien n’est-elle pas de savoir résoudre un problème qui se pose à lui, avec les outils dont il dispose. Si l’absence de bouton pour faire le travail est un obstacle rédibitoire, changeons tout de suite de métier.
Mais qu’à cela ne tienne. Il me faut un clique-bouton donc je le crée !
Modeleur graphique et boite à outils « traitements »
Le modèle de traitement qui résulte de cette opération est accessible ici en téléchargement.
L’interface produite est la suivante :
Sélection par expression
Une autre possibilité offerte part QGIS réside dans l’utilisation d’expressions pour la sélection.
Je peux donc sélectionner dans une couche de points, tous les objets dont la géometrie est à une certaine distance d’un objet d’une autre couche (ici une ligne).
Présentation réalisée et enregistrée dans le cadre du séminaire « Système d’information embarqué, cahier/carnet de terrain et de laboratoire électronique : quelles interactions avec les bases de données ? »
le Mercredi 05 octobre 2016 à Paris – Jussieu (amphi Charpak).
Cliquez sur l’image ci-dessous pour accéder à la vidéo.
Je partage ici un trés gros travail réalisé par les collègues géomaticiens du CEN Picardie (Jérôme Boutet, Marie Héraude, et Gratien Testud) qui ont actualisé le document « Aide-mémoire SIG et Quantum GIS Lisboa 1.8 », réalisé en 2013 par Guillaume Doucet du CEN Bourgogne.
Les démarches présentées sont généralisables et répétables.
La première présentation concerne des cas d’utilisation d’outils de « reporting » qui croisent connaissance naturaliste et données foncière, pour informer un acquéreur ou un vendeur sur les enjeux connus sur les parcelles concernées ou plus classiquement pour informer un propriétaire de l’intérêt patrimonial de sa propriété :
Chaque version de ligne de la table saisie.saisie_observation y est datée (date_operation). La fenêtre est une partition réalisée selon id_obs et ordonnée par date_operation. Les fonctions window sont expliquées ici dans la doc de postgresql :
Suite aux discussions des journées utilisateurs de QGIS des 10 et 11 décembre. J’ai testé l’utilisation d’une table atributaire comme couche de couverture. Et ça fonctionne !
Je ne sais pas si c’était le cas sur les versions précédentes mais ça fonctionne sur la 2.12. Ma table ne contient que deux colonnes cd_ref et lb_nom.
On utilisera les règles de symbologie pour n’afficher que les données correspondant au cd_ref courant :
Voici la présentation faite à l’occasion du séminaire utilisateur de QGIS des 10 et 11 décembre dernier.
Elle a été réalisée avec Inkscape et SOZI. Utilisez les boutons de la souris ou les flèches de votre clavier pour avancer/reculer. La molette ou les signes +/- pour zoomer/dézoomer.
Cette page est le résultat d’un travail mené en commun, en mars 2015 par 4 géomaticiens et informaticiens des CEN Rhône-Alpes (Rémy Clément, Guillaume Costes et Laurent Poulin) et Languedoc-Roussillon (Mathieu Bossaert)
Elle a été actualisée le 17 mai 2018.
OpenDataKit est une suite d’outils libres dédiée à la collecte de données sur terminaux mobiles Androïd.
D’une relative simplicité de mise en oeuvre, la solution permet facilement de décrire et créer des formulaires correspondant à nos besoins. Une fois les données récupérées, il est simple de les intégrer à notre base de données en place.
Nous allons donc passer en revue l’installation des outils de la suite, la définition du formulaire avec XLSForm, et la ventilation des données récoltées dans notre base de données « métier », SiCen.
Présentation générale
ODK est un générateur de formulaires Open Source pour Android. Il permet de collecter des données en mode déconnecté. Les données sont envoyées quand une connexion est disponible, ou par upload de fichiers.
Les formulaires sont créés de manière simple, en utilisant un outil dédié (ODKBuild) ou en les décrivant dans un fichier excel avec le standard XLSForm
Tous les types de données sont disponibles et disposent de « widgets » adaptés : dates, textes, nombres, booléens, geo. Tous les médias que peut créer votre appareil androïd peuvent être attachés à l’observation eux aussi : son, vidéo, photo.
Il est possible d’interroger de longs référentiels (ex. TAXREF), fournis en csv avec le formulaire.
« XLSForm est une norme de formulaires créée pour aider à simplifier la création de formulaires dans Excel ». C’est dans un tableur que nous allons décrire de manière simple la strcuture et la logique du formulaire.
Installée depuis le dépot des applications de google, elle va se connecter au serveur « Aggregate », récupérer et proposer au téléchargement la liste des formulaires disponibles. Puis envoyer à « Aggregate » les données collectées.
Installé au sein d’un réseau interne, Aggregate peut-être associé à MySQL ou PostgreSQL. Dans notre cas, il sera asoocié à PostGIS et stockera ses données dans le schéma « odk« de notre base de données « SiCen« .
Mise en œuvre au sein de l’intranet
Installation d’aggregate
Elle peut se faire simplement en utilisant une machine virtuelle, diffusée à prix libre depuis cet été : https://gum.co/odk-aggregate-vm
Dans notre cas, nous allons procéder au déploiement d’une application java sur un serveur Tomcat.
Il s’agit en fait d’un exécutable qui va générer l’archive .war et le script SQL de création de la base de données (base de données, utilisateur odk et schema), conformément aux paramètres renseignés. Les tables sont créées au lancement du war et à l’ajout de nouveaux formulaires
On exécute sur le serveur de base de données les commandes SQL générées. Elles vont permettre de créer les tables et autres objets de base de données nécessaires à Aggregate pour fonctionner.
Puis on déploie l’applicatiopn depuis l’interface de tomcat-manager ou en déplaçant le war dans le dossier webapps de tomcat
Mise en œuvre du formulaire
Nous avions le projet de réaliser une application mobile complète dédiée à l’outil WEB SiCen, permettant de saisir nos observations sur un terminal Android pour les retrouver dans l’interface web de SICEN, sans intervention de l’observateur, autre que d’envoyer les données du formulaire au serveur.
Besoin simple
Il nous faut collecter des données d’observations d’espèces, localisées. Elles sont collectées dans le cadre d’une étude et selon un protocole particuliers
-> une étude, un protocole, des localités sur lesquelles on observe des espèces
Un formulaire GeoODK
→ conçu avec XLSForm
Le fichier excel décrivant le formulaire ainsi que les ressources csv nécessaires à son fonctionnement sont présents dans l’archive ci-jointe : demo_aten.zip
Trucs et astuces
Pour éviter la demande d’ajout de groupe, lors des « repeat » : Supprimer dans le xml le jr template il est présent au début du xml dans la liste des balises champs du début
Possibilité de cumuler la fonction quick + search dans la colonne « appearance » du fichier excel des formulaires avec la fonction quick search(…)
Le widget date avec calendrier n’est pas adapté : mettre dans la colonne « appearance » no-calendar
Génération des noms de formulaire automatiquement en rajoutant une colonne « instance_name » dans l’onglet « settings » du fichier excel. En utilisant un champ calculé.
Des référentiels en csv générés depuis la base de données
ODK permet désormais d’associer au formulaire de grosses listes de références dans des fichiers csv. Elles seront diffusées à ODKCollect avec le formulaire. Ce dernier les transformera en base de données locale sqlite.
La fonction search() permet de filtrer les entrées proposées, à celle contenant la chaîne de caractèressaisie par l’utilisateur. Nous utiliserons cette possibilité pour la gestion des listes de choix relatives aux :
taxons observés
observateurs et structures
études et protocoles
Voici une démonstration du résultat
Création du fond de carte
L’application permet non seulement de créer tout type d’objet mais également d’embarquer sur le terminal des fonds cartographiques en local (pas de réseau nécessaire).
Les fonds doivent être au format mbtiles. Ils peuvent être généré avec TileMill, comme décrit dans la doc de GeoODK : http://geoodk.com/mbtiles_howto.php, ou d’autres outils.
Dans notre cas, nous utiliserons MOBAC parceque nous l’utilisons déjà. Par contre, le mbtile produit par MOBAC nécessitera une petite mabnipulation :
Ouvrir le fichier avec un sqlite manager et exécuter la requête suivante :
CREATE VIEW images as SELECT ROWID, « tile_data », tile_row as « tile_id » FROM « tiles » ORDER BY ROWID
Le fichier MBTiles correspondant aux fonds cartographiques doit ensuite être placé dans un sous dossier du dossier OfflineLayers : OfflineLayersSous_Dossier_contenant_Mbtiles.
Ventilation des données dans la base «métier»
Création d’une vue qui met les données au format propre à notre table de destination (saisie.saisi_obsevration)
Voilà donc nos données envoyées au serveur Aggregate, directement visibles et modifiables dans SiCen !
Conclusion / Bilan
Améliorations récentes
→ utilisation de gros référentiels + widget cartographique
Facilité de mise en œuvre de la solution
→ appli Android + déploiement WAR / Machine virtuelle
Souplesse / facilité de création de formulaires de saisie
→ Par des collègues non géomaticien / montée en compétence rapide
Intégration aisée au SI en place dans la structure
→ En utilisant les outils standards de notre base de données (vues et triggers)
Chaque donnée intégrée à la base de données de l’atlas doit être examinée (validée / invalidée). Afin de faciliter le travail de validation, les fonctions présentées ici, permettent de passer chaque donnée saisie au crible des connaissances actuelles sur l’espèce, issues de la base de données.
Chaque taxon a tout d’abord été « caractérisé » selon les connaissances actuelles mobilisables dans la base de données (données validées).
Pour chaque espèce ont donc été calculées les références suivantes :
liste des communes
liste des entités et des ensembles paysagers
lises des semaines et des décades d’observation
listes des observateurs ayant déjà une donnée validée pour ce taxon
la répartition altitudinale (plages de 100 m d’altitude, correspondant à la division entière de l’altitude par 100)
Pour chaque nouvelle donnée, ou à chaque modification d’une donnée, les valeurs saisies vont être confrontés à la grille précédemment calculée. C’est un trigger qui déclenche cette confrontation de la donnée saisie aux valeurs de référence pour le taxon
La fonction de comparaison qui est appelée par le trigger est la suivante :
Un « score » est affiché dans le champ « décision validation » afin de permettre aux validateurs de filtrer les données selon ce score.
Dés qu’une donnée est validée, la grille est recalculée pour le taxon concerné. Si une donnée anciennement validée est invalidée, les valeurs de référence pour le taxon sont aussi recalculées.
Au niveau de la base de données, c’est encore un trigger qui déclenche la mise à jour de la ligne de la grille relative au taxon mentionné dans la donnée.
Il appelle cette fonction, qui va supprimer la ligne de référence pour le taxon concerné et la réinsérer en tenant compte des nouvelles données validées :
Cette démonstration s’arrête au moment de l’envoi des données collectées au serveur, et de la démonstration de l’intégration automatique et instantanée des données à SiCen.
Après avoir téléchargé les deux fichiers csv, on les intègre au schéma inpn de notre base de données sous les noms inpn.protection_especes et inpn.protection_especes_types
Afin de pouvoir exploiter pleinement cette donnée, nous allons créer une matrice contenant une ligne par taxon, et une colonne par statut. Si l’espèce est concernée par le statut, l’article est mentionné.
Selon la version de postgresql dont on dispose (9.3+ ou inférieure à 9.3), nous allons créer une vue matérialisée ou une table.
La vue matérialisée permettra une mise à jour future de la matrice des protections sans avoir à supprimer, recréer et réindexer cette matrice.
Pour le congrès de Dunkerque d’octobre 2015 et le travail d’analyse de la contribution des CEN à la connaissance naturaliste, nous avons entrepris de faire remonter les deonnées du réesau au format « DEE » du SINP.
Ce travail nous a permis de consolider prés de 2000000 de données.
Voici la rquête SQL qui nous a peris cette mise en forme depuis la base sicen.
SELECT row_number() OVER (ORDER BY (lpad(saisie_observation.id_obs::text, 7, '0'::text))) AS gid,
CASE saisie_observation.determination::text
WHEN 'Vu'::text THEN 'te'::text
WHEN 'Entendu'::text THEN 'te'::text
WHEN 'Indice de présence'::text THEN 'te'::text
WHEN 'Cadavre'::text THEN 'te'::text
WHEN 'Capture'::text THEN 'te'::text
WHEN 'Collection'::text THEN 'Co'::text
WHEN 'Littérature'::text THEN 'Li'::text
ELSE 'te'::text
END AS statutsource,
CASE saisie_observation.determination::text
WHEN 'Littérature'::text THEN 'à préciser'::text
ELSE NULL::text
END AS referencebiblio,
lpad(lot_donnee.id_lot::text, 4, '0'::text) AS jddid,
'SICEN-LR:00-175'::text AS jddcode,
lpad(saisie_observation.id_obs::text, 7, '0'::text) AS identifiantorigine,
NULL::text AS identifiantpermanent,
CASE
WHEN lot_donnee.libelle::text ~~* 'bénévolat%'::text THEN 'Pr'::text
WHEN saisie_observation.id_etude = ANY (ARRAY[39,
51]) THEN 'NSP'::text
ELSE 'Ac'::text
END AS dspublique,
NULL::text AS codeidcnpdispositif,
'CEN L-R'::text AS organismestandard,
CASE
WHEN lower(saisie_observation.type_effectif) = 'absence'::text THEN 'No'::text
ELSE 'Pr'::text
END AS statutobservation,
btrim(concat(saisie_observation.nom_vern, ' / ', saisie_observation.nom_complet), ' / '::text) AS nomcite,
saisie_observation.cd_nom::integer AS cdnom,
taxref.cd_ref::integer AS cdref,
'non'::text AS sensible,
COALESCE(saisie_observation.effectif, saisie_observation.effectif_min, 1::bigint)::integer AS denombrementmin,
COALESCE(saisie_observation.effectif, saisie_observation.effectif_max)::integer AS denombrementmax,
CASE
WHEN saisie_observation.type_effectif ~~* ANY (ARRAY['abondance%'::text,
'classe%'::text]) THEN 'Es'::text
ELSE 'Co'::text
END AS typedenombrement,
CASE
WHEN saisie_observation.type_effectif IS NOT NULL THEN 'In'::text
ELSE NULL::text
END AS objetdenombrement,
CASE
WHEN saisie_observation.observateur = '20'::text THEN 'NSP'::text
ELSE md.liste_nom_auteur(saisie_observation.observateur, ', '::text)
END AS identiteobservateur,
CASE md.liste_nom_structure(saisie_observation.structure, ', '::text)
WHEN 'Pas de structure'::text THEN 'indépendant'::text
ELSE md.liste_nom_structure(saisie_observation.structure, ', '::text)
END AS organismeobservateur,
'CEN L-R'::text AS organismegestionnairedonnees,
CASE
WHEN saisie_observation.observateur <> '20'::text THEN md.liste_nom_auteur(saisie_observation.observateur, ', '::text)
ELSE NULL::text
END AS determinateur,
btrim(concat(validateur.nom, ' ', validateur.prenom)) AS validateur,
saisie_observation.remarque_obs AS commentaire,
COALESCE(saisie_observation.date_obs, saisie_observation.date_debut_obs) AS datedebut,
COALESCE(saisie_observation.date_obs, saisie_observation.date_fin_obs) AS datefin,
NULL::TIME WITHOUT TIME ZONE AS heuredebut,
NULL::TIME WITHOUT TIME ZONE AS heurefin,
NULL::date AS datedeterminationobs,
saisie_observation.elevation::numeric AS altitudemin,
saisie_observation.elevation::numeric AS altitudemax,
NULL::numeric AS profondeurmin,
NULL::numeric AS profondeurmax,
NULL::text AS codehabitat,
NULL::text AS refhabitat,
st_asgml(saisie_observation.geometrie) AS geometrie,
CASE saisie_observation."precision"
WHEN 'GPS'::saisie.enum_precision THEN 10
WHEN '0 à 10m'::saisie.enum_precision THEN 10
WHEN '10 à 100m'::saisie.enum_precision THEN 100
WHEN '100 à 500m'::saisie.enum_precision THEN 500
ELSE 1000
END AS "precision",
CASE
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POINT%'::text THEN 'St'::text
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%POLYGON%'::text
OR st_geometrytype(saisie_observation.geometrie) ~~* '%LINE%'::text THEN 'In'::text
ELSE 'NSP'::text
END AS natureobjetgeo,
COALESCE(saisie_observation.code_insee, commune.code_insee::text) AS codecommune,
commune.nom AS nomcommune,
sites_cen_inpn_2014.id_mnhn::text AS codeen,
NULL::text AS typeen,
NULL::text AS codemaille,
st_x(st_centroid(saisie_observation.geometrie)) AS st_x,
st_y(st_centroid(saisie_observation.geometrie)) AS st_y
FROM saisie.saisie_observation
JOIN inpn.taxref_v8 taxref USING (cd_nom)
JOIN ign_bd_topo.commune ON st_intersects(commune.geometrie, saisie_observation.geometrie)
LEFT JOIN referentiels_divers.sites_cen_inpn_2014 ON st_intersects(sites_cen_inpn_2014.geometrie, saisie_observation.geometrie)
LEFT JOIN md.lot_donnee ON saisie_observation.id_etude = lot_donnee.id_etude
AND saisie_observation.id_protocole = lot_donnee.id_protocole
AND ((CASE
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Point%'::text THEN 'point'::text
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%LineString%'::text THEN 'ligne'::text
WHEN st_geometrytype(saisie_observation.geometrie) ~~* '%Polygon%'::text THEN 'perimetre'::text
ELSE NULL::text
END || '_'::text) || CASE saisie_observation.regne
WHEN 'Plantae'::text THEN 'espece'::text
WHEN 'Animalia'::text THEN 'espece'::text
WHEN 'Fungi'::text THEN 'espece'::text
WHEN 'Habitat'::text THEN 'habitat'::text
ELSE NULL::text
END) = lot_donnee.type_donnee::text
LEFT JOIN md.personne validateur ON saisie_observation.validateur = validateur.id_personne
WHERE taxref.cd_nom::text ~ '^[\d+]'::text
AND (saisie_observation.regne = ANY (ARRAY['Animalia'::text,'Plantae'::text,'Fungi'::text]))
AND (md.liste_nom_structure(saisie_observation.structure, ', '::text) = ANY (ARRAY['CEN LR'::text,'Pas de structure'::text]))
AND saisie_observation.cd_nom <> '000000'::text;
Dans notre cas, nous allons procéder au déploiement d’une application java sur un serveur Tomcat.
Besoin simple