Le portail francophone de la géomatique

BALDE · Tue 22 June 2021 14:44

Bonjour,
J'essaye en ce moment d'automatiser sous PostgreSQL le processus de typologie de l'historique des modification des données importés dans OpenStreetMap. La typologie de ces données se fait à l'aide du champs tags, lat(latitude) et lon(longitude). Le champ tags permet de voir les changements et ajouts et suppressions de tags réalisés entre les versions d'un même objet (nb: un objet peut avoir jusqu'à 8 versions). Quant aux champs lat et lon, ils permettent de détecter les changements de position d'un objet, et cela par la différence de coordonnées entre les versions du même objet.
Pour faire cette typologie, j'ai d'abord découpé le champ tags en plusieurs champs afin de mieux repérer les changements.
En revanche, je ne sais pas trop comment faire une fonction qui parcours ma table ligne par ligne en comparant les tags mais aussi les latitudes ou longitudes de chaque versions d'un objet à celle précédente et mettre dans mon champs typemodif "enrichissement" pour les ajouts de tags, "modification valeur" pour les modifications de valeur à l'intérieur d'un tags, "suppression" pour les suppressions, "deplacement" pour le changement de coordonnées d'un même objet, "enrichissement et modification valeur" lors qu'on observe à la fois un ou des enrichissement(s) et une modification de valeur et "enrichissement et deplacement" lors qu'il y a ajout de tag(s) et modification de coordonnées.

La structure de ma table (histpolice_donneevectimport) est comme suit :
-osm_id : identifiant de l'objet
- vnode : versions de modification de l'objet
- tags mais découper en 20 champs (1 tag par champ et par version) :
- lat : latitude
- lon : longitude
- typemodif : type de modification (mais vide pour le moment)

Je souhaiterais savoir si quelqu'un a déjà fait ce type de traitement ou a une idée sur la façon de procéder ?
Merci d'avance

Dernière modification par BALDE (Tue 22 June 2021 16:02)

tumasgiu · Tue 22 June 2021 15:53

Salut,

au lieu de découper le champs tags, j'opterais pour une colonne de données semi structurées,
style json ou hstore. De cette manière si jamais de nouveaux tags apparaissent, vous n'aurez
pas a rajouter de nouvelles colonnes.
Je découperais ma colonne modif en deux colonnes booléennes par contre : modif_pos et modif_tags

La requête donnerait quelque chose comme çà

Code:

INSERT INTO 
   histpolice_donneevectimport
SELECT 
   osm_id,  
   vnode+1, 
   d.lat <> l.lat  OR d.lon <> l.lon, 
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
     NATURAL LEFT JOIN
          each(l.tags)  AS b
     WHERE 
         COALESCE(a.value <> b.value, TRUE)
   ) 
FROM 
    osm_data d
CROSS JOIN LATERAL 
     (SELECT 
        *
      FROM
         histpolice_donneevectimport h
     WHERE 
         d.osm_id = d.osm_id
     ORDER BY 
         vnode DESC
     LIMIT 1
     ) AS l

Je ne garantis pas les performances sur un gros dataset par contre...

Dernière modification par tumasgiu (Tue 22 June 2021 16:03)

n314 · Tue 22 June 2021 16:51

Vous connaissez https://heigit.org/big-spatial-data-ana … en/ohsome/ je suppose ? Qui ne répond pas à votre besoin ?

Nicolas Ribot · Tue 22 June 2021 16:52

Oh nice !

BALDE · Wed 23 June 2021 10:51

Bonjour,
Merci pour vos retours !
Je vais essayer d'explorer vos suggestions. Quant à Ohsome, je l'ai consulté mais je n'ai pas trouver grand chose par rapport aux tâches que je souhaite réaliser.

BALDE · Wed 23 June 2021 17:26

Je viens de tester le programme qui marche mais sans le INSERT INTO c'est à dire à partir du select. En effet, ce programme renvoie hormis les champs de la table de d (osm_id, tags, vnode, lat, lon) trois autres champs. Les deux premiers champs ajoutés ont le même nom (?column?) mais le premier est de type integer (numéro de la version où il y a un changement si j'ai bien compris) alors que les deuxième et troisième sont de type booléen. Les résultats de ces deux dernières colonnes sont soit vrai (True), soit faux (False). J'arrive donc toujours pas à faire ma typologie des modification à travers ce résultat.

Voici ci-dessous mon script :

Code:

INSERT INTO 
   testhist_police
SELECT 
   d.osm_id, d.vnode, d.tags, d.lat, d.lon,  
   d.vnode+1, 
   d.lat <> l.lat  OR d.lon <> l.lon, 
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
     NATURAL LEFT JOIN
          each(l.tags)  AS b
     WHERE 
          COALESCE(a.value <> b.value, TRUE)
   ) 
FROM 
    histpolice_donneevectimport d 
CROSS JOIN LATERAL 
     (SELECT 
        *
      FROM
         testhist_police h
     WHERE 
         d.osm_id = d.osm_id
     ORDER BY 
         vnode DESC
     LIMIT 1
     ) AS l

Y aurait-il un moyen d'améliorer mon script afin d'obtenir une des deux typologies de modification que j'ai réalisées manuellement sous Excel (cf. fichier ci-joint) ?

Dernière modification par BALDE (Wed 23 June 2021 17:59)

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

tumasgiu · Thu 24 June 2021 09:31

Voici la requête modifiée qui prends en compte votre besoin de vraiment détailler
les différences entre les tags (plus corrections de coquilles).

J'ai tout de même conservé des types booleens pour votre typologie, je pense
que cela est plus pertinent car plus simple à manipuler.

Si vous tenez à avoir une représentation textuelle, vous pouvez créer une vue qui exploitera cette table
et utiliser ces champs booléens dans une expression CASE.

Code:

SELECT 
   d.osm_id, d.vnode, d.tags, d.lat, d.lon,  
   d.vnode+1, 
   d.lat <> l.lat  OR d.lon <> l.lon AS deplacement, 
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
     JOIN
          each(l.tags)  AS b
    USING (key)
     WHERE 
          a.value <> b.value
   )  AS modifications_tags,
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
      LEFT JOIN
          each(l.tags)  AS b
    USING (key)
     WHERE 
          b.value IS NULL
   )  AS enrichissement_tags,
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
      RIGHT JOIN
          each(l.tags)  AS b
     USING (key)
     WHERE 
          a.value IS NULL
   ) AS suppression_tags
   
FROM 
    histpolice_donneevectimport d 
    
CROSS JOIN LATERAL 
     (SELECT 
        *
      FROM
         testhist_police h
     WHERE 
         d.osm_id = h.osm_id
     ORDER BY 
         vnode DESC
     LIMIT 1
     ) AS l

La clause INSERT ne marchera que quand votre table testhist_police contiendra les mêmes colonnes que la clause SELECT.

Dernière modification par tumasgiu (Thu 24 June 2021 09:45)

BALDE · Thu 24 June 2021 11:31

Parfait, c'est ce dont j'avais besoin !
Je vous remercie pour cette immense aide.

Dernière modification par BALDE (Thu 24 June 2021 11:38)

tumasgiu · Thu 24 June 2021 20:26

ultime coquille, les noms des deux dernieres expressions sont à inverser :

Code:

SELECT 
   d.osm_id, d.vnode, d.tags, d.lat, d.lon,  
   d.vnode+1, 
   d.lat <> l.lat  OR d.lon <> l.lon AS deplacement, 
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
     JOIN
          each(l.tags)  AS b
    USING (key)
     WHERE 
          a.value <> b.value
   )  AS modifications_tags,
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
      LEFT JOIN
          each(l.tags)  AS b
    USING (key)
     WHERE 
          b.value IS NULL
   )  AS suppressions_tags,
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
      RIGHT JOIN
          each(l.tags)  AS b
     USING (key)
     WHERE 
          a.value IS NULL
   ) AS enrichissement_tags
   
FROM 
    histpolice_donneevectimport d 
    
CROSS JOIN LATERAL 
     (SELECT 
        *
      FROM
         testhist_police h
     WHERE 
         d.osm_id = h.osm_id
     ORDER BY 
         vnode DESC
     LIMIT 1
     ) AS l

BALDE · Tue 29 June 2021 12:08

Oui, Je m'en suis rendu compte en vérifiant les résultats.
Merci pour la remarque !

BALDE · Fri 02 July 2021 12:32

Bonjour Tumasgiu,

Est-il possible de réaliser ce traitement sur une seule table ? J'ai toujours des erreurs dans mes résultats. En effet, j'ai quelques objets détectés comme étant des enrichissements, déplacements, modifications ou suppressions alors qu'ils ne le sont pas. Par exemple entre la première et deuxième version du premier objet, ça me met enrichissement_tags = true alors que c'est une suppression (cf. capture d'écran).
Je me suis dit qu'il y a peut-être un problème de correspondance entre les deux tables or que c'est la même table qui est dupliquée et ordonnée en fonction de l'osm_id et du vnode.
Merci d'avance

Dernière modification par BALDE (Fri 02 July 2021 12:35)

Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

tumasgiu · Fri 02 July 2021 17:17

Oui en principe c'est faisable, mais je ne cerne pas très bien votre cas d'utilisation.

Pourriez vous poster la requête que vous executez ?

BALDE · Sun 04 July 2021 16:04

Voici ci-dessous, la requête complète

Code:

CREATE TABLE typomodifpolice AS (
SELECT 
   d.osm_id, d.vnode, d.tags, d.lat, d.lon, 
   d.vnode+1 AS v_next, 
   d.lat <> l.lat  OR d.lon <> l.lon and d.osm_id = l.osm_id AS deplacement,
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
     JOIN
          each(l.tags)  AS b
    USING (key)
     WHERE 
          a.value <> b.value
   )  AS modifications_tags,
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
      LEFT JOIN
          each(l.tags)  AS b
    USING (key)
     WHERE 
          b.value IS NULL
   )  AS suppressions_tags,
   
   EXISTS(
     SELECT 
          1
     FROM 
          each(d.tags) AS a
      RIGHT JOIN
          each(l.tags)  AS b
     USING (key)
     WHERE 
          a.value IS NULL
   ) AS enrichissement_tags
   
FROM 
    histpolice_donneevectimport d 
CROSS JOIN LATERAL 
     (SELECT 
        *
      FROM
         testhist_police h 
     WHERE 
         d.osm_id = h.osm_id
     ORDER BY 
         osm_id, vnode DESC
     LIMIT 1
     ) AS l)
-- Création du champ de typologie     
ALTER TABLE typomodifpolice ADD COLUMN typologie VARCHAR (100)
   -- Remplissage du champ créé
UPDATE typomodifpolice 
SET typologie = (
    CASE 
        WHEN enrichissement_tags = 'true'
            THEN 'enrichissment'
        WHEN enrichissement_tags = 'true' AND deplacement = 'true'
            THEN 'enrichissment_deplacement'
        WHEN deplacement = 'true' 
            THEN 'deplacement'
        WHEN modifications_tags = 'true'
            THEN 'modification valeur'
        WHEN suppressions_tags = 'true' 
            THEN 'suppression'
        WHEN suppressions_tags = 'true' AND enrichissement_tags = 'true'
            THEN 'enrichissement_suppressions'

     ELSE 'Pas de modification'
     END)

.

BALDE · Mon 05 July 2021 15:06

Bonjour @tumasgiu,
J'ai finalement résolu le problème avec l'aide de mon maître de stage.
Merci beaucoup pour ton assistance !
Cordialement

Dernière modification par BALDE (Mon 05 July 2021 15:06)

tumasgiu · Mon 05 July 2021 19:26

Super, si vous pouvez poster la solution ici, çà serait génial

BALDE · Tue 06 July 2021 11:12

voici la solution :

Code:

DROP TABLE IF EXISTS typomodifpolice;

CREATE TABLE typomodifpolice AS (

SELECT

   d.osm_id, d.vnode, d.tags, d.geom_hist,--d.lat, d.lon,

   d.vnode+1 AS v_next,

   CASE WHEN ST_Equals(d.geom_hist, l.geom_hist) THEN 'no geom change' 
    ELSE 'geom change' end as change_geom,

   EXISTS(SELECT 1 FROM each(d.tags) AS a JOIN 
          each(l.tags)  AS b USING (key) 
          WHERE a.value <> b.value)  AS modification_tags,

   EXISTS(SELECT 1 FROM each(d.tags) AS a LEFT JOIN 
          each(l.tags)  AS b USING (key) 
          WHERE b.value IS NULL)  AS enrichissement_tags,

   EXISTS(SELECT 1 FROM each(d.tags) AS a RIGHT JOIN 
          each(l.tags)  AS b USING (key) 
          WHERE a.value IS NULL)  AS suppression_tags

FROM histpolice_donneevectimport d

CROSS JOIN LATERAL (
    SELECT * FROM histpolice_donneevectimport h 
                    WHERE d.osm_id = h.osm_id and 
                    d.vnode=h.vnode+1 LIMIT 1) AS l)
                    
 
--on insère la version initiale de l'objet car la requête précedente donne que les versions après la version initiale;

INSERT INTO typomodifpolice  SELECT 

osm_id, vnode, tags, geom_hist,

vnode+1, 'first version', false, false, false

FROM histpolice_donneevectimport where vnode=1;

tumasgiu · Tue 06 July 2021 16:55

Merci !

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

#1 Tue 22 June 2021 14:44

Automatisation de la typologie des modification de l'historique d'OSM

#2 Tue 22 June 2021 15:53

Re: Automatisation de la typologie des modification de l'historique d'OSM

Code:

#3 Tue 22 June 2021 16:51

Re: Automatisation de la typologie des modification de l'historique d'OSM

#4 Tue 22 June 2021 16:52

Re: Automatisation de la typologie des modification de l'historique d'OSM

#5 Wed 23 June 2021 10:51

Re: Automatisation de la typologie des modification de l'historique d'OSM

#6 Wed 23 June 2021 17:26

Re: Automatisation de la typologie des modification de l'historique d'OSM

Code:

#7 Thu 24 June 2021 09:31

Re: Automatisation de la typologie des modification de l'historique d'OSM

Code:

#8 Thu 24 June 2021 11:31

Re: Automatisation de la typologie des modification de l'historique d'OSM

#9 Thu 24 June 2021 20:26

Re: Automatisation de la typologie des modification de l'historique d'OSM

Code:

#10 Tue 29 June 2021 12:08

Re: Automatisation de la typologie des modification de l'historique d'OSM

#11 Fri 02 July 2021 12:32

Re: Automatisation de la typologie des modification de l'historique d'OSM

#12 Fri 02 July 2021 17:17

Re: Automatisation de la typologie des modification de l'historique d'OSM

#13 Sun 04 July 2021 16:04

Re: Automatisation de la typologie des modification de l'historique d'OSM

Code:

#14 Mon 05 July 2021 15:06

Re: Automatisation de la typologie des modification de l'historique d'OSM

#15 Mon 05 July 2021 19:26

Re: Automatisation de la typologie des modification de l'historique d'OSM

#16 Tue 06 July 2021 11:12

Re: Automatisation de la typologie des modification de l'historique d'OSM

Code:

#17 Tue 06 July 2021 16:55

Re: Automatisation de la typologie des modification de l'historique d'OSM

Pied de page des forums