Pages: 1
- Sujet précédent - [PostGIS] : optimisation de requête pour les objets non intersectés - Sujet suivant
#1 Fri 09 November 2018 08:57
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
[PostGIS] : optimisation de requête pour les objets non intersectés
Bonjour,
J'ai deux tables, une contenant des multipolygones et une autre contenant des points. Les deux tables possèdent des index spatiaux.
Je cherche à trouver les points qui ne sont pas contenus dans les polygones.
Voici les requêtes que j'ai testé afin de comparer les temps d'execution:
Code:
select * from "Phil"."import_leve_PointZ" a left join lateral ( select true t from "Phil"."polygon"b where st_intersects(b.geom, a.geom) limit 1 ) b on true where b.t is null
Code:
select a.geom from "Phil"."import_leve_PointZ" a WHERE not EXISTS (select * from "Phil"."polygon"b where st_intersects(b.geom, a.geom) )
Ma table de points contient 4067720 d'objets et ma table polygone n'en contient qu'un seul à trou ( d'une surface de
16244319.4368 m²)
Les temps de traitement sont d'environ 50 minutes quelque soit la requête.
Est-ce qu'il y aurait un moyen de réduire ce temps de traitement?
D'avance merci
Ted
Hors ligne
#2 Fri 09 November 2018 09:23
- ChristopheV
- Membre
- Lieu: Ajaccio
- Date d'inscription: 7 Sep 2005
- Messages: 3175
- Site web
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Bonjour,
Compte tenu que vous n'avez qu'un seul multipolygon je virerais l'index pour être sur que celui qui est pris en compte est celui des points.
Ensuite je raisonnerais en creux. Je chercherais les points qui sont dans le multipolygon puis je ferais une union except entre le résultat et la table point.
Non testé, une idée comme ça.
Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close
Hors ligne
#3 Fri 09 November 2018 09:58
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Bonjour,
Deux points:
• Découper le gros polygone en pleins de petits (st_subdivide): le temps de traitement sera réduit de facon... spectaculaire
• Utilisez un anti join pour trouver les geometries sans rapport spatial avec d'autres: les index spatiaux ne marchent bien que pour répondre: "oui, des objets sont en relations spatiales"::
Code:
select p.id from points p where not exists ( select null from poly_decoupe d where st_dwithin(p.geom, d.geom, 0.001) --< precision a regler suivant vos données, ou st_contains );
Pfff pardon ! c'est votre deuxieme requete ! j'ai lu un peu vite...
Découpage donc !
Nicolas
Hors ligne
#4 Fri 09 November 2018 10:05
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Si les points sont tjs contenus dans les trous du PG, vous pouvez dumper les trous (st_dumprings), indexer cette table, et chercher les points contenus dans ces PG: ca marche souvent tres vite aussi si les trous sont petits (sinon éventuellement les découper)
Nicolas
Hors ligne
#5 Fri 09 November 2018 10:08
- ChristopheV
- Membre
- Lieu: Ajaccio
- Date d'inscription: 7 Sep 2005
- Messages: 3175
- Site web
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Bonjour,
Découper le gros polygone en pleins de petits (st_subdivide): le temps de traitement sera réduit de facon... spectaculaire
Pour ce genre de requête en revanche pour des fonction comme st_symdifference() c'est l'inverse.
Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close
Hors ligne
#6 Fri 09 November 2018 10:23
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Bonjour,
merci pour vos éléments de réponse.
Pour St_subdivide, je vais être bloqué car ma version est trop ancienne (PostGIS=2.1.4 GEO 3.4.2) alors qu'il faudrait la version 2.2.0 et 3.5.0.
Et bien évidemment, je n'ai pas la main dessus ;-)
Hors ligne
#7 Fri 09 November 2018 10:28
- ChristopheV
- Membre
- Lieu: Ajaccio
- Date d'inscription: 7 Sep 2005
- Messages: 3175
- Site web
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Bonjour,
Comme l'indique Nicolas, les index ça marche bien pour le OUI. Combien de temps met votre sous requête avec st_within() ?
Christophe
L'avantage d'être une île c'est d'être une terre topologiquement close
Hors ligne
#8 Fri 09 November 2018 10:28
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1538
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Vous pouvez découper vous meme, avec une grille de découpage (plus long que subdivide, mais ca marche bien)
Pour créer la grille: https://trac.osgeo.org/postgis/wiki/Use … ateFishnet
Nicolas
Hors ligne
#9 Fri 09 November 2018 11:39
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
@ ChristopheV, j'ai relancé avec st_within est la requête n'est pas finie. Elle devrait mettre le même temps je pense?
@ Nicolas, j'ai lancé "le filet" et je suis en train de découper pour polygone et ensuite lancer la requête de recherche.
J'attends les résultats et vous tiens au courant dès que possible
Hors ligne
#10 Mon 12 November 2018 09:34
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Re bonjour à tous,
Le st_within dans la sous requête me donne un temps de traitement de 51 minutes.
Quant à la requête proposée par Nicolas, j'ai des messages d'erreur :
NOTICE: gserialized_gist_joinsel: jointype 5 not supported
ERROR: Relate Operation called with a LWGEOMCOLLECTION type. This is unsupported.
HINT: Change argument 2: 'GEOMETRYCOLLECTION(LINESTRING(-1.5977 49.6718641,-1.5977 49.6718465),LINESTRI...'
********** Erreur **********
ERROR: Relate Operation called with a LWGEOMCOLLECTION type. This is unsupported.
État SQL :XX000
Astuce : Change argument 2: 'GEOMETRYCOLLECTION(LINESTRING(-1.5977 49.6718641,-1.5977 49.6718465),LINESTRI...'
Hors ligne
#11 Mon 12 November 2018 10:43
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Pour la requête de Nicolas,
pouvez vous la poster, ainsi que le plan d’exécution ?
On dirait que la découpe génère des géométries
incompatibles avec l'index (!), pour remédier à cela
il faudrait peut être filter au préalable la sortie de votre subdivide maison,
afin de ne retenir que les (multi)polygones.
Pour votre requête initiale, simple remarque :
je ne sais pas si utiliser une requête latérale produira
le plan de requête le plus optimal (même si peut être que dans ce cas-ci,
il sera identique).
Sinon, avec 4 fois moins de points que vous et pour un polygone de surface équivalente,
avec la requête suivante j'arrive à 1min10 :
Code:
SELECT st_dump(st_difference(points, geom)) point FROM (SELECT st_union(point) FROM mes_points) p, (SELECT geom FROM mes_polys LIMIT 1) g;
Il faut ensuite refaire la jointure avec la table des points. (on peut utiliser st_memunion pour être gentil et ne pas consommer trop de mémoire).
EDIT: 2min20 avec une jointure spatiale
EDIT2: 1min30 en joignant sur l'id des points (utilisation d'une des dimensions dispo (M ou Z) pour stocker l'idée du point dans le st_union).
EDIT3: changer st_union avec st_collect n'a pas l'air d'affecter le temps d'exécution.
EDIT4: la dimension M ne peut pas être utilisée pour stocker l'id car elle est effacée par st_difference.
Dernière modification par tumasgiu (Mon 12 November 2018 11:53)
Hors ligne
#12 Mon 12 November 2018 13:45
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Merci Tumasgiu,
J'ai filtré les objets en les changeant tous en "single" et je me suis rendu compte qu'il y avait des lignes. Après leurs occultations, la requête ne Nicolas fonctionne parfaitement et son temps de traitement de l'ordre de 3 minutes.
J'ai testé ta requête (post précédent), mais elle me renvoie un résultat vide, est-ce normal?
Ted
Hors ligne
#13 Mon 12 November 2018 14:08
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Je ne sais pas, il faudrait voir votre requête.
Hors ligne
#14 Mon 12 November 2018 14:15
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
De toutes les manières, la méthode par subdivision est préférable, je pense.
Dernière modification par tumasgiu (Mon 12 November 2018 14:18)
Hors ligne
#15 Mon 12 November 2018 14:19
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Ok,
je vais donc rester à la méthode de subdivision qui fonctionne parfaitement.
Encore merci
Hors ligne
#16 Mon 12 November 2018 14:24
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Mais je serai curieux de voir la différence si vous voulez bien poster la requête.
Hors ligne
#17 Mon 12 November 2018 14:51
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Si je comprends vous voulez la requête de Nicolas?
La voici, c'est celle que j'avais posté également au tout début de mon post.
Code:
select p.id, p.geom from "Phil"."import_leve_PointZ" p where not exists ( select null from "Phil"."polygondecoup_singlev2" d where st_intersects(p.geom, d.geom) )
Est-ce cela?
Hors ligne
#18 Mon 12 November 2018 14:55
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Ah désolé, je voulais votre test avec le st_difference.
Hors ligne
#19 Mon 12 November 2018 15:12
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
En testant séparément, je me suis aperçu que dans la requête :
Code:
SELECT st_dump(st_difference(geom, g.geom)) point FROM (SELECT st_union(geom) FROM "Phil"."import_leve_PointZ") p, (SELECT geom FROM "Phil"."polygon" LIMIT 1) g
la partie relative au st_union me renvoyait un résultat vide!
Hors ligne
#20 Mon 12 November 2018 15:48
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Oui, en fait, votre requête calcule la différence du polygone avec lui même,
puisque geom et g.geom font tout deux réference a la colonne geom de g.
Une colonne issue du calcul d'une fonction porte le nom de la fonction si elle n'a pas d'alias,
ici, le nom de la colonne de la table p serait "st_union".
il faudrait donner un alias au st_union, genre points.
Une requête qui devrait marcher, (avec la jointure):
Code:
SELECT z.* FROM "Phil"."import_leve_PointZ" z JOIN ( SELECT (st_dump(st_difference(points, g.geom))).geom point FROM (SELECT st_union(geom) points FROM "Phil"."import_leve_PointZ") p, (SELECT geom FROM "Phil"."polygon" LIMIT 1) g) foo ON ST_equals(foo.geom, z.geom);
Dernière modification par tumasgiu (Mon 12 November 2018 16:04)
Hors ligne
#21 Tue 13 November 2018 09:24
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Tumasgiu, je viens de stopper la requête, car ça faisait 17h30 qu'elle tournait
Hors ligne
#22 Tue 13 November 2018 11:45
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Oui effectivement, sage décision.
Je vous embête mais si vous trouvez le temps,
est ce que vous pourriez poster le plan ?
vous pouvez l'obtenir en ajoutant EXPLAIN au début de la requête.
Merci.
Hors ligne
#23 Tue 13 November 2018 11:51
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
En fichier joint le résultat du explain.
En espérant que cela vous soit utile
Hors ligne
#24 Tue 13 November 2018 17:10
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Merci.
Bon Postgres a visiblement décidé de ne pas utiliser l'index
présent sur votre table de point, pour faire la jointure.
L'ajout de l'opérateur && (cf. https://georezo.net/forum/viewtopic.php?pid=300647)
permet de "forcer" son utilisation.
Dernière modification par tumasgiu (Tue 13 November 2018 17:29)
Hors ligne
#25 Wed 14 November 2018 13:32
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Merci pour le "&&", sur ta requête elle se termine en 20 minutes (contrairement à la dernière fois où j'avais du la stopper).
Hors ligne
#26 Wed 14 November 2018 14:23
- tumasgiu
- Membre
- Lieu: Ajaccio
- Date d'inscription: 5 Jul 2010
- Messages: 1142
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Merci.
Dernière précisions ensuite je ne vous embête plus.
Pouvez vous me dire combien de temps La requête avec la grille avait mis à terminer ?
Et est ce que vous pourriez me donner la config de la machine
sur laquelle tourne postgresql ainsi que l'OS et éventuellement les modifs
que vous auriez apporté au fichier de configuration ?
Hors ligne
#27 Wed 14 November 2018 14:47
- Ted
- Participant assidu
- Date d'inscription: 16 Jan 2007
- Messages: 181
Re: [PostGIS] : optimisation de requête pour les objets non intersectés
Avec la grille 3 minutes
là j'obtiens un résultat en 20 minutes, mais le résultat n'est pas bon, il faut que je vois pourquoi. Normalement, je devrais avoir qu'une seule "anomalie", présentement j'en ai 4067733!
La machine est mon PC de travail, RAm 12Go, I7-3773 CPU 3.4Ghz.
Je n'ai apporté aucune modification sur la config.
Hors ligne
Pages: 1
- Sujet précédent - [PostGIS] : optimisation de requête pour les objets non intersectés - Sujet suivant