OSMRead
Signatures
OSMRead(VARCHAR path);
OSMRead(VARCHAR path, VARCHAR tableName);Description
Reads a OSM file from path and creates several tables prefixed by tableName representing the file’s contents.
11 tables are produced.
- (1) table_prefix + _tag : table that contains all tag keys listed in the OSM file -> ID_TAG SERIAL PRIMARY KEY, TAG_KEY VARCHAR UNIQUE.
- (2) table_prefix + _node : table that contains all nodes -> ID_NODE BIGINT PRIMARY KEY, THE_GEOM POINT,ELE DOUBLE PRECISION,USER_NAME VARCHAR,UID BIGINT,VISIBLE BOOLEAN,VERSION INTEGER,CHANGESET INTEGER,LAST_UPDATE TIMESTAMP, NAME VARCHAR.
- (3) table_prefix + _node_tag : table that contains a list of tag keys (TAG_VALUE) for each node -> ID_NODE BIGINT, ID_TAG BIGINT,TAG_VALUE VARCHAR.
- (4) table_prefix + _way : table that contains all ways -> ID_WAY BIGINT PRIMARY KEY, USER_NAME VARCHAR, UID BIGINT, VISIBLE BOOLEAN, VERSION INTEGER, CHANGESET INTEGER, LAST_UPDATE TIMESTAMP, NAME VARCHAR.
- (5) table_prefix + _way_tag : table that contains a list of tag keys (TAG_VALUE) for each way -> ID_WAY BIGINT, ID_TAG BIGINT, VALUE VARCHAR.
- (6) table_prefix + _way_node : table that contains the list of nodes used to represent a way -> ID_WAY BIGINT, ID_NODE BIGINT, NODE_ORDER INT.
- (7) table_prefix + _relation: table that contains all relations -> ID_RELATION BIGINT PRIMARY KEY,USER_NAME VARCHAR, UID BIGINT,VISIBLE BOOLEAN,VERSION INTEGER,CHANGESET INTEGER,LAST_UPDATE TIMESTAMP.
- (8) table_prefix + _relation_tag : table that contains a list of tag keys (TAG_VALUE) for each relation -> ID_RELATION BIGINT, ID_TAG BIGINT, VALUE VARCHAR.
- (9) table_prefix + _node_member : table that stores all nodes that are referenced into a relation -> ID_RELATION BIGINT,ID_NODE BIGINT, ROLE VARCHAR, NODE_ORDER INT.
- (10) table_prefix + _way_member : table that stores all ways that are referenced into a relation -> ID_RELATION BIGINT, ID_WAY BIGINT, ROLE VARCHAR, WAY_ORDER INT.
- (11) table_prefix + _relation_member : table that stores all relations that are referenced into a relation -> ID_RELATION BIGINT, ID_SUB_RELATION BIGINT, ROLE VARCHAR, RELATION_ORDER INT.
By default, the tableName is the filename given in path without the extension.
The OSM driver supports the following zipped extension : osm.gz and osm.bz2.
Examples
Read OSM file:
-- Takes the table name from the filename
CALL OSMRead('/home/user/bretagne.osm');
-- Uses the given table name
CALL OSMRead('/home/user/bretagne.osm', 'OSM_BRETAGNE');
-- Uses a zipped file
CALL OSMRead('/home/user/bretagne.osm.gz', 'OSM_BRETAGNE');
-- Uses another zipped file
CALL OSMRead('/home/user/bretagne.osm.bz2', 'OSM_BRETAGNE');Based on the created tables, the user can build other OSM data.
H2GIS extract buildings:
DROP TABLE IF EXISTS OSM_BRETAGNE_BUILDINGS;
CREATE TABLE OSM_BRETAGNE_BUILDINGS(ID_WAY BIGINT PRIMARY KEY) AS SELECT DISTINCT ID_WAY FROM OSM_BRETAGNE_WAY_TAG WT, OSM_BRETAGNE_TAG T WHERE WT.ID_TAG = T.ID_TAG AND T.TAG_KEY IN ('building');
DROP TABLE IF EXISTS OSM_BRETAGNE_BUILDINGS_GEOM;
CREATE TABLE OSM_BRETAGNE_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 OSM_BRETAGNE_NODE N,OSM_BRETAGNE_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 OSM_BRETAGNE_WAY W,OSM_BRETAGNE_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;PostGIS extract buildings:
DROP TABLE IF EXISTS OSM_BRETAGNE_BUILDINGS;
CREATE TABLE OSM_BRETAGNE_BUILDINGS AS SELECT DISTINCT ID_WAY FROM OSM_BRETAGNE_WAY_TAG WT, OSM_BRETAGNE_TAG T WHERE WT.ID_TAG = T.ID_TAG AND T.TAG_KEY IN ('building');
ALTER TABLE OSM_BRETAGNE_BUILDINGS ADD PRIMARY KEY(ID_WAY);
DROP TABLE IF EXISTS OSM_BRETAGNE_BUILDINGS_GEOM;
CREATE TABLE OSM_BRETAGNE_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 OSM_BRETAGNE_NODE N,OSM_BRETAGNE_WAY_NODE WN WHERE N.ID_NODE = WN.ID_NODE ORDER BY WN.NODE_ORDER) ORDEREDNODES WHERE ORDEREDNODES.IDWAY = W.ID_WAY) THE_GEOM ,W.ID_WAY FROM OSM_BRETAGNE_WAY W,OSM_BRETAGNE_BUILDINGS B WHERE W.ID_WAY = B.ID_WAY) GEOM_TABLE WHERE ARRAY_LENGTH(THE_GEOM, 1) > 2 AND ST_EQUALS(THE_GEOM[1],THE_GEOM[ARRAY_LENGTH(THE_GEOM, 1)]);