Le portail francophone de la géomatique

Bonjour,

Le support des index dans les foreign tables dependent de la version de PG.
Un "explain" devant votre requete vous permettra de voir s'ils sont utilisés.

Les FDW, c'est très pratique, mais dans votre cas PG <-> PG, je vous conseille de regrouper vos données dans une seule base, organisée en schémas. C'est beaucoup plus simple et pratique à gérer.

Pour copier d'une base à l'autre: create table localtable as select * from foreigntable;

Pour votre requete, le fait de déclarer des tables tmp dans des CTE, pour filtrer les communes, fait que vous n'avez plus d'index au moment des croisements spatiaux (enfin je pense: un explain... permettrait d'en etre sur).

Ex sur les plans des requetes suivantes:

explain select c.id, c.geom, v.gid, v.nature
from ref.commune c join bdtopo_2016.zone_vegetation v on st_intersects(c.geom, v.geom)
where left(v.nature, 5) = 'Forêt';
-- QUERY PLAN with index
-- Nested Loop  (cost=0.28..2077160.83 rows=315709 width=2935)
--   ->  Seq Scan on zone_vegetation v  (cost=0.00..1912601.48 rows=80580 width=1279)
--         Filter: ("left"((nature)::text, 5) = 'Forêt'::text)
--   ->  Index Scan using commune_geom_gist on commune c  (cost=0.28..2.03 rows=1 width=2922)
--         Index Cond: (geom && v.geom)
--         Filter: _st_intersects(geom, v.geom)

vs 

explain
with tmp as (
    select v.gid, v.nature, v.geom
    from bdtopo_2016.zone_vegetation v
    where left(v.nature, 5) = 'Forêt'
), tmp1 as (
     select *
     from ref.commune
     limit 1
) select c.id, c.geom, t.*
from tmp1 c
           join tmp t on st_intersects(c.geom, t.geom);
-- QUERY PLAN
-- Nested Loop  (cost=1912601.86..1935365.73 rows=27 width=140)
--   Join Filter: ((c.geom && t.geom) AND _st_intersects(c.geom, t.geom))
--   CTE tmp
--     ->  Seq Scan on zone_vegetation v  (cost=0.00..1912601.48 rows=80580 width=1279)
--           Filter: ("left"((nature)::text, 5) = 'Forêt'::text)
--   CTE tmp1
--     ->  Limit  (cost=0.00..0.38 rows=1 width=3059)
--           ->  Seq Scan on commune  (cost=0.00..13908.67 rows=36467 width=3059)
--   ->  CTE Scan on tmp1 c  (cost=0.00..0.02 rows=1 width=36)
--   ->  CTE Scan on tmp t  (cost=0.00..1611.60 rows=80580 width=104)

Il faut mieux mettre la clause where des forets direct dans la requete de croisement.

Ensuite, oui ca sera surement long s'il y a bcp de communes: 47M de batiments en france, pas mal de zones boisées. Ca fait beaucoup d'objets a croiser.
Là le conseil est de découper les datasets:
Postgis aime bien travailler avec pleins de petits objets, plutot qu'un gros, notamment pour la phase de recherche dans l'index, et ensuite lors des calculs.
Ici par ex, vous croisez les communes avec les batiments: les communes sont immenses par rapport aux batiments et chaque intersection commune/bat va prendre du temps.
De meme avec les zones boisées ou surfaces en eau qui peuvent etre de très grands polygones.

Donc:
• découper les gros datasets (st_subdivide => nouvelle PK pour les PG découpés)
• requete sur les tables découpées (indexées).
• Union par PK commune pour reconstruire les synthèses par commune.

Nicolas

Bonjour,

Effectivement tout dans la même BD dans des schémas différents.
Une autre astuce pour les tables temporaires, c'est de créer des tables "réelles" et d'ajouter un index ex :

Puis création de l'index spatial.

L'autre idée, qui revient à un découpage de dataset, c'est de créer une table avec les objet qui ont une intersection, puis de calculer l'intersection.:

Puis calcul de st_area(st_intersection(a.geom,b.geom))

Pareil pour les jointures, il vaut mieux écrire une requête pour le croisement bâti, commune, puis une pour hydro commune, puis une avec le résultat de la première et de forêt ...

Hello Sylvain !

Un "trick" bien utile également (de maitre Ramsey, et également vu sur cette liste), lors du croisement de deux couches dont l'une contient des gros PG par rapport à l'autre (commune/batiment, ou commune/parcelle par ex) est de tester si le petit pg est contenu dans le gros: si c'est le cas, pas la peine de faire l'intersection entre les deux objets.

Ex sur le croisement communes (6 538 888 de vertex) et batiments (45 798 055 d'objets) pour avoir la somme de bati par commune:

1) approche "classique":

select c.id, c.nom, c.code_insee, sum(st_area(st_intersection(b.geom, c.geom))) as a
from ref.commune c join ref.batiment b on st_intersects(b.geom, c.geom)
where c.code_insee like '93%'
group by 1, 2, 3;

2) approche "test de contenance":

select c.id, c.nom, c.code_insee,
       sum(
         case when st_contains(c.geom, b.geom)
                   then st_area(b.geom)
              else st_area(st_intersection(b.geom, c.geom)) end
       ) as a
from ref.commune c join ref.batiment b on st_intersects(b.geom, c.geom)
where c.code_insee like '93%'
group by 1, 2, 3;

On teste si le batiment est contenu dans la commune => on prend sa surface directement.

Sur une commune:
1: ~ 6 s
2: ~ 515 ms

Sur un departement (93)
1: ~1 min 57 s
2: ~ 6 s !

Bref, dès qu'on évite de faire trop souvent une intersection entre un gros objet complexe et un petit, les requêtes sont assez rapides.

Nico

PS: on lit parfois qu' un CASE WHEN ....END dans un select n'est pas performant. Là, on voit que l'overhead du case par rapport au gain de ne pas calculer l'intersection est négligeable.

C'est l'interro de rentrée, Christophe ?
J'ai pas révisé

Je dirais: aucune:
st_within est définie comme suit:

SELECT $2 OPERATOR(public.~) $1 AND public._ST_Contains($2,$1)

Nico

Oui memes plans pour les deux requetes:

explain (verbose ) select c.id, c.nom, c.code_insee, count(c.*)
from ref.batiment b join ref.commune c  on st_within(b.geom, c.geom)
where c.code_insee like '93%'
group by 1, 2, 3;

et

explain (verbose ) select c.id, c.nom, c.code_insee, count(c.*)
from ref.commune c  join ref.batiment b on st_contains(c.geom, b.geom)
where c.code_insee like '93%'
group by 1, 2, 3;

Dernière modification par Nicolas Ribot (Mon 10 September 2018 15:37)

+1

Bonjour,

Moi c'est géomètre pas geo-maître ! cf. ma citation de Brassens.

Vu le code de la fonction c'est blanc bonnet et bonnet blanc.

Motörhead ca fait plus planer.

Salut les maîtres Jedi de PostGis !

Je suis toujours sur mon intersection Bd-Topo communes !
J'ai donc rapatrié mes données dans la BDD de travail.
Et ne disposant que de PostGis 2.1.3 / GEOS 3.5.0, je n'ai pas accès à la fonction "St_Subdivide"...

Pour être sûr de mes requêtes, je les fais finalement étapes par étapes, thème par thème (forêt, hydro, bâti, ...).

J'ai bien fait de vérifier, parce-que mes résultats d’intersection Forêt/Communes étaient faux !
(voir capture d'écran en pièce jointe)

J'utilisais cette requête pour la géométrie intersectée :

CASE WHEN St_Contains(c.geom, f.geom)
     THEN f.geom
     ELSE st_intersection(f.geom, c.geom)
END as geom

Du coup, j'ai eu des résultats plus cohérents avec cette requête :

CASE WHEN St_Contains(c.geom, f.geom)
     THEN f.geom
     ELSE ST_CollectionExtract(St_Multi(ST_Makevalid(St_Intersection(c.geom,f.geom))),3)
END as geom

(avec un GROUP BY derrière)

Est-ce que ça vous semble bon comme requête, ou est-ce qu'il y avait plus simple ?

Merci pour vos conseils de Jedi, pour un (pas si) jeune Padawan du SQL spatial !

Bonjour,

En sus du message de Nicolas, il faut savoir ce que vous voulez obtenir, je m'explique.
Le contour des communes et le contour parcellaire n'ont pas de topologie identique, que ce soit BD-Topo-BD-Parcellaire ou cadastre.
Donc si on veut récupérer la géométrie originale de la parcelle et non son intersection dans le cas ou il y a juste une légère différence du à ce problème de cohérence topologique, avec l'utilisation de st_symdiffrence() entre l'intersection et l'original et si st_area(st_symdifference())<valeur on reprend l'original.