Le portail francophone de la géomatique

Il faudrait faire aussi un coalesce(st_difference(....), communes.geom)

Merci pour les réponses.

Comme il s'agissait de mon premier message sur le forum, j'ai pu continuer à chercher avant que mon message soir en ligne, et j'avais en effet inclus un left join.

Concernant

En me basant sur la réponse de Nicolas à ce message https://georezo.net/forum/viewtopic.php … difference
J'arrivais à la requête suivante :

create materialized view tmp.non_exclu
AS
select communes.insee, st_difference(communes.geom, coalesce(contraintes.geom, 'GEOMETRYCOLLECTION EMPTY'::geometry)) as geom 
from 
tmp.communes_osm_aura communes
left join 
  (
  select a.insee, st_union(b.geom) as geom
  from tmp.communes_osm_aura a
  join 
  tmp.table_contraintes b
  on (a.geom && b.geom
  and st_intersects(a.geom, b.geom) )
  group by a.insee
  )  contraintes on (communes.insee = contraintes.insee) ;

Si je comprends bien, gvellut tu ferais plutôt

(...)
select communes.insee, coalesce(st_difference(communes.geom, contraintes.geom), communes.geom) as geom 
(...)

Les deux ont l'air de créer les mêmes données dans la vue.

Par contre, il y a un truc qui me chiffonne, c'est que je me retrouve avec 3 sortes de geometry :

select geometrytype(geom), count(*)
from tmp.non_exclu
group by 1
---
'GEOMETRYCOLLECTION','75'
'MULTIPOLYGON','3938'
'POLYGON','82'

Alors que dans la table des communes, j'ai seulement des MULTIPOLYGON

select geometrytype(geom), count(*)
from tmp.communes_osm_aura
group by 1
---
'MULTIPOLYGON','4095'

Et surtout, comment ça se fait que j'ai autant de communes dans la vue, alors que certaines sont chevauchées à 100% par une contrainte ?
Autrement dit, je devrais avoir moins que 4095 lignes dans la vue... Je suppose que ça a un lien avec mes 3 types de geometry mais je ne comprends pas bien pourquoi...

edit : suppression mise en gras dans sections code, ce qui ne fonctionne pas, pour ne pas perturber la lecture...

Dernière modification par p.jeremie (Tue 28 November 2017 14:07)

merci.

J'ai procédé comme ça pour mieux comprendre le cheminement, mais j'ai toujours un truc qui me chiffonne.

La première partie est claire, j'ai pour chaque commune l'union de ses contraintes dans une table intermédiaire.

C'est la deuxième partie qui me pose soucis.
J'ai autant de lignes dans la table finale que dans la table des communes.
Je m'attendais à n'avoir que les communes qui ont encore un bout "dispo" (autrement dit qui n'ont pas 100% de leur surface chevauchée par une contrainte).

Je précise que j'ai des zones de contraintes qui sont plus grandes que certaines communes (et donc ces communes sont bien complètement chevauchées).

J'ai donc ajouté une condition pour ne garder que les geometry non vides.

Je n'ai pas trouvé d'autre moyen que d'ajouter une requête qui englobe la requête avec les st_multi, st_collectionExtract et st_difference, car sinon je dois répéter tout le paquet de fonctions dans le where ! Autrement dit, pas possible de faire référence à l'alias geom car ambigu.

J'arrive à la requête suivante (j'ai choisi de faire un WITH plutôt qu'une table intermédiaire) :

create materialized view tmp.non_exclu
AS
-- union des contraintes par commune
with contraintes_communes as (
    select
        communes.insee_com as insee,
        st_union(contraintes.geom) as geom
    from ressources.communes_ign_aura communes
    join
    tmp.table_contraintes contraintes
      on (st_intersects(communes.geom, contraintes.geom))
    group by communes.insee_com
)
select insee, geom from
    (select communes.insee_com as insee, 
        -- récupération des multipolygones correspondants à la différence 
        -- entre les geom des communes et l'union des contraintes des communes
        st_multi(
            st_collectionExtract(
                st_difference(
                    communes.geom, 
                    coalesce(cc.geom, 'GEOMETRYCOLLECTION EMPTY'::geometry)
                ),
            3
            )
        ) as geom
    from ressources.communes_ign_aura communes
    left join contraintes_communes cc
    on communes.insee_com = cc.insee
    ) as s
-- filtrage des géométries vides
where st_isempty(s.geom) is not true;

Et comme ça je ne récupère plus les communes 100% concernées par des contraintes.

Remarque : du coup j'ai constaté que les communes ne disparaissent par lors de l'extraction !
Je me demande même si elle est bien nécessaire maintenant que j'ai ajouté un un filtre sur les géométries vide... car je récupère la même table avec et sans le st_collectionExtract().

Remarque 2 : ça pourrait être sympa sur ce forum d'avoir des blocs code larges comme le message et la coloration syntaxique du SQL en mettant par exempe [ code = sql ]. Une piste : https://fluxbb.org/resources/mods/flux-geshi/

Remarque 3 : j'accepte le tutoiement