22 Jun
2021
22 Jun
'21
2:44 p.m.
Message posté par : BALDE (bailobalde2018(a)gmail.com)
----------------------------------------
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écouper le champ tags en plusieurs
champs.
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
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344347#p344347
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
22 Jun
22 Jun
3:53 p.m.
Message posté par : T. Rossini
----------------------------------------
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 b.value IS NULL OR a.value <> b.value,
)
FROM
osm_data d
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...
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344360#p344360
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
4:52 p.m.
Message posté par : Nicolas Ribot
----------------------------------------
Oh nice !
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344363#p344363
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
23 Jun
23 Jun
5:26 p.m.
Message posté par : BALDE
----------------------------------------
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 mon script :
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
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344406#p344406
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
24 Jun
24 Jun
11:31 a.m.
Message posté par : BALDE
----------------------------------------
Parfait, c'est ce dont j'avais besoin !
Je te remercie pour cette immense aide et te souhaite le meilleur.
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344430#p344430
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
29 Jun
29 Jun
12:08 p.m.
Message posté par : BALDE
----------------------------------------
Oui, Je m'en suis rendu compte en vérifiant les résultats.
Merci pour la remarque !
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344588#p344588
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
2 Jul
2 Jul
5:17 p.m.
Message posté par : T. Rossini
----------------------------------------
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 ?
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344713#p344713
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
5 Jul
5 Jul
3:06 p.m.
Message posté par : BALDE
----------------------------------------
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
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344737#p344737
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
6 Jul
6 Jul
11:12 a.m.
Message posté par : BALDE
----------------------------------------
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;
-----------------
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344764#p344764
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
22 Jun
22 Jun
4:51 p.m.
Message posté par : n314
----------------------------------------
Vous connaissez https://heigit.org/big-spatial-data-analytics-en/ohsome/ je suppose ? Qui
ne répond pas à votre besoin ?
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344362#p344362
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
23 Jun
23 Jun
10:51 a.m.
Message posté par : BALDE
----------------------------------------
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.
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344388#p344388
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
24 Jun
24 Jun
9:31 a.m.
Message posté par : T. Rossini
----------------------------------------
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éees 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.
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344419#p344419
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
8:26 p.m.
Message posté par : T. Rossini
----------------------------------------
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
-----------------
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344464#p344464
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
2 Jul
2 Jul
12:32 p.m.
Message posté par : BALDE
----------------------------------------
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, modification ou suppression alors qu'ils ne le sont
pas. Je me suis dit qu'il y a peut-être un problème de correspondance entre les deux
tables or que la même table qui est dupliquée et ordonnée en fonction de l'osm_id et
du vnode. 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.
Merci d'avance
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344698#p344698
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
4 Jul
4 Jul
4:04 p.m.
Message posté par : BALDE
----------------------------------------
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)
-----------------
5 Jul
5 Jul
7:26 p.m.
Message posté par : T. Rossini
----------------------------------------
Super, si vous pouvez poster la solution ici, çà serait génial :)
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344745#p344745
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
6 Jul
6 Jul
4:55 p.m.
Message posté par : T. Rossini
----------------------------------------
Merci !
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=344773#p344773
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net
1558
days inactive
1572
days old
16 comments
1 participants
participants (1)
-
geobd＠georezo.net