Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 lun. 10 septembre 2018 12:14

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 224

[PostGis] Index spatiaux et foreign table

Bonjour à tous,

Je tente un croisement d'une couche communes d'un projet avec la Bd-Topo, afin d'avoir quelques stats communales (surface en forêt, bâtie et hydro).
Ma couche de travail des communes et celles de la BD Topo se situent dans 2 bases de données différentes.
Je les ai importées dans un schéma (bdtopo) de la BD de travail via un Foreign Data Wrapper (FDW), et des Foreign Table.

Malheureusement, la requête de croisement mets des heures à s’exécuter...
J'ai pourtant bien des index spatiaux sur chacune des tables à croiser.

Je me pose la question de savoir s'ils sont utilisés dans le cadre d'une jointure sur des Foreign Table ?

Voici la requête :

Code:

CREATE MATERIALIZED VIEW vm_communes_stats_bdtopo AS

WITH    communes_test as (SELECT * FROM communes LIMIT 1), -- limité à 1 commune pour le test *
    forets as (SELECT * FROM bdtopo.veget_zone_vegetation WHERE left(nature,5)='Forêt')

SELECT     c.code_insee,
    c.nom,
    c.geom,
    sum(st_area(St_Intersection(c.geom,b.geom))) as surf_bati,
    sum(st_area(St_Intersection(c.geom,f.geom))) as surf_foret,
    sum(st_area(St_Intersection(c.geom,h.geom))) as surf_hydro
  FROM communes_test c
  JOIN bdtopo.bati_indifferencie b ON ST_Intersects(c.geom, b.geom)
  JOIN forets f ON ST_Intersects(c.geom, f.geom)
  JOIN bdtopo.hydro_surface_eau h ON ST_Intersects(c.geom, h.geom)
  GROUP BY c.code_insee,
    c.nom,
    c.geom
    ;

Mais peut-être ma requête n'est-elle pas optimale : si vous voyez une erreur de conception, n'hésitez pas à me la signaler !

Merci pour vos conseils !

Sylvain M.

* même en limitant à 1 seule commune, la requête est en cours depuis plus d'1 demie-heure...

Dernière modification par Sylvain M. (lun. 10 septembre 2018 12:15)


Sylvain M.

Hors ligne

 

#2 lun. 10 septembre 2018 13:58

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

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:


Code:

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

Hors ligne

 

#3 lun. 10 septembre 2018 14:24

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

Concernant le support des index, des infos là:
https://dba.stackexchange.com/questions … ign-tables

Nicolas

Hors ligne

 

#4 lun. 10 septembre 2018 14:36

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2745
Site web

Re: [PostGis] Index spatiaux et foreign table

Bonjour,

G. Brassens a écrit:

Déférence gardée envers Paul Valéry,
Moi, l'humble troubadour, sur lui je renchéris,


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 :

SELECT * INTO monschema.extraitforet FROM bdtopo.veget_zone_vegetation WHERE left(nature,5)='Forêt'


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.:

SELECT ida,idb,geoma,geomb INTO shema.nomtable FROM a,b WHERE st_intersects(a.geom,b.geom)


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 ...


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#5 lun. 10 septembre 2018 14:41

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 224

Re: [PostGis] Index spatiaux et foreign table

Bonjour Maître Nicolas*,

Nicolas a écrit:

Le support des index dans les foreign tables dependent de la version de PG.


Je suis encore sur PG 9.3

Je viens de rajouter l'explain à ma requête, et voici le résultat (la requête est légèrement différente car j'avais "simplifié" pour le forum) :

Code:

"GroupAggregate  (cost=1390.17..1390.96 rows=1 width=532)"
"  CTE communes_test"
"    ->  Limit  (cost=0.00..198.07 rows=1 width=11820)"
"          ->  Seq Scan on communes  (cost=0.00..36048.69 rows=182 width=11820)"
"                Filter: ("left"((pnr)::text, 5) = 'pnrnm'::text)"
"  CTE forets"
"    ->  Foreign Scan on veget_zone_vegetation  (cost=100.00..116.23 rows=2 width=174)"
"  ->  Sort  (cost=1075.87..1075.87 rows=1 width=532)"
"        Sort Key: c.code_insee, c.nom, c.surface, c.pnr, c.nom_evolution, c.geom"
"        ->  Nested Loop  (cost=200.00..1075.86 rows=1 width=532)"
"              Join Filter: ((c.geom && h.geom) AND _st_intersects(c.geom, h.geom))"
"              ->  Nested Loop  (cost=100.00..538.22 rows=1 width=500)"
"                    Join Filter: ((c.geom && f.geom) AND _st_intersects(c.geom, f.geom))"
"                    ->  Nested Loop  (cost=100.00..537.65 rows=1 width=468)"
"                          Join Filter: ((c.geom && b.geom) AND _st_intersects(c.geom, b.geom))"
"                          ->  CTE Scan on communes_test c  (cost=0.00..0.02 rows=1 width=436)"
"                          ->  Foreign Scan on bati_indifferencie b  (cost=100.00..153.86 rows=1462 width=32)"
"                    ->  CTE Scan on forets f  (cost=0.00..0.04 rows=2 width=32)"
"              ->  Foreign Scan on hydro_surface_eau h  (cost=100.00..153.86 rows=1462 width=32)"

Personnellement, je ne sais pas encore interpréter ce résultat : je vais me pencher là dessus (ça fait un moment que j'en entends parler, mais jamais encore eu le courage de m'y mettre...

Nicolas a écrit:

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).


C'est dommage ça !
Bon, je vais mettre les "where" après, et importer les datas dans ma BDD, ce sera plus simple !

Je me penche aussi sur le st_subdivide que je ne connaissais pas smile

Merci Nicolas !

* oui, c'est toi qui m'a initié à PostGis lors d'une formation ATEN en 2014 ;-) J'ai énormément appris, mais comme tu le vois, le chemin est encore long pour maitriser la Force


Sylvain M.

Hors ligne

 

#6 lun. 10 septembre 2018 14:44

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

big_smile

Hello Sylvain !

Hors ligne

 

#7 lun. 10 septembre 2018 14:49

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

9.3, pas de chance que les index foreigns soient appelés de ce que je comprends.

En plus, le explain dit que la table commune est locale, les tables bdtopo sont foreign.
Dans l'autre sens (foreign table commune, locales tables bdtopo), leurs index seraient utilisés.

Nicolas

Hors ligne

 

#8 lun. 10 septembre 2018 15:08

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

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":

Code:

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":

Code:

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.

Hors ligne

 

#9 lun. 10 septembre 2018 15:18

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2745
Site web

Re: [PostGis] Index spatiaux et foreign table

Bonjour,

Nicolas, sur ce sujet :

différence entre st_contains(communegeom,batigeom) et st_within(batigeom,communegeom) avec pour les deux un index sur la table bâtiment ?


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#10 lun. 10 septembre 2018 15:25

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

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

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

Code:

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

Nico

Oui memes plans pour les deux requetes:

Code:

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

Code:

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 (lun. 10 septembre 2018 15:37)

Hors ligne

 

#11 lun. 10 septembre 2018 15:33

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 711

Re: [PostGis] Index spatiaux et foreign table

Pour le CASE WHEN, on peut ré-écrire la requête avec un UNION,
mais pas sûr que ça soit réellement plus performant, peut être même cela le serait il moins.

Pour le explain, il y a explain.despez.com qui est bien pratique pour lire les résultats
et apprendre à les interpréter..

Hors ligne

 

#12 lun. 10 septembre 2018 15:36

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

tumasgiu a écrit:

Pour le explain, il y a explain.despez.com qui est bien pratique pour lire les résultats
et apprendre à les interpréter..


+1

Hors ligne

 

#13 lun. 10 septembre 2018 15:37

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 711

Re: [PostGis] Index spatiaux et foreign table

Nicolas Ribot a écrit:

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

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

Code:

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

Nico


Oui, même pas d'overhead dû à l'appel de deux fonctions
puisque la fonction sera le plus souvent ré-écrit par le planner.

Dernière modification par tumasgiu (lun. 10 septembre 2018 15:39)

Hors ligne

 

#14 lun. 10 septembre 2018 15:39

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2745
Site web

Re: [PostGis] Index spatiaux et foreign table

Bonjour,

C'est l'interro de rentrée, Christophe ?


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.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#15 lun. 10 septembre 2018 15:51

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 224

Re: [PostGis] Index spatiaux et foreign table

Oulala, ma question n'était donc pas si bête vu toutes ces réponses !
(d'ailleurs, il n'y a pas de question bête, je sais bien !)

Oui, même pas d'overhead dû à l'appel de deux fonctions
puisque la fonction sera le plus souvent ré-écrit par le planner.


Bon, là vous m'excuserez, mais moi je suis plus fan de Radiohead qu'Overhead, et pour le planner, j'ai le vertige...
Mais j'ai plein de bonnes lectures avec vos échanges !


Sylvain M.

Hors ligne

 

#16 lun. 10 septembre 2018 16:01

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 711

Re: [PostGis] Index spatiaux et foreign table

Motörhead ca fait plus planer.

Hors ligne

 

#17 lun. 10 septembre 2018 16:12

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2745
Site web

Re: [PostGis] Index spatiaux et foreign table

Non !

Pour rester sur le sujet géomatique c'est "The Box Top" (utile pour le comparateur &&) , the letter
"Give me a ticket for an Aeroplane ..."


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

#18 mer. 12 septembre 2018 11:57

Sylvain M.
Membre
Lieu: Carrouges, Orne (61)
Date d'inscription: 8 sept. 2005
Messages: 224

Re: [PostGis] Index spatiaux et foreign table

Salut les maîtres Jedi de PostGis ! smile

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 :

Code:

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 :

Code:

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 ! smile


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Sylvain M.

Hors ligne

 

#19 mer. 12 septembre 2018 12:38

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1123

Re: [PostGis] Index spatiaux et foreign table

Oui tjs mieux de valider les objets issus d'opérations topo (union, intersection, etc.)

Nicolas

Hors ligne

 

#20 mer. 12 septembre 2018 13:58

ChristopheV
Membre
Lieu: Ajaccio
Date d'inscription: 7 sept. 2005
Messages: 2745
Site web

Re: [PostGis] Index spatiaux et foreign table

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.


Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |