Forums PostgreSQL.fr

Jules_B

Membre

Problème vue sql avec changement de géométrie et row_number

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 ,

FrançoisC

Membre

Re : Problème vue sql avec changement de géométrie et row_number

Bonjour,

Le problème réside dans le fait que la fonction row_number() est exécutée à chaque fois que la vue est interrogée, le code des unités foncières sera réinitialisé à chaque fois.

Une solution possible à ce problème est d’utiliser la fonction generate_series() pour générer une séquence de numéros uniques de manière définitive. Après cela, vous pouvez utiliser cette séquence pour générer les codes des unités foncières.