User:Didier2020/Utilisation osm2pgsql - postgis
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