4 Jul
2021
4 Jul
'21
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)
-----------------