3.4 Extract OSM data - orbisgis/h2gis GitHub Wiki

Introduction

This tutorial describes how extract and transform OSM data using H2GIS functions. The method is divided in three steps. Firstly a OSM file is extracted using a bounding box. Secondly the osm file is parsed and loaded in a H2GIS database. Then the OSM data is processed in SQL to build two GIS tables : buildings and roads.

Extract OSM file

To extract an OSM file using a bounding box runs the following command.

call ST_OSMDOWNLOADER('POLYGON ((-2.77 47.64, -2.77 47.65, -2.76 47.65, -2.76 47.64, -2.77 47.64)) '::geometry,'/tmp/vannes.osm')

where the input geometry represents the bounding box in lat/lon and /tmp/vannes.osm the output file.

Load OSM file

Thanks to the OSMRead function that offers the mechanism to parse the OSM file. The OMS file is stored in 11 tables. More details are available on h2gis.org website : OSMRead function

Execute the command :

call OSMRead('/tmp/vannes.osm', 'vannes');

Where /tmp/vannes.osm is the extracted file and vannes a prefix for 11 tables created.

Build GIS tables

In order to be "GIS readable" the OSM tables must be transformed.Indeed, in OSM there are no layers in the traditional GIS sense. All features are in one big database organized in a pseudo graph model. The database contains tables for each Element type (nodes, ways, relations). Please consult OMS database for more information.

We propose two scripts. The first one to create a table that contains buildings as polygons and the second one to extract a table of roads represented with lines. Note thats these scripts are for demonstration purpose. Feel free to adapt them to extract more details.

Buildings table

DROP TABLE IF EXISTS VANNES_BUILDINGS;
CREATE TABLE VANNES_BUILDINGS(ID_WAY BIGINT PRIMARY KEY) AS SELECT DISTINCT ID_WAY 
FROM VANNES_WAY_TAG WT, VANNES_TAG T 
WHERE WT.ID_TAG = T.ID_TAG AND T.TAG_KEY IN ('building');
DROP TABLE IF EXISTS VANNES_BUILDINGS_GEOM;

CREATE TABLE VANNES_BUILDINGS_GEOM AS SELECT ID_WAY, 
ST_MAKEPOLYGON(ST_MAKELINE(THE_GEOM)) THE_GEOM FROM (SELECT (SELECT 
ST_ACCUM(THE_GEOM) THE_GEOM FROM (SELECT N.ID_NODE, N.THE_GEOM,WN.ID_WAY IDWAY FROM 
VANNES_NODE N,VANNES_WAY_NODE WN WHERE N.ID_NODE = WN.ID_NODE ORDER BY 
WN.NODE_ORDER) WHERE  IDWAY = W.ID_WAY) THE_GEOM ,W.ID_WAY 
FROM VANNES_WAY W,VANNES_BUILDINGS B 
WHERE W.ID_WAY = B.ID_WAY) GEOM_TABLE WHERE ST_GEOMETRYN(THE_GEOM,1) = 
ST_GEOMETRYN(THE_GEOM, ST_NUMGEOMETRIES(THE_GEOM)) AND ST_NUMGEOMETRIES(THE_GEOM) > 
2;
DROP TABLE VANNES_BUILDINGS;

Roads table

DROP TABLE IF EXISTS VANNES_ROADS;
CREATE TABLE VANNES_ROADS(ID_WAY BIGINT PRIMARY KEY) AS SELECT DISTINCT ID_WAY FROM VANNES_WAY_TAG WT, 
VANNES_TAG T WHERE WT.ID_TAG = T.ID_TAG AND T.ID_TAG =13;
DROP TABLE IF EXISTS VANNES_ROADS_GEOM;
CREATE TABLE VANNES_ROADS_GEOM AS SELECT ID_WAY, ST_MAKELINE(THE_GEOM) THE_GEOM FROM (SELECT (SELECT 
ST_ACCUM(THE_GEOM) THE_GEOM FROM (SELECT N.ID_NODE, N.THE_GEOM,WN.ID_WAY IDWAY FROM VANNES_NODE 
N,VANNES_WAY_NODE WN WHERE N.ID_NODE = WN.ID_NODE ORDER BY WN.NODE_ORDER) WHERE  IDWAY = W.ID_WAY) 
THE_GEOM ,W.ID_WAY FROM VANNES_WAY W,VANNES_ROADS B WHERE W.ID_WAY = B.ID_WAY) GEOM_TABLE;
DROP TABLE VANNES_ROADS;