Le portail francophone de la géomatique

yaks48 · Wed 07 December 2022 10:47

Bonjour,
Je travaille sous pgAdmin 4 et dbManager (QGIS 3.28.1)
dans le cadre de ma requête SQL qui consiste dans les grosses lignes à regarder l'intersection de plusieurs zonages avec certaines parcelles, je cherche à supprimer certains doublons particuliers : des doublons sur certains champs lorsque d'autres champs sont égaux à une valeur 'ns' (non significative).
Par exemple, je cherche à éliminer les doublons vis-à-vis de ma jointure sur natura2000 et znieff (imaginons respectivement 0 (car pas d'intersection avec mes parcelles vis-à-vis de Natura2000) et 1 pour znieff, mais la même géométrie est répétée plusieurs fois à cause d'une autre intersection avec une autre table nommée t1.
L'intersection renvoie soit "NULL" si pas d'intersection, soit 'ns' si non significatif, soit du texte si l'intersection est significative.

je veux supprimer les lignes qui doublonnent sur n2000 et znieff ET qui ont un 'ns' ET dont une ligne au moins est NULL ou <> 'ns' pour l'intersection avec t1
je ne veux pas supprimer une géométrie qui n'aurait qu'une ligne dont un 'ns' pour l'intersection avec t1.

je n'arrive pas à trouver de solution adaptée en SQL sur le forum ou sur internet

J'espère être clair, désolé je suis débutant en SQL j'espère bien m'exprimer

Merci d'avance énormément

Mathieu Denat · Wed 07 December 2022 20:17

Bonsoir,

Je ne suis pas sur d'avoir saisi en détail les subtilités (je n'ai pas saisi à quel comment intervient t1), une requête aurait été bienvenue!

Si j'ai bien compris, l'idée est de tester l'intersection d'une couche avec des zonages.
Personnellement je procéderais par étape (je pars du postulat que les parcelles ont un identifiant unique : id_parcelle, et que vous travaillez sous postgreSQL):
1. sélection des tables qui intersectent mon zonage, je garde l'id unique et l'info du zonage 1
2. idem pour le zonage 2
3. idem pour le zonage 3, etc, etc
4. je joins les différentes tables à ma table parcelle sur leur identifiant

Code:

with

--pour les znieff
znieff as (
select distinct p.*, 'true'::boolean znieff
from parcelles p
join znieff tj on st_intersects(p.geom,tj.geom)
)

--pour natura2000
,n2000 as (
select distinct p.id, 'true'::boolean n2000
from parcelles p
join natura_2000 tj on st_intersects(p.geom,tj.geom)
)

---etc, etc ajouter autant de blocs que de zonages

-- étape finale: tout joindre
select distinct p.*, z.znieff, n.n2000
from parcelle p
left join znieff z  on p.id_parcelle = z.id_parcelle
left join n2000 n on p.id_parcelle = n.id_parcelle

Le résultat attendu est du type:
geom, id_parcelle, znieff, n2000, etc.
znieff et n2000 sont des booléens (valeur vrai/faux ou null).

Cette couche peut être intersectée avec la couche t1 (et éventuellement filtré au préalable).

J'espère apporter un début de solution, bonne continuation.

yaks48 · Fri 09 December 2022 10:25

Bonjour Mathieu,
Merci beaucoup pour votre réponse.
Je vais essayer votre solution et voir ce que ça donne.

pour l'instant j'ai construit ce code (t1 correspond en fait au rpg) :

Code:

 create table mes_resultats_temporaires as

select mes_parcelles.geom, mes_parcelles.numero, 

CASE WHEN st_area(st_intersection(mes_parcelles.geom, rpg.geom)) > 0.05*st_area(mes_parcelles.geom) 
    THEN rpg.libelle 
    WHEN st_area(st_intersection(mes_parcelles.geom,rpg.geom)) between 0 and 0.05*st_area(mes_parcelles.geom)
    THEN 'ns'
    ELSE NULL 
    END as r_rpg,

row_number() OVER () as champ_uni

from mes_parcelles

left join rpg on st_intersects(mes_parcelles.geom, rpg.geom)

group by mes_parcelles.geom, mes_parcelles.numero, r_rpg
;

create table resultat_final as

select mes_resultats_temporaires.geom, mes_resultats_temporaires.numero, mes_resultats_temporaires.r_rpg

CASE WHEN sum(st_area(st_intersection(mes_resultats_temporaires.geom, znieff.geom))) > 0.05*st_area(mes_resultats_temporaires.geom) 
    THEN 1 ELSE 0 END as r_znieff,
CASE WHEN sum(st_area(st_intersection(mes_resultats_temporaires.geom, n2000.geom))) > 0.05*st_area(mes_resultats_temporaires.geom) 
    THEN 1 ELSE 0 END as r_n2000,
    
row_number() OVER () as champ_uni

from mes_resultats_temporaires

left join znieff on st_intersects(mes_resultats_temporaires.geom, znieff.geom)
left join n2000 on st_intersects(mes_resultats_temporaires.geom, n2000.geom)

group by mes_resultats_temporaires.geom, mes_resultats_temporaires.numero, r_znieff, r_n2000

je l'ai fait en deux fois car si je fais tout en une fois, il m'est arrivé d'avoir une parcelle qui avait 0 en znieff par exemple et la même parcelle qui avait 1 en znieff : une autre ligne a été créée car j'intersecte 2 entités de la table rpg. et du coup j'ai l'impression que postgre a sommé les surfaces deux fois, ce qui m'a fait dépasser les 5 % de la surface de ma parcelle analysée.

Et derrière, je cherche à éliminer les doublons sur tous les champs, sauf mon champ rpg : je veux éliminer les lignes contenant 'ns' au rpg, que si elles existent et que le reste est identique par ailleurs.

j'ai essayé cela mais je ne suis pas sûr ...

Code:

delete from resultat_final 
where champ_uni in(
        select champ_uni
            from (
                select *, row_number() over (partition by numero, r_znieff as rn
                from resultat_final ) x
            where (x.rn > 1 and rpg='ns') )

est-ce plus clair ?

merci beaucoup pour votre aide, je vais essayer votre méthode.

bien à vous

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 Wed 07 December 2022 10:47

QGIS: suppression conditionnelle de doublons SQL

#2 Wed 07 December 2022 20:17

Re: QGIS: suppression conditionnelle de doublons SQL

Code:

#3 Fri 09 December 2022 10:25

Re: QGIS: suppression conditionnelle de doublons SQL

Code:

Code:

Pied de page des forums