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"

 Planete
 Extraction par continent, pays ou région

Exemples

Import d'un fichier osm

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

-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 de node
select count(*) from planet_osm_point; 

comptabilisation du nombre de boulangerie

select count(*) from planet_osm_point where shop='bakery';
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
 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

Utilsation 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 Metropole: 2154,
Guadeloupe (971): 32620, Martinique (972): 32620, Guyanne(973): 32622, Reunion (974): 2975, Mayotte (976): 32738

utiliser les coordonnées wgs 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 de josm (d'osm)

inconvénient: les distances ne sont plus en metre mais en degré 1e-3 correspond a peu près 111m

exemple trouver les erreurs de géometrie sur les delimitations 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.

transformer la géometrie pour calculer le nombre de km de voie en tenant compte des sens uniques

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

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

Detecter des géométrie avec "trop" de nodes

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)>4 and
  (nbpt-ptsimple)>200 
order by ((nbpt/ptsimple)*(nbpt-ptsimple)) desc


Ensuite

Lancer des requetes en parallele

creer 2 fichiers csv des resulats des 2 requetes puis, quand les 2 requetes 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

documentation postgis

documentation sql

requete from

convertir un json avec multigéometrie pour Qgis =

  • # utiliser ogr2ogr pour ecrire le json dans la base postgresql/postgis

ogr2ogr -f "PostgreSQL" PG:"dbname=mydbpostgis user=didier" "osmose-cover.json" #table créé = 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;

  • acceder a la nouvelle table par Qgis/database