Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
J’ai besoin d’aide sur une requête SQL :
La requête crée une vue qui vient créer des unités foncières. Ces unités foncières regroupent les parcelles cadastrales possédant le même propriétaire qui sont côtes à côtes dans chaque zone d’activité économique (zae).
L’image suivante montre en hachuré bleu les parcelles et en dessous en marrons avec les gros traits noirs les unités foncières formées par ces parcelles.
J’ai notamment besoin pour chaque unité foncière de lui associer un code distinct, ce code est constitué du numero de la zae auquel appartient l’unité foncière puis d’un numéro unique.
Pour ça j’utilise la concaténation suivante :
concat(lpad(zae_test.numero_zae::text, 2, '0'::text), '-', lpad(row_number() OVER (PARTITION BY zae_test.numero_zae)::text, 3, '0'::text)) AS code,
Le problème que je rencontre est que quand je modifie la géométrie d’une zae, ce code va se regénérer automatiquement et même pour des unités foncières non présentes dans la zae modifiée. Sur les deux images de la vue avant et après update, c’est la zae ‘test_3’ qui est modifiée.
Les codes des unités foncières issues de la zae ‘test_3’ seront modifiés mais aussi ceux de la zae ‘test_1’ alors que la géométrie n’a pas été modifiée. Pourtant les codes des unités foncières issus de la zae ‘test_2’ n’auront eux pas changés.
Table zae_test
id|nom |wkb_geometry |numero_zae|
--+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
1|Test_1|POLYGON ((356671.93 6602155.66, 356703.862701489 6602141.220630944, 356729.59562072833 6602200.777748152, 356713.94 6602213.12, 356700.84 6602223.45, 356553.55 6602339.73, 356551.16 6602335.56, 356528.49 6602296.02, 356523.52 6602287.34, 356503.75 6602252| 1|
2|Test_2|POLYGON ((363040.6 6611270.57, 363033.64 6611277.32, 363023.51 6611285.82, 363008.41 6611298.24, 362975.96 6611322.8, 362980.14 6611331.5, 362984.31 6611340.18, 362991.77 6611352.66, 362994.7 6611355.54, 363001.4 6611362.12, 363010.01 6611366.59, 363025.1| 2|
3|Test_3|POLYGON ((347182.15 6615051.99, 347147.35 6615104.1, 347151.13 6615107.18, 347170.5 6615123.06, 347173.59 6615125.59, 347132.44 6615157.73, 347080 6615198.7, 347028.41 6615239, 347012.64 6615221.65, 346994.43 6615199.83, 346975.28 6615179.57, 346966.01 66| 3|
Vue avant update
|code|refparc_liste|numero_zae|nom_zae|
|----|-------------|----------|-------|
|01-001|ZR0063|1|Test_1|
|01-002|ZR0655|1|Test_1|
|01-003|ZR0633,ZR0636,ZR0444,ZR0443,ZR0634,ZR0635,ZR0445|1|Test_1|
|01-004|ZR0656|1|Test_1|
|01-005|ZR0438,ZR0439|1|Test_1|
|01-006|ZR0448|1|Test_1|
|01-007|ZR0440,ZR0452|1|Test_1|
|01-008|ZR0451|1|Test_1|
|01-009|ZR0437|1|Test_1|
|01-010|ZR0441|1|Test_1|
|01-011|ZR0062|1|Test_1|
|01-012|ZR0657|1|Test_1|
|02-001|C0956,C0957|2|Test_2|
|02-002|C0959|2|Test_2|
|02-003|C0958|2|Test_2|
|02-004|C0955|2|Test_2|
|03-001|AD0173|3|Test_3|
|03-002|AD0172,AD0171|3|Test_3|
|03-003|AD0181,AD0180|3|Test_3|
|03-004|AD0179|3|Test_3|
|03-005|AD0169|3|Test_3|
|03-006|AD0177|3|Test_3|
|03-007|AD0178|3|Test_3|
|03-008|AD0189|3|Test_3|
|03-009|AD0176|3|Test_3|
|03-010|AD0221,AD0220|3|Test_3|
|03-011|AD0223|3|Test_3|
|03-012|AD0170|3|Test_3|
|03-013|AD0168|3|Test_3|
|03-014|AD0186|3|Test_3|
Vue après update
|code|refparc_liste|numero_zae|nom_zae|
|----|-------------|----------|-------|
|01-001|ZR0062|1|Test_1|
|01-002|ZR0063|1|Test_1|
|01-003|ZR0633,ZR0636,ZR0444,ZR0443,ZR0634,ZR0635,ZR0445|1|Test_1|
|01-004|ZR0656|1|Test_1|
|01-005|ZR0438,ZR0439|1|Test_1|
|01-006|ZR0441|1|Test_1|
|01-007|ZR0448|1|Test_1|
|01-008|ZR0440,ZR0452|1|Test_1|
|01-009|ZR0451|1|Test_1|
|01-010|ZR0437|1|Test_1|
|01-011|ZR0657|1|Test_1|
|01-012|ZR0655|1|Test_1|
|02-001|C0956,C0957|2|Test_2|
|02-002|C0959|2|Test_2|
|02-003|C0958|2|Test_2|
|02-004|C0955|2|Test_2|
|03-001|AD0181,AD0180|3|Test_3|
|03-002|AD0166|3|Test_3|
|03-003|AD0173|3|Test_3|
|03-004|AD0172,AD0171|3|Test_3|
|03-005|AD0163,AD0164,AD0165|3|Test_3|
|03-006|AD0179|3|Test_3|
|03-007|AD0189|3|Test_3|
|03-008|AD0169|3|Test_3|
|03-009|AD0177|3|Test_3|
|03-010|AD0178|3|Test_3|
|03-011|AD0156|3|Test_3|
|03-012|AD0176|3|Test_3|
|03-013|AD0221,AD0220|3|Test_3|
|03-014|AD0223|3|Test_3|
|03-015|AD0170|3|Test_3|
|03-016|AD0186|3|Test_3|
|03-017|AD0167,AD0168|3|Test_3|
J’ai déjà essayé avec un ORDER BY dans la concaténation :
concat(lpad(zae_test.numero_zae::text, 2, '0'::text), '-', lpad(row_number() OVER (PARTITION BY zae_test.numero_zae ORDER BY zae_test.id)::text, 3, '0'::text)) AS code, Mais le code se modifie quand même.
Voici mon code complet :
CREATE OR REPLACE VIEW public.unites_foncieres_test
AS SELECT row_number() OVER () AS ogc_fid,
foo.code_insee,
foo.nompro,
foo.adr_prop,
foo.cp_prop,
foo.ville_prop,
foo.wkb_geometry,
( SELECT sum(parcelles.surff) AS surface_fiscale
FROM dblink('hostaddr=xxx port=xxx dbname=xxx user=xxx password=xxx'::text, 'SELECT surff, wkb_geometry FROM parcelles'::text) parcelles(surff integer, wkb_geometry geometry(MultiPolygon,2154))
WHERE st_contains(foo.wkb_geometry, st_pointonsurface(parcelles.wkb_geometry))
GROUP BY foo.wkb_geometry) AS surface_fiscale,
round(st_area(foo.wkb_geometry)::integer::numeric, 0) AS surface,
concat(lpad(zae_test.numero_zae::text, 2, '0'::text), '-', lpad(row_number() OVER (PARTITION BY zae_test.numero_zae ORDER BY zae_test.id)::text, 3, '0'::text)) AS code,
( SELECT string_agg(btrim(parcelles.refparc::text), ','::text) AS string_agg
FROM dblink('hostaddr=xxx port=xxx dbname=xxx user=xxx password=xxx'::text, 'SELECT refparc, wkb_geometry FROM parcelles'::text) parcelles(refparc character varying, wkb_geometry geometry(MultiPolygon,2154))
WHERE st_contains(foo.wkb_geometry, st_pointonsurface(parcelles.wkb_geometry))) AS refparc_liste,
foo.numero_zae,
foo.nom_zae
FROM ( SELECT faa.code_insee,
faa.nompro,
faa.adr_prop,
faa.cp_prop,
faa.ville_prop,
faa.numero_zae,
(st_dump(st_union(faa.wkb_geometry))).geom::geometry(Polygon,2154) AS wkb_geometry,
faa.nom_zae
FROM ( SELECT parcelles.ogc_fid,
parcelles.wkb_geometry,
parcelles.code_insee,
parcelles.nompro,
parcelles.adr_prop,
parcelles.cp_prop,
parcelles.ville_prop,
zae_1_1.nom AS nom_zae,
zae_1_1.numero_zae
FROM zae_test zae_1_1,
dblink('hostaddr=xxx port=xxx dbname=xxx user=xxx password=xxx'::text, 'SELECT ogc_fid, wkb_geometry, code_insee, nompro,adr_prop,cp_prop,ville_prop FROM parcelles'::text) parcelles(ogc_fid integer, wkb_geometry geometry(MultiPolygon,2154), code_insee character varying(5), nompro character varying, adr_prop character varying, cp_prop character varying, ville_prop character varying)
WHERE st_contains(zae_1_1.wkb_geometry, st_pointonsurface(parcelles.wkb_geometry))) faa,
zae_test zae_1
GROUP BY faa.nompro, faa.code_insee, faa.adr_prop, faa.cp_prop, faa.ville_prop, faa.numero_zae, faa.nom_zae) foo
JOIN zae_test ON foo.numero_zae::text = zae_test.numero_zae::text;
Et autre interrogation (moins importante), l’affichage de ma vue est très long, je suppose que c’est parce que j’utilise un dblink mais je ne suis pas sûr. Pour pallier ça, j’ai fait une vue matérialisée temporairement avec un trigger qui vient refresh la vue.
Merci de votre aide (si vous avez une solution) et bonne journée 
,
Pages : 1