User:Didier2020/Utilisation osm2pgsql - postgis

From OpenStreetMap Wiki
Jump to: navigation, search

Memento pour l'utilisation d'une base de données postgresql/postgis. Debian 6.0

osm2pgsql crée son schéma de base de données : schéma

Installation

en tant que root:

 aptitude install osm2pgsql postgis postgresql-8.4-postgis postgresql-contrib
 su - postgres
 createuser didier

en ajoutant l'option -s, l'utilisateur aura les droits de super-user (creation de table ...)

Configuration

en tant que didier

 createdb -E UTF8 osmgis
 createlang plpgsql osmgis
 psql -d osmgis -f /usr/share/postgresql/8.4/contrib/hstore.sql
 psql -d osmgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
 psql -d osmgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

osmgis étant le nom de la base de donnée

Schéma base de données

La base osmgis contient les tables ci-dessous après l'import par osm2pgsql

  • planet_osm_line: contient tous les ways
  • planet_osm_point: contient tous les nodes ayant des tags (les tags définis dans le default.style d'osm2pgsql)
  • planet_osm_polygon: contient tous les polygones (ways fermés naturellement ou par une relation)
  • planet_osm_roads: contient les ways qualifiés comme routes ...

Chacune de ces tables contient plusieurs champs:

  • osm_id: la référence du node ou way, contient tous les ways. Si l'id est négatif, cela correspond a une relation (-1234 => relation 1234)
  • un champs par tag spécifié dans le fichier default.style
  • z_order: valeur calculée automatiquement lors de l'upload. Correspond a...
  • way_area: valeur calculée automatiquement lors de l'upload. Correspond a...
  • way: valeur calculée automatiquement lors de l'upload. Correspond a...

Récupération des données

Fichiers "osm.bz2" ou ".osm.pbf"

Exemples

Import d'un fichier osm

osm2pgsql -U didier -d osmgis ile-de-france.osm

où -U spécifie l'utilisateur et -d spécifie la base à utiliser

osm2pgsql applique un filtre sur les tags qui seront dans la base de données.

Le style utilisé par défaut se trouve en /usr/share/osm2pgsql/default.style En cas d'utilisation d'un fichier style personalisé : l'option -S

Ce fichier "dit" à osm2pgsql comment convertir les données dans la base de données : pour les nodes et way, tags qui seront stockés dans la base.

Utilisation détaillée:

 man osm2pgsql
 osm2pgsql -h

Utiliser les données

en ligne de commande avec pgsql

lancer psql en utilisant la base osmgis

psql osmgis
afficher les tables
\d
comptabilisation du nombre total de noeuds dans la base
 SELECT COUNT(*)
 FROM planet_osm_point;
Comptabilisation du nombre de boulangeries dans la base
 SELECT COUNT(*)
 FROM planet_osm_point
 WHERE shop = 'bakery';

Noter que cela oublie de compter les boulangeries marquées sur des polygones ou multipolygones fermés (des bâtiments par exemple).

comptabilisation du nombre de boulangerie par commune
 SELECT 
   a.osm_id, a.name, count(*)
 FROM
   planet_osm_polygon AS a,
   planet_osm_point AS b 
 WHERE
   a.admin_level = '8' AND
   b.shop = 'bakery' AND
   ST_contains(a.way, b.way) = True
 GROUP BY
   a.osm_id, a.name;

après chaque ligne, il ne se passe rien ... sauf quand la ligne se termine par ";"

utilisation de la colonne tags

Nombre de point qui on le tag addr:housenumber

 SELECT
   COUNT(tags)
 FROM
   planet_osm_nodes
 WHERE
   tags @> '{addr:housenumber}'::text[]
pour quitter
\q

utilisation directe en ligne de commande

l'option "-c" permet d'effectuer la requête qui est entre parenthèses

psql -d osmgis -c "select count(*) from planet_osm_point where shop='bakery';"

l'option "-f" permet d'effectuer la requête qui est écrite dans monfichier.sql

psql -d osmgis -f monfichier.sql

Sauvegarder les résulats

sauvegarde simple

psql -d osmgis -c "select count(*) from planet_osm_point where shop='bakery';" > jesauve.txt

sauvegarde au format csv

psql -q -F\; -A --pset footer -d osmgis -f marequete.sql -o fichiersortie.csv

en utilisant pgadmin3

Pgadmin3 est un client graphique. Il dispose aussi d'un éditeur de requêtes (un plus quand on débute en sql)

On peut aussi sauvegarder ses requêtes en fichier. Attention toutefois, ces fichiers ne peuvent pas être utilisés directement par psql (il faut préalablement enlever un "." en début du fichier)

Utilisation Geofla

Geofla est un produit de l'IGN qui est sous licence ouverte, au format SHP: description des unités administratives régions,départements, communes,...

L'exemple ci après concerne les communes (les limites "supérieures", département régions sont déjà dans osm avec plus de précision)

Conversion du format SHP Lambert en Mercator

ogr2ogr -f "ESRI Shapefile" -s_srs EPSG:2154 -t_srs EPSG:900913 transforme.shp COMMUNE.SHP

Transformation du SHP en données utilisables dans postgis

shp2pgsql -s 900913 transforme commune > commune.sql

Import postgis

psql -d osmgis -f commune.sql

Utilisation de l'EPSG:900913 pour que les données importées avec osm2pgsql aient la même géométrie s_srs à utiliser respectivement:

  • France métropolitaine: EPSG:2154,
  • Guadeloupe (971) et Martinique (972): EPSG:32620
  • Guyane (973): EPSG:32622
  • La Réunion (974): EPSG:2975
  • Mayotte (976): EPSG:32738

Utiliser les coordonnées WGS84 a la place de l'EPSG:900913

osm2pgsql -l -U didier -d osmgis ile-de-france.osm
  • Avantage : les coordonnées des géométries sont dans le système de coordonnées cartographique utilisé dans le rendu OSM (cette projection est conforme et respecte à peu près les formes et les angles pour des objets pas trop grands)
  • Inconvénient : les distances ne sont plus en mètres (mesurés sur le cône de la projection géographique utilisée dans chaque région) mais en degrés : 1 millième de degré en projection WGS84 correspond à peu près à 111 m (aux latitudes moyennes de la métropole), mais cette distance s’accroît vers l'équateur (par exemple en Guyane ou à Mayotte) et diminue vers les zones polaires (les surfaces ne sont donc pas respectées non plus et ne sont pas comparables même pour de petits objets de même taille selon leur latitude : on ne peut pas comparer directement la largeur d'une voie de circulation en Guyane avec celle d'une voie à Paris, ni mesurer une densité d'objets par kilomètre carré sans tenir compte des latitudes).

Exemple : trouver les erreurs de géométrie sur les délimitations administratives

 SELECT 
   osm_id, ST_IsValidReason(way)
 FROM 
   planet_osm_polygon
 WHERE 
   st_isvalid(way) = 'f' AND
   admin_level IS NOT NULL AND
   osm_id < 0

IsValidReason affichera le cas échéants les coordonnées en WGS84. Pour ce type de requêtes, le système de coordonnées utilisé n'a pas d'importance puisqu'on n'y effectue aucune mesure de distance.

Exemple : transformer la géométrie pour calculer le nombre de km de voies en tenant compte des sens uniques

Cette requête est partiellement fausse car il n'y a pas de tag pour distinguer une route en sens unique, d'une route a chaussée séparée. Ici on doit transformer la géométrie dans un système de coordonnées géographiques (ici EPSG:2154 pour la France métropolitaine) pour mesurer les distances en mètres.

 SELECT highway, ROUND(SUM(coef * kmway)) AS kilometers
 FROM
   ( SELECT
       highway,
       (SELECT CASE WHEN oneway IN ('yes','1','-1') THEN 0.5 ELSE 1 END) AS coef,
       ST_Length(ST_Transform(way, 2154)) / 1000 AS kmway
     FROM
        planet_osm_line
     WHERE
       highway is not null AND
       highway IN
         (
           ('motorway'),
           ('motorway_link'),
           ('trunk'),
           ('trunk_link'),
           ('primary'),
           ('primary_link'),
           ('secondary'),
           ('secondary_link'),
           ('tertiary'),
           ('tertiary_link'),
           ('unclassified'),
           ('residential'),
           ('track'),
           ('path')
         ) 
   ) AS tt
 GROUP BY
   highway

Détecter des géométries avec "trop" de nodes

Le paramètre de simplification est exprimé ici dans le même système de coordonnées que les données dans la base, donc en degrés, il correspond ici à une simplification à un cent-millième de degré, soit un peu plus d'un mètre aux latitudes moyenne de la France métropolitaine. La requête suivante détermine pour chaque chemin comprenant beaucoup de nœuds (plus de 200) un chemin polygonal topologiquement équivalent (préservant les intersections sans en ajouter) et liste ceux qui compte 4 fois plus de nœuds que nécessaire avec le chemin simplifié pour avoir une précision de tracé de l'ordre du mètre.

 SELECT osm_id
 FROM
   (SELECT 
     osm_id,
     ST_NPoints(way) AS nbpt,
     ST_NPoints(ST_SimplifyPreserveTopology(way, 0.000010)) AS ptsimple
   FROM planet_osm_polygon
   WHERE ST_NPoints(way) > 200 AND osm_id > 0
   ) AS he
 WHERE
   (nbpt - ptsimple) > 200 AND
   (nbpt / ptsimple) > 4
 ORDER BY ((nbpt - ptsimple) * (nbpt / ptsimple)) DESC

Pour ce type de requête, il n'est pas vraiment nécessaire de convertir la géométrie en coordonnées géographiques conformes en mètres, la liste obtenue est indicative étant donné le critère "flou" utilisé et la tolérance donnée ; on peut travailler en degrés directement (et cela fonctionne tant qu'on n'a pas à traiter des objets près des pôles: le but est aussi de simplifier le rendu cartographique tel qu'il est généré dans les "tuiles" d'OSM là où un meilleure précision n'est pas absolument nécessaire pour les chemins les plus longs. Cependant pour les petits objets (batiments individuels, clotures, ponts) on peut faire cette conversion comme précédemment.

Ensuite

Lancer des requêtes en parallèle

Créer 2 fichiers csv des résultats des 2 requêtes puis, quand les 2 requêtes sont terminées, lancer un script python de traitement x voulu

Dans un script bash :

 psql -F\; -A --pset footer -d osm -f ana1.sql -o ana1.csv &
 psql -F\; -A --pset footer -d osm -f ana2.sql -o ana2.csv &
 wait
 python traiterfinal.py

Il faut approfondir le language SQL et PostGIS

Convertir un fichier geojson avec multigéométrie pour Qgis

  • Utiliser ogr2ogr pour écrire le json dans la base PostgreSQL/PostGIS :
ogr2ogr -f "PostgreSQL" PG:"dbname=mydbpostgis user=didier" "osmose-cover.json"
# La table créée est nommée "ogrgeojson"
  • Créer une nouvelle table sans les multigéométries:
 CREATE TABLE ogrgeojson_2 AS 
 SELECT ogc_fid, the_geom
 FROM (
  SELECT
    ogc_fid,
    ST_GeometryN(wkb_geometry, generate_series(1, ST_NumGeometries(wkb_geometry))) AS the_geom
  FROM ogrgeojson
 ) AS foo;
  • accéder à la nouvelle table "ogrgeojson_2' par Qgis/database