osm2pgsql and conversion to layers in Geoserver

My interests have been focused lately on the use of Openstreetmap with PostgresQL and Geoserver.

The installation of PostgresQL 9.0/Postgis 1.5 and Geoserver 2.0.3 went without problems on my Windows machine.

After that, I downloaded the Netherlands.osm from http://download.geofabrik.de/osm/europe/. In order for the data to be available in Geoserver, I had to import the file in PostgresQL, with the aid of the well known osm2pgsql.

Below you’ll find the output. After that, I defined a store in Geoserver with the Netherlands data and was able to define the different layers.

planet_osm_line
planet_osm_point
planet_osm_polygon
planet_osm_roads
planet_osm_nodes
planet_osm_rels
planet_osm_ways

However, some of the layers - the last three: nodes, rels and ways - cannot be viewed in Layer Preview. You get the following exception in Geoserver

<?xml version="1.0" encoding="UTF-8" standalone="no"?> java.lang.NullPointerException null

While creating the layers in Geoserver I noticed that none of the three had a native SRS defined and no values in the bounding boxes, other than -1 and 0 values. From the OSM2PGSQL logs below, you see that the three files while being created from the netherlands data had something with a primary key, while the ‘correct’ data was processed without the same NOTICE. The notices, errors and absence of a Native SRS in the layers must be related.

My questions are:

  • Is something wrong with the Netherlands data?
  • With what tools can I check or investigate the osm file
  • what is the meaning of the skipping notices?

Any clues are highly appreciated!

Kind regards,

Pim Verver


Microsoft Windows [versie 6.1.7601]
osm2pgsql -U postgres -s -S default.style -d POINederland1 netherlands\netherlands.osm

osm2pgsql SVN version 0.69-21289M

Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE: table “planet_osm_point” does not exist, skipping
NOTICE: table “planet_osm_point_tmp” does not exist, skipping
Setting up table: planet_osm_line
NOTICE: table “planet_osm_line” does not exist, skipping
NOTICE: table “planet_osm_line_tmp” does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE: table “planet_osm_polygon” does not exist, skipping
NOTICE: table “planet_osm_polygon_tmp” does not exist, skipping
Setting up table: planet_osm_roads
NOTICE: table “planet_osm_roads” does not exist, skipping
NOTICE: table “planet_osm_roads_tmp” does not exist, skipping
Mid: pgsql, scale=100, cache=800MB, maxblocks=102401*8192
Setting up table: planet_osm_nodes
*** WARNING: intarray contrib module not installed
*** The resulting database will not be usable for applying diffs.
NOTICE: table “planet_osm_nodes” does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “planet_osm_nodes
pkey" for table “planet_osm_nodes”
Setting up table: planet_osm_ways
NOTICE: table “planet_osm_ways” does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways

pkey” for table “planet_osm_ways”
Setting up table: planet_osm_rels
NOTICE: table “planet_osm_rels” does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “planet_osm_rels_
pkey” for table “planet_osm_rels”

!! You are running this on 32bit system, so at most
!! 3GB of RAM can be used. If you encounter unexpected
!! exceptions during import, you should try running in slim
!! mode using parameter -s.

Reading in file: netherlands\netherlands.osm
Processing: Node(44836k) Way(5832k) Relation(69k)
Node stats: total(44836601), max(1202333648)
Way stats: total(5832818), max(104204121)
Relation stats: total(69556), max(1475765)

Going over pending ways
processing way (4491k)

Going over pending relations

node cache: stored: 43395502(96.79%), storage efficiency: 41.38%, hit rate: 95.9
4%
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes
Stopping table: planet_osm_ways
Stopped table: planet_osm_ways
Stopping table: planet_osm_rels
Stopped table: planet_osm_rels
Committing transaction for planet_osm_point
Sorting data and creating indexes for planet_osm_point
Completed planet_osm_point
Committing transaction for planet_osm_line
Sorting data and creating indexes for planet_osm_line
Completed planet_osm_line
Committing transaction for planet_osm_polygon
Sorting data and creating indexes for planet_osm_polygon
Completed planet_osm_polygon
Committing transaction for planet_osm_roads
Sorting data and creating indexes for planet_osm_roads
Completed planet_osm_roads

Hi,

Points, lines and polygons are the ones you will need. You can well skip the nodes, rels and ways tables when rendering with Geoserver. Mapkik is skipping them as well.

I would suggest to do some search from the web with OSM, Geoserver and SLD. This one is good reading to start with
http://blog.geoserver.org/2009/01/30/geoserver-and-openstreetmap/

For learnign PostGIS I would recommend to download OpenJUMP and make it to connect with your database. With OpenJUMP you can run SQL queries from PostGIS and get the result on a map. Download a recent nightly build, it has two important new features which make is easier to run SQL queries with a geometry field and you can save the project with the SQL query layers so you do not need to write them all again next time.

Those three tables are the result of running in --slim mode. If you don’t intend to apply diffs to your database, and you have enough memory (for The Netherlands, you’ll be fine), you’re better of not running in --slim mode. If you also leave out _int.sql from your db, loading will be significantly faster and with a smaller db.

Thank you both for your quick reply!

I’ll follow up on your suggestions.

Kind regards,

Pim Verver