Aktuelles

ISCOM erhält Zuschlag für Förderung im Rahmen der Initialförderung

(19-11-2013)

ISCOM bewarb sich dieses Jahr mit zwei Projektideen im Rahmen der hochschulinternen Initialförderungsrunde. Zum einen stellte Prof. Dr. Melo das Projekt...

Weiterlesen …

Erfolgreiche Teilnahme am Dillinger Firmenlauf

(19-07-2013)

Erstmalig stellte ISCOM beim diesjährigen Dillinger Firmenlauf am 27. Juni 2013 eine eigene Laufmannschaft zusammen. Gemäß dem Motto der Hochschule "wer laufend denkt, bewegt sich"...

Weiterlesen …

Sie sind hier: ISCOM Downloads

osm-import: Convert OSM data to CSV for database import

Martin Dirichs
martin.dirichs@htwsaar.de
Version 2.1, May 2013

Overview

osm-import is a conversion tool for OpenStreetMap data that prepares large OSM input files to be imported into a PostGIS database while preserving the topological information of all street data.

Originally this tool was intended to prepare OSM data for use with pgRouting and it is probably still usable for this purpose. However, pgRouting compatibility is no longer a design goal. If you want to use it with pgRouting, expect to make some changes to the output of osm-import, such as renaming of table columns etc. Any feedback on this is appreciated.

This file is written as AsciiDoc file.

License

Copyright © 2013 HTW Saarland.

This program is free software under the conditions of the GNU General Public License as found in http://www.gnu.org/licenses/gpl.html (see here for a German translation).

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

The following third party libraries are used by osm-import which have their own license conditions:

  • Java Topology Suite 1.12 by Martin Davis (LGPL)

  • fastutil 5.1.5 by Sebastiano Vigna (LGPL)

  • Spatial Index Library 0442b by Marios Hadjieleftheriou (LGPL); note that this library had to be modified slightly for use with osm-import in order to support larger data sets

  • Apache Commons Compress by Apache Software Foundation (Apache License 2.0)

Prerequisites

  • Download an OpenStreetMap data file, for example from http://download.geofabrik.de/osm. You can leave it compressed; osm-import can handle compressed files in Zip, GZip or BZip2 formats directly.

  • Have a current Java Standard Edition installed, obtainable from http://www.oracle.com/technetwork/java/javase/downloads/index.html. Version 1.5 or higher is required. If you also want to build the source, you need a Java Development Kit (JDK). Otherwise, a runtime environment (JRE) is sufficient.

  • Download and install Apache Ant.

  • Copy file osm-import-sample.properties to osm-import.properties and adapt it to have the correct paths for the OSM file to use as input as well as the intermediate files and the resulting files. An exemplary configuration can be found in osm-import-sample.properties.

Running osm-import

The conversion from OSM XML data to a CSV file containing only the ways is done in three steps. Provided that file osm-import.properties contains the correct settings, run the following three commands from the home directory of osm-import:

ant compress
ant convert
ant finish

You probably want to have a look at the Ant configuration file build.xml before. Here, you can add switches for the Java Virtual Machine (JVM) and command line options to the three osm-import steps. The most important setting for the JVM is the maximum memory for it to use. osm-import has been designed to work on 32 bit machines with 2 GB of memory. For these machines, the default setting in the build.xml should be fine with 1400M. It might be possible to tune it a bit to the higher, say 1500M or 1550M but not much more than that. For 64 bit machines, you can set it much higher, depending on the amount of memory available in your system. This is especially beneficial for the second step, Convert, when used together with the command line option that specifies how many nodes may be processed at a time.

All command line options for Compress, Convert and Finish are documented in the build.xml file. See there for more details.

Importing into a PostGIS database

One result of running the three conversion steps of osm-import is a CSV file with all highway data in a form that preserves the topology of the ways. This CSV file (actually a tabulator separated file) can then be read into a PostGIS database. This is done with a sequence of SQL statements that create the table, import the raw data, convert the geometry data and create index keys on the table. Here is a sample database session to import the CSV data:

-- You may use a different name for the way table
\set way way
create table :way (
  way_id integer not null,
  osm_id integer not null,
  type varchar(32) not null,
  oneway boolean not null,
  lanes smallint not null,
  name varchar(256),
  maxspeed float not null,
  maxweight float not null,
  maxheight float not null,
  maxaxleload float not null,
  maxwidth float not null,
  maxlength float not null,
  forbidden smallint not null, -- 0: -, 1: cars (and HGVs), 2: HGVs
  toll boolean not null,
  x1 double precision not null,
  y1 double precision not null,
  x2 double precision not null,
  y2 double precision not null,
  trafficsignals smallint not null,
  stopsigns smallint not null,
  tollbooths smallint not null,
  crossings smallint not null,
  ascent smallint not null,
  descent smallint not null,
  length double precision not null,
  city smallint not null, -- percentage of way within city
  linestring varchar(100000) not null,
  source integer not null,
  target integer not null,
  graph_number integer not null
);
set client_encoding to 'UTF-8';
copy :way from 'ways.csv';
-- Note that you may have to change the table name in the following statement
select addgeometrycolumn('way', 'the_geom', 4326, 'LINESTRING', 2);
update :way set the_geom = geometryfromtext(linestring, 4326);
alter table :way drop linestring;
alter table :way add primary key (way_id);
create index geom_idx on :way using gist (the_geom);
vacuum full analyze :way;

The second result of running the three conversion steps of osm-import is a CSV
file with all turn restrictions applying to the ways. This CSV file can then be
read into a PostGIS database as well.

Setting class ids

The following table lists a suggestion of how to group different OSM highway types to yield fourteen classifications of ways.

OSM highway type Class id Comment

motorway

1

 

motorway_link

7

 

trunk

2

 

trunk_link

8

 

primary

3

 

primary_link

9

 

secondary

4

 

secondary_link

9

 

tertiary

5

 

tertiary_link

9

not compliant with OSM tagging guidelines

unclassified

5

 

road

6

 

residential

11

 

living_street

12

 

service

10

 

pedestrian

12

 

services

10

 

rest_area

10

 

ford

9

 

incline

9

 

incline_steep

9

 

mini_roundabout

9

not correctly tagged by OSM guidelines

roundabout

9

not correctly tagged by OSM guidelines

minor

9

not correctly tagged by OSM guidelines

ferry

13

not a highway type

eurotunnel

14

manually added, see eurotunnel.csv

Note that "eurotunnel" is not created as a direct result of any run of osm-import, as rail lines are not imported at all. Instead, the file doc/eurotunnel.csv has been provided as a means of manually adding the Eurotunnel to the ways file after running Convert.

These classifications can also be set on each row of the way table with the following SQL commands:

-- Adapt the following line if using another name for the way table
\set way way
alter table :way add class_id smallint;
create table classification (type varchar(32) primary key, class_id smallint not null);
insert into classification (type, class_id) values
('motorway', 1),
('motorway_link', 7),
('trunk', 2),
('trunk_link', 8),
('primary', 3),
('primary_link', 9),
('secondary', 4),
('secondary_link', 9),
('tertiary', 5),
('tertiary_link', 9),
('unclassified', 5),
('road', 6),
('service', 10),
('services', 10),
('rest_area', 10),
('residential', 11),
('living_street', 12),
('pedestrian', 12),
('ford', 9),
('incline', 9),
('incline_steep', 9),
('mini_roundabout', 9),
('roundabout', 9),
('minor', 9),
('ferry', 13),
('eurotunnel', 14);
update :way set class_id = c.class_id from classification c where :way.type = c.type;

Importing boundaries into a PostGIS database

create table boundary (
  boundaryId integer not null,
  osmId integer not null,
  adminLevel smallint not null,
  name varchar(256),
  multiPolygon varchar(5000000) not null
);
set client_encoding to 'UTF-8';
copy boundary from 'boundaries.csv';
select addgeometrycolumn('boundary', 'the_geom', 4326, 'MULTIPOLYGON', 2);
update boundary set the_geom = geometryfromtext(multipolygon, 4326);
alter table boundary drop multipolygon;
alter table boundary add primary key (boundary_id);
create index boundary_geom_idx on boundary using gist (the_geom);
vacuum full analyze boundary;