Le portail francophone de la géomatique

nebuka · Thu 01 September 2011 15:18

Bonjour,

j'ai un petit soucis d'optimisation d'une de mes requêtes.

Voilà la situation,
J'ai 2 tables, une avec des parcelles et leur information et une avec des zones humides.
La seule chose qui lie mes 2 tables ce sont leurs multipolygones.

Ma requête consiste à connaitre toutes les parcelles qui sont en contact partiellement ou totalement avec une zone humide donnée

D'après les informations que j'ai glanées un peu partout, je pensais que je pouvais utiliser un opérateur spatial : &&
Ce qui donne en SQL :

Code:

select idparcelle from parcelle, zone_humide WHERE zone_humide.the_geom && parcelle.the_geom) AND zone_humide='670057'

Et là j’obtiens pas du tout ce que je souhaite. Il me retourne des parcelles qui ne sont pas en contact avec la zone humide.

Par contre si j'utilise la fonction Intersects(), j'obtien de bon résultat avec cette requête :

Code:

select idparcelle from parcelle, zone_humide WHERE Intersects(zone_humide.the_geom ,parcelle.the_geom) AND zone_humide='670057'

Le soucis c'est qu'avec la première j'ai un résultat en 1,5 secondes, avec la deuxième : 2h35. (j'ai 50 millions d'entrées dans la table parcelle).
J'ai plus de 1500 zones humides à traiter donc là ... ça marche pas.

Est-ce qu'il y aurais un moyen pour optimiser la requête ?

D'avance merci pour votre aide.

Nebuka

Yves · Thu 01 September 2011 15:50

nebuka a écrit:

Bonjour,

j'ai un petit soucis d'optimisation d'une de mes requêtes.

Voilà la situation,
J'ai 2 tables, une avec des parcelles et leur information et une avec des zones humides.
La seule chose qui lie mes 2 tables ce sont leurs multipolygones.

Ma requête consiste à connaitre toutes les parcelles qui sont en contact partiellement ou totalement avec une zone humide donnée

D'après les informations que j'ai glanées un peu partout, je pensais que je pouvais utiliser un opérateur spatial : &&
Ce qui donne en SQL :
Code:
select idparcelle from parcelle, zone_humide WHERE zone_humide.the_geom && parcelle.the_geom) AND zone_humide='670057'
Et là j’obtiens pas du tout ce que je souhaite. Il me retourne des parcelles qui ne sont pas en contact avec la zone humide.

Par contre si j'utilise la fonction Intersects(), j'obtien de bon résultat avec cette requête :
Code:
select idparcelle from parcelle, zone_humide WHERE Intersects(zone_humide.the_geom ,parcelle.the_geom) AND zone_humide='670057'
Le soucis c'est qu'avec la première j'ai un résultat en 1,5 secondes, avec la deuxième : 2h35. (j'ai 50 millions d'entrées dans la table parcelle).
J'ai plus de 1500 zones humides à traiter donc là ... ça marche pas.

Est-ce qu'il y aurais un moyen pour optimiser la requête ?

D'avance merci pour votre aide.

Nebuka

Bonjour,

As tu créé un index spatial sur tes colonnes géométriques ? Dans ce cas il faudra que tu utilises la fonction st_intersects(). Il me semble qu'utiliser l'opérateur spatial && peut être toujours utiliser. Celui-ci compare la bbox des géométries et est donc complémentaire de la deuxième requête.

Y.

tr · Thu 01 September 2011 16:38

Bonjour,

Effectivement comme le dit Yves, l'opérateur && et la fonction st_intersects ne font pas la même chose. Les docs de la version 1.5 sont ici :
- http://postgis.refractions.net/document … erlap.html
- http://postgis.refractions.net/document … sects.html

Selon la version de postgis, st_intersects ne fait pas de comparaison d'enveloppe il me semble. Il peut alors être utile dans le where de faire d'abord un "&&" puis un "st_intersects".

Et pour optimiser encore un peu, tu peux tester aussi en mettant

Code:

WHERE zone_humide='670057' 
AND zone_humide.the_geom && parcelle.the_geom 
AND Intersects(zone_humide.the_geom ,parcelle.the_geom)

nebuka · Thu 01 September 2011 16:52

Bon j'ai effectivement des indexes sur les données géographiques.

Dès mon retour au bureau je regardais les liens et essayera la requête proposée.

merci déjà pour ces infos.

Nebuka

MathieuB · Thu 01 September 2011 17:50

Bonsoir,

dans mon souvenir,

Code:

st_intersects(geom1,geom2)

est la "nouvelle" syntaxe pour faire

Code:

geom1 && geom2 AND Intersects(geom1,geom2)

http://postgis.refractions.net/documentation/manual-1.5/ST_Intersects.html

mentionne "This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries."

Dernière modification par Mathieu BOSSAERT (Thu 01 September 2011 18:15)

nebuka · Fri 02 September 2011 09:23

Bonjour,

alors voilà les premiers tests de ce matin, donc avec la requête suivante :

Code:

SELECT idparcelle FROM parcelle, zone_humide 
WHERE zone_humide.the_geom && parcelle.the_geom
AND Intersects(zone_humide.the_geom ,parcelle.the_geom)
AND zone_humide='670057'

J'arrive à avoir un résultat en un peu moins de 6 secondes, je vais lancer la requête sur une sélection d'une centaine de zone pour voir la durée de traitement.

Déjà un grand merci pour vos aide.

Nebuka

Yves · Fri 02 September 2011 09:42

Pour Mathieu :

Je peux me tromper mais :

A && B

The "&&" operator is the "overlaps" operator. If A's bounding box overlaps B's bounding box the operator returns true.

Avant l'arrivé des fonctions st_* le fonctionnement était en effet d'utiliser cet opérateur non pas pour utiliser les index mais parce que cet opérateur déclenchait l'utilisation des index. Bon la nuance est faible, je vous l'accorde et je ne suis pas très claire mais l'idée est que cet opérateur va réaliser un premier tri entre les géométries pour réaliser ensuite le test d'intersection qui va utiliser l'index.

Nebuka, si tu enlèves " zone_humide.the_geom && parcelle.the_geom AND " ta requête met combien de temps pour se réaliser ?

Y.

nebuka · Fri 02 September 2011 09:43

sans le && j'attend environ 2h20 avant d'avoir le résultat.

ChristopheV · Fri 02 September 2011 12:12

Bonjour,

Quelle est la différence de traitement entre une requête utilisant && pour comparer les boxs et stocker le résultat dans une table temporaire puis executer une requête intersect sur cette table temporaire et une requête comme indiquée par nebuka, qui combine les deux en une seule commande SQl ?

Ensuite juste pour info connaissez vous le type d'algo en œuvre dans intersects() ?

Nicolas Granier · Fri 02 September 2011 12:14

pour savoir si les index sont utilisés en fonction des différentes combinaisons (&& + Intersects, ST_intersects, && + ST_intersect,...), la fonction Explain peut être utilisée en préfixe de la requête.

A+

nebuka · Fri 02 September 2011 13:18

Bon alors après quelques dizaines de minutes de traitement j'ai ce message d'erreur :

Code:

NOTICE:  TopologyException: side location conflict at 924854 6.51993e+06


ERREUR:  GEOS intersects() threw an error!

********** Erreur **********

ERREUR: GEOS intersects() threw an error!
État SQL :XX000

Donc pour débugger je fais un

Code:

SELECT isValid(the_geom) from zone_humide

et j'obtien 3 warning :

Code:

NOTICE:  Ring Self-intersection at or near point 735616 7.04323e+06
NOTICE:  Ring Self-intersection at or near point 955606 6.75697e+06
NOTICE:  Ring Self-intersection at or near point 163887 6.77441e+06

J'ai corrigé les erreurs et je relance ... wait and see

Nebuka

nebuka · Mon 05 September 2011 09:41

Bon en fait ça fait presque 3 jours que ça corrige ma table parcelle ... je patiente encore.

ppluvinet · Mon 05 September 2011 10:11

Plusieurs questions :

- Que fais-tu comme correction pour enlever les self-intersection ? Buffer(the_geom,0), cleangeometry(the_geom) ? voir http://www.kappasys.ch/pgtools/cleangeo … ometry.sql

- Es-tu sûr que les objets sont bien indexés ? Create index.... puis vacuum analyse. voir http://www.postgis.fr/node/187

- combien as-tu de parcelles ? combien as-tu de zones humides ? et quelle est en gros la surface moyenne d'une zone humide ?

- As tu tenté de booster Postresql en de modifiant les paramètres par défaut de postgresql dans le fichier : C:\Program Files (x86)\PostgreSQL\8.4\data\postgresql.conf voir http://wiki.postgresql.org/wiki/Tuning_ … SQL_Server

A bientôt

nebuka · Mon 05 September 2011 11:30

Alors voici les réponse :

- Que fais-tu comme correction pour enlever les self-intersection ? Buffer(the_geom,0), cleangeometry(the_geom) ? voir http://www.kappasys.ch/pgtools/cleangeo … ometry.sql

Pour enlever les self-intersection je fais un Buffer(the_geom,0) "Bête et méchant" dans un Update ... le script donné en lien irait-il plus vite ?

- Es-tu sûr que les objets sont bien indexés ? Create index.... puis vacuum analyse. voir http://www.postgis.fr/node/187

Les indexes sont bien là ... et par contre effectivement je n'ai pas fait de VACUUM ANALYSE ...

- combien as-tu de parcelles ? combien as-tu de zones humides ? et quelle est en gros la surface moyenne d'une zone humide ?

J'ai 50 000 000 de parcelle dans la base, 1 500 zones humide (surface moyenne ?)

- As tu tenté de booster Postresql en de modifiant les paramètres par défaut de postgresql dans le fichier : C:\Program Files (x86)\PostgreSQL\8.4\data\postgresql.conf voir http://wiki.postgresql.org/wiki/Tuning_ … SQL_Server

Je suis sous CentOS pour PostGIS et j'ai modifié les lignes suivante lors de l'installation (postgresql.conf) :
shared_buffer=4GB (le serveur à 16Go de RAM)
work_mem=1GB

et c'est tout. Je ne maitrise pas spécialement le tuning, donc je n'ai touché à rien.

Si j'arrête le traitement "Buffer" qui tourne actuellement et que je fais un VACCUM ANALYSE est-ce que je peut gagner du temps de traitement ? et éventuellement utiliser le script ?

vincentp · Mon 05 September 2011 13:23

nebuka a écrit:

Alors voici les réponse :
- Que fais-tu comme correction pour enlever les self-intersection ? Buffer(the_geom,0), cleangeometry(the_geom) ? voir http://www.kappasys.ch/pgtools/cleangeo … ometry.sql

Pour enlever les self-intersection je fais un Buffer(the_geom,0) "Bête et méchant" dans un Update ... le script donné en lien irait-il plus vite ?

Pour mettre à jour une table entière de 50M d'enregistrement, notamment s'il y a des géométries, il vaut mieux faire une création de nouvelle table avec tous les champs et la géométrie modifiée, puis supprimer l'ancienne et renommer la nouvelle. Ensuite rajouter les contraintes et les indexes.

Les updates de table entière ça peut etre très long, et ça nécessite ensuite de faire du vacuum, alors que la création de table élimine pas mal de problèmes liés au MVCC.

D'autre part, si la machine est multi-processeur, il peut être intéressant de séparer la requête unique (qui va s'exécuter sur un seul processeur), en N requêtes en parallèle, car l'opération de buffer − st_buffer − est couteuse en calculs.

Mais le plus efficace reste de faire le st_buffer uniquement sur les géométries invalides, avec un update s'il n'y en a pas trop :
update parcelles set the_geom = st_buffer(the_geom, 0) where not st_isvalid(the_geom)

ppluvinet · Mon 05 September 2011 13:25

Vincent a été un peu + rapide, je crois qu'on dit à peu près la même chose dit autrement !

Même pour 50 000 000 de parcelles, je trouve que 3 jours c'est un peu beaucoup. Quelques heures me semblerait + raisonnable !
Pour optimiser la suppression des slf-intersection : j'opterais pour la fonction cleangeometry (qui fait bien + qu'enlever les self-intersection) et je mettrai une condition de type "where isValid(the_geom) = FALSE". De plus, pour optimiser le temps (mais pas la place sur le serveur !) je ne ferais pas un Update mais un select (il me semble que c'est beaucoup + rapide).
De plus, avec ce nombre important de parcelles et de zones humides, il semble indispensable d'utiliser les INDEX.

Code:

--- pour nettoyer : 
create table parcelle2 as 
select id_parcelle , the_geom 
from parcelle where isvalid(the_geom)
union
select id_parcelle ,cleangeometry( the_geom) as the_geom 
from parcelle where isvalid(the_geom) = FALSE;

CREATE INDEX idx_parcelle2_gist ON parcelle2
  USING GIST ( the_geom GIST_GEOMETRY_OPS );
 --vacuum analyse  indispensable si tu veux utiliser les index  : 
vacuum analyse parcelle2 ;

Pour le tunning, il me semble qu'il y a d'autres choses à modifier comme effective_cache_size et maintainance_work_mem.

Ensuite pour la requette d'intersection , utilise ST_INTERSECTS()

Dernière modification par ppluvinet (Mon 05 September 2011 13:28)

nebuka · Mon 05 September 2011 14:53

Merci pour vos info ppluvinet et vincentp la réparation à été effectivement plus rapide ... la j'ai en cours le vacuum analyse et après je lance quelques requêtes de test.
Si tout est OK je lance la requete principale.

MathieuB · Mon 05 September 2011 14:58

Bonjour Pascal,

la fonction marche en effet très bien! Par contre elle réalise déjà le test de validité.

Code:

[...]
-- Only process if geometry is not valid, 
-- otherwise put out without change
[...]

Un select simple sans union doit suffire.
Penser à recréer les contraintes sur la colonne the_geom.

tr · Mon 05 September 2011 15:10

Dans postgis 1.5, "REINDEX TABLE" ne prennent pas en compte les index spatiaux (au lieu de faire un VACUUM ANALYSE) ?

ppluvinet · Mon 05 September 2011 15:10

Salut Mathieu,

effectivement, le UNION n'est peut-être pas nécessaire si on utilise cleangeometry().

Pour optimiser la durée du vacuum analyse, il me semble qu'il faut augmenter la valeur par défault de maintainance_work_mem dans postgresql.conf (dans la limite du raisonnable bien sûr ! 300 Mo semble suffisant ). Il faut relancer le service ensuite.

Dernière modification par ppluvinet (Mon 05 September 2011 15:17)

nebuka · Mon 05 September 2011 15:54

Bon apparemment j'aurais toujours des erreurs dans mes données "parcelle".
Quand j'ai utilisé la requête de ppluvinet j'ai eu ça comme message :

Code:

NOTICE:  Self-intersection at or near point 642276 6.87328e+06
CONTEXT:  PL/pgSQL function "cleangeometry" line 15 at IF

pour moi je pensais qu'il avait corrigé les erreurs nan ? Ou est-ce que j'ai un soucis avec la fonction cleangeometry ?

ppluvinet · Mon 05 September 2011 16:08

Je pense qu'il n'y a pas d'erreur, juste que POSTGIS a repéré une erreur et l'a corrigée.
Pour savoir s'il y a encore des erreurs, faire

Code:

select count(*) from parcelle2 where isvalid(the_geom) = FALSE ;

Si il trouve 0, alors c'est que les erreurs ont selble-t-il étaient corrigées.

Dernière modification par ppluvinet (Mon 05 September 2011 16:08)

nebuka · Wed 14 September 2011 13:36

Bonjour,

je viens après quelques jours d'absence vous donner les "résultats" des manipulations :
- tout d'abord j'ai réussis à corriger toutes les données géographique
- j'ai pu faire tourner ma "grosses" requête ... 2.5 jours de calcul et avoir tout les résultats.

Au sujet de l'optimisation de la base, j'avais déjà fait toutes les manipulations décrites, par contre j'ai une question au sujet du multi processeurs. A priori postgres peut travailler en mutli processeurs mais 1 requêtes par processeurs.
Est-ce qu'il n'y a pas moyen d'optimiser ça au niveau des fonctions spatiales ? Car en vérifiant les processus tournant sur mon serveur (top sous centos), j'ai vu que très rarement il y avait 2 processus psql ... mais jamais 3 ou 4 (4 étant le nombre de core du serveur).

Encore merci pour vos différentes interventions qui on pu faire avancer le schmilblik.

Nicolas Ribot · Wed 14 September 2011 16:18

nebuka a écrit:

Bonjour,

je viens après quelques jours d'absence vous donner les "résultats" des manipulations :
- tout d'abord j'ai réussis à corriger toutes les données géographique
- j'ai pu faire tourner ma "grosses" requête ... 2.5 jours de calcul et avoir tout les résultats.

Au sujet de l'optimisation de la base, j'avais déjà fait toutes les manipulations décrites, par contre j'ai une question au sujet du multi processeurs. A priori postgres peut travailler en mutli processeurs mais 1 requêtes par processeurs.
Est-ce qu'il n'y a pas moyen d'optimiser ça au niveau des fonctions spatiales ? Car en vérifiant les processus tournant sur mon serveur (top sous centos), j'ai vu que très rarement il y avait 2 processus psql ... mais jamais 3 ou 4 (4 étant le nombre de core du serveur).

Encore merci pour vos différentes interventions qui on pu faire avancer le schmilblik.

Bonjour,

Postgresql n'est effectivement pas multithread: 1 requete ne peut tourner que sur un processeur.
Par contre, s'il y a plusieurs requetes, elles seront distribuées sur les differents processeurs de la machine.
Pour en profiter, une idee est de découper ses données spatialement (ou sur un autre critere) en autant de parties qu'il y a de processeurs, puis de lancer autant de requetes que de parties, pour tirer partie des différents processeurs.
A la fin du traitement, on reconstruit l'ensemble complet.

Nicolas

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 Thu 01 September 2011 15:18

Optimisation de requête PostGIS

Code:

Code:

#2 Thu 01 September 2011 15:50

Re: Optimisation de requête PostGIS

Code:

Code:

#3 Thu 01 September 2011 16:38

Re: Optimisation de requête PostGIS

Code:

#4 Thu 01 September 2011 16:52

Re: Optimisation de requête PostGIS

#5 Thu 01 September 2011 17:50

Re: Optimisation de requête PostGIS

Code:

Code:

#6 Fri 02 September 2011 09:23

Re: Optimisation de requête PostGIS

Code:

#7 Fri 02 September 2011 09:42

Re: Optimisation de requête PostGIS

#8 Fri 02 September 2011 09:43

Re: Optimisation de requête PostGIS

#9 Fri 02 September 2011 12:12

Re: Optimisation de requête PostGIS

#10 Fri 02 September 2011 12:14

Re: Optimisation de requête PostGIS

#11 Fri 02 September 2011 13:18

Re: Optimisation de requête PostGIS

Code:

Code:

Code:

#12 Mon 05 September 2011 09:41

Re: Optimisation de requête PostGIS

#13 Mon 05 September 2011 10:11

Re: Optimisation de requête PostGIS

#14 Mon 05 September 2011 11:30

Re: Optimisation de requête PostGIS

#15 Mon 05 September 2011 13:23

Re: Optimisation de requête PostGIS

#16 Mon 05 September 2011 13:25

Re: Optimisation de requête PostGIS

Code:

#17 Mon 05 September 2011 14:53

Re: Optimisation de requête PostGIS

#18 Mon 05 September 2011 14:58

Re: Optimisation de requête PostGIS

Code:

#19 Mon 05 September 2011 15:10

Re: Optimisation de requête PostGIS

#20 Mon 05 September 2011 15:10

Re: Optimisation de requête PostGIS

#21 Mon 05 September 2011 15:54

Re: Optimisation de requête PostGIS

Code:

#22 Mon 05 September 2011 16:08

Re: Optimisation de requête PostGIS

Code:

#23 Wed 14 September 2011 13:36

Re: Optimisation de requête PostGIS

#24 Wed 14 September 2011 16:18

Re: Optimisation de requête PostGIS

Pied de page des forums