Améliorer la recherche dans les données openstreetmap PGSQL

Extraire des données OSM, créer sa carte, uMap, utiliser sur un GPS ou un smartphone...
Répondre
nicolas2117

Améliorer la recherche dans les données openstreetmap PGSQL

Message par nicolas2117 » mar. avr. 02, 2013 12:21 pm

Bonjour,

Je souhaite effectuer des recherches dans les données d'openstreetmap qui sont stockées dans une base de données postgreSQL avec l'extension PostGIS.
Les recherches sont du type "Je veux tous les bars à moins de 50m et tous les restaurants à moins de 100m". J'ai absolument aucun problème pour effectuer ses recherches mais elles sont tres tres lente... (le serveur est pas super non plus :/) J'aimerais savoir si vous avez des solutions pour optimiser la vitesse d'execution de ses requetes.
J'utilise osmosis pour importer les données dans ma base avec le schema pgsnapshot simple.

Merci.

sly
Messages : 489
Inscription : sam. févr. 07, 2009 6:33 pm
Localisation : Chambéry - savoie

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par sly » mar. avr. 02, 2013 12:31 pm

Je ne suis pas spécialiste du schéma créé par osmosis, mais il me semble que lorsque tu as besoin de requêtes spatiales (distance à un point, appartenance à un polygone, etc.) le schéma de osm2pgsql est un peu plus adapté pour cela.

Mais il faudrait une vue d'ensemble des requêtes que tu as besoin de réaliser pour voir quel schéma est le plus adapté à ton besoin.

En outre, quand tu dis "lent" c'est quoi "lent" ? Et peux tu donner un exemple de requête ?

nicolas2117

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par nicolas2117 » mar. avr. 02, 2013 1:48 pm

Salut, merci de te pencher sur mon problème :)

Tout d'abord une petite précision que j'avais oublié de mentionner je travail sur un extract de Lyon (avec toutes les données).

J'avais en premier lieu utilisé osm2pgsql mais le schéma me semblait adapter surtout pour le rendu, après je suis loin d’être un expert. Je suis allé sur http://wiki.openstreetmap.org/wiki/Data ... se_Schemas et j'ai vu que pgsnapshot était marqué comme schéma pour" analysis" c'est pour ça que je l'ai utilisé. Après le schéma de osm2pgsql me semble bien plus facile à exploiter pour mes travaux :)

Lent c'est plus d'une seconde pour une requête de se genre SELECT * FROM nodes WHERE ST_DWithin(geom, 'Mon point', 50) AND tags @> hstore('amenity', 'restaurant') (je donnerai des informations plus précise quand j'aurais accès à la base de données) La requête signifie tous les restaurant dans un périmètre de 50 mètre autour de mon point.Je peux eventuellement faire des requestes du genre SELECT * FROM nodes WHERE ST_DWithin(geom, 'Mon point', 50) qui signifie tous ce qui est dans le périmètre de 50 metre de mon point.

Si tu connais mieux le format de osm2pgsql, n'hésite pas à me dire comment optimiser avec ce schéma :) Surtout que je risque d'avoir besoin de faire des recherches sur des polygon et avec pgsnapshot ca risque d'être plus compliqué...

Merci.

sly
Messages : 489
Inscription : sam. févr. 07, 2009 6:33 pm
Localisation : Chambéry - savoie

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par sly » mar. avr. 02, 2013 3:13 pm

nicolas2117 a écrit : Tout d'abord une petite précision que j'avais oublié de mentionner je travail sur un extract de Lyon (avec toutes les données).
Donc ça devrait être encore plus rapide. 1 seconde c'est largement trop, tu devrais obtenir plutôt un truc du genre entre 5ms et 20ms voire moins si tout peut être mis en cache RAM.
Lent c'est plus d'une seconde pour une requête de se genre SELECT * FROM nodes WHERE ST_DWithin(geom, 'Mon point', 50) AND tags @> hstore('amenity', 'restaurant')
J'ai fais la même chose sur ma base de donnée qui contient toute l'europe avec le schéma osm2pgsql :

Code : Tout sélectionner

gis=# SELECT name,amenity FROM europe_point WHERE  ST_Within(way,st_buffer(st_transform(st_geomfromtext('POINT(4.85 45.759)',4326),900913),300)) and amenity='restaurant';
           name            |  amenity
---------------------------+------------
 Mezziano                  | restaurant
 Soleil Levant             | restaurant
 Le Karachi                | restaurant
 A Deux Pâtes de Chez Vous | restaurant
 La Squadra                | restaurant
 Au Palais de Fez          | restaurant
 Côté Jardin               | restaurant
 Les Compères              | restaurant
 Le Bistrot du Palais      | restaurant
                           | restaurant
 Le P'tit Bouchon          | restaurant
 Trianon                   | restaurant
(12 lignes)

Temps : 1,152 ms
Juste histoire de montrer qu'il y a un facteur 1000 en vitesse ;-)
Donc, il doit clairement manquer un indexe dans ta base, ou le schéma n'est pas fait pour.
(a noter que pour réaliser cette requête je n'ai utilisé aucun indexe en plus de ceux "de base" créé par osm2pgsql ni aucun tunning particulier, j'ai juste "lancé la requête", en transformant toutefois de lat/lon vers mercator sphérique car ma base n'est pas en lat/lon mais si tu préfères l'avoir en lat/lon l'option -l de osm2pgsql est pour toi)

Si tu connais mieux le format de osm2pgsql, n'hésite pas à me dire comment optimiser avec ce schéma :) Surtout que je risque d'avoir besoin de faire des recherches sur des polygon et avec pgsnapshot ca risque d'être plus compliqué...
Merci.
Si tu as un jour besoin de polygones, comme par exemple tous les restaurants qui ne sont pas rentrés comme point mais comme polygones ou si tu cherches des restaurants dans un arrondissement de lyon, alors là, plus de doutes : osm2pgsql.

nicolas2117

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par nicolas2117 » mar. avr. 02, 2013 3:35 pm

Je vais essayer de tester tout ca ce soir pour comparer. Tu as quoi comme machine?

sly
Messages : 489
Inscription : sam. févr. 07, 2009 6:33 pm
Localisation : Chambéry - savoie

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par sly » mar. avr. 02, 2013 3:42 pm

quad core 3.1GHZ avec 8Go de RAM et RAID 5 sur disques magnétiques.

nicolas2117

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par nicolas2117 » mer. avr. 03, 2013 3:40 pm

Alors j'ai importé un extract de la ville de lyon avec osm2pgsql (j'ai compilé la derniere version de osm2pgsql)

voici le log de l'import avec la commande, le style est le style par défaut. La base de données fait 450Mo

Code : Tout sélectionner

nico@brooklyn:~/map$ osm2pgsql/osm2pgsql -d "lyon2" -U nico -W -S ./default.style lyon.osm.bz2 
osm2pgsql SVN version 0.81.0 (64bit id space)

Password:
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE:  table "planet_osm_line" does not exist, skipping
NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE:  table "planet_osm_polygon" does not exist, skipping
NOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping
Setting up table: planet_osm_roads
NOTICE:  table "planet_osm_roads" does not exist, skipping
NOTICE:  table "planet_osm_roads_tmp" does not exist, skipping
Allocating memory for dense node cache
Allocating dense node cache in one big chunk
Allocating memory for sparse node cache
Sharing dense sparse
Node-cache: cache=800MB, maxblocks=102401*8192, allocation method=3
Mid: Ram, scale=100

Reading in file: lyon.osm.bz2
Processing: Node(9540k 40.4k/s) Way(1421k 7.85k/s) Relation(6161 154.03/s)  parse time: 457s

Node stats: total(9540273), max(2210538916) in 236s
Way stats: total(1421232), max(211045933) in 181s
Relation stats: total(6161), max(2827742) in 40s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Writing way (1421k)
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Writing relation (6161)
node cache: stored: 9540273(100.00%), storage efficiency: 54.62% (dense blocks: 4373, sparse nodes: 6494262), hit rate: 99.23%
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_point
Analyzing planet_osm_roads finished
Analyzing planet_osm_point finished
Analyzing planet_osm_line finished
Analyzing planet_osm_polygon finished
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on  planet_osm_point
Creating indexes on  planet_osm_point finished
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on  planet_osm_roads
All indexes on  planet_osm_point created  in 35s
Completed planet_osm_point
Creating indexes on  planet_osm_roads finished
All indexes on  planet_osm_roads created  in 39s
Completed planet_osm_roads
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on  planet_osm_line
Creating indexes on  planet_osm_line finished
All indexes on  planet_osm_line created  in 74s
Completed planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on  planet_osm_polygon
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 207s
Completed planet_osm_polygon

Osm2pgsql took 1111s overall

La meme requete que la tienne sur planet_osm_node

Code : Tout sélectionner

name	amenity
A Deux Pâtes de Chez Vous	restaurant
Au Palais de Fez	restaurant
Les Compères	restaurant
La Squadra	restaurant
Côté Jardin	restaurant
Le Bistrot du Palais	restaurant
Mezziano	restaurant
Soleil Levant	restaurant
Le Karachi	restaurant
9 ligne(s)

Temps d'exécution total : 429.623 ms

Requête SQL exécutée.
J'ai beaucoup moins de données mais j'ai un résultat pas loin de 400 fois moins rapide...
La meme requete apres (j'imagine en cache?)

Code : Tout sélectionner

name	amenity
A Deux Pâtes de Chez Vous	restaurant
Au Palais de Fez	restaurant
Les Compères	restaurant
La Squadra	restaurant
Côté Jardin	restaurant
Le Bistrot du Palais	restaurant
Mezziano	restaurant
Soleil Levant	restaurant
Le Karachi	restaurant
9 ligne(s)

Temps d'exécution total : 23.504 ms

Requête SQL exécutée.
La configuration du serveur http://www.online.net/fr/serveur-dedie/dedibox-sc c'est une machine que j'avais de disponible pour les tests c'est pas pour l'exploitation ^^ elle a pas grand chose dans le ventre :'( mais ca me semble pas expliquer une si grosse différence...

sly
Messages : 489
Inscription : sam. févr. 07, 2009 6:33 pm
Localisation : Chambéry - savoie

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par sly » jeu. avr. 04, 2013 8:33 pm

nicolas2117 a écrit : 9 ligne(s)
Pas un peu bizarre ça, moi j'en ai 12, tu as bien tapé la même requête que moi ?
Temps d'exécution total : 429.623 ms
Je viens de tenter à l'instant "à froid" sur ma base :
665ms
(donc cohérent avec ton résultat)

Puis, une deuxième fois : 1ms
La configuration du serveur http://www.online.net/fr/serveur-dedie/dedibox-sc c'est une machine que j'avais de disponible pour les tests c'est pas pour l'exploitation ^^ elle a pas grand chose dans le ventre :'( mais ca me semble pas expliquer une si grosse différence...
entre 1ms et 20ms, je pense que c'est la différence de bécane qui explique. Pour le "à froid" on voit qu'on est relativement proche et que ce qui change c'est la vitesse disque et si ma ou ta machine faisait quelque chose pendant ce temps

nicolas2117

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par nicolas2117 » jeu. avr. 04, 2013 9:38 pm

oui c'est la même requête (enfin sur planet_osm_point) mais je me demande si l'extract de ma base de données est bien. J'avais déjà réaliser cette opération ma base de données avait une taille de 2Go la elle a une taille de 450Mo :/ (après c'est peut être seulement la zone qui était plus grande.)
Est ce que tu sais ce que c'est le à froid? Ce que j'aimerais savoir c'est si je dois m'attendre a 450ms pour chaque requetes ou a 23ms.

Je regarde actuellement cette doc http://wiki.postgresql.org/wiki/Tuning_ ... SQL_Server pour essayer d'augmenter les performances.

Pourrais tu essayer cette requête SELECT * FROM europe_polygon WHERE ST_Within(way,st_buffer(st_transform(st_geomfromtext('POINT(4.85 45.759)',4326),900913),300));
Qui retourne tous les polygones dans un rayon de 300m sur ta base de données? J'aimerais voir combien ça met de temps pour l'europe (si tu as la base mondiale n'hesite pas a tester dessus :D)

Merci de ton aide, bonne soirée;)

sly
Messages : 489
Inscription : sam. févr. 07, 2009 6:33 pm
Localisation : Chambéry - savoie

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par sly » ven. avr. 05, 2013 2:04 am

nicolas2117 a écrit : Est ce que tu sais ce que c'est le à froid?
Tu veux dire : "d'où ça vient / a quoi c'est dû ?"
Je vois en gros ce que c'est sans non plus être expert en la question. C'est lié au noyau linux et à sa manière de garder en mémoire des blocs de fichiers précédemment accédés afin d'en accélérer l'accès. Postgresql utilisant des fichiers, il est soumis à cette différence "à froid / à chaud"
Ce que j'aimerais savoir c'est si je dois m'attendre a 450ms pour chaque requêtes ou a 23ms.
Ça dépend de la RAM dont tu disposes et des données régulièrement accédées. Si tu as plus de RAM que tous les fichiers de tous tes disques réunis, alors tu aura tout le temps 23ms car tout tiendra en cache.
En moyenne, je considère, d'expérience, que si la taille de ta base de donnée est inférieure à la taille de ta RAM alors tu sera quasiment tout le temps à la vitesse max.
Pourrais tu essayer cette requête SELECT * FROM europe_polygon WHERE ST_Within(way,st_buffer(st_transform(st_geomfromtext('POINT(4.85 45.759)',4326),900913),300));
~17ms à chaud
Qui retourne tous les polygones dans un rayon de 300m sur ta base de données?
tous les polygones.... entièrement à l'intérieur du cercle de rayon de 300m
J'aimerais voir combien ça met de temps pour l'europe (si tu as la base mondiale n'hesite pas a tester dessus :D)
Vu les indexes utilisés, et vu cette requête, je pense que peut importe la couverture de la base ça donnera à peu prêt le même résultat que tu disposes de monde/europe/france ou rhône (ça dépendra surtout si les données sont en cache, ou pas)

nicolas2117

Re: Améliorer la recherche dans les données openstreetmap PGSQL

Message par nicolas2117 » ven. avr. 05, 2013 7:57 am

Ok. Merci Sly pour l'aide ;)

Répondre

Qui est en ligne ?

Utilisateurs parcourant ce forum : Aucun utilisateur inscrit et 3 invités