Back to OSM Berlin Index

---

Introduction

OSM data as shipped by GeoFabrik Shapefiles are not immediately ready to be used, so we'll need to slightly rearrrange them in a more convenient form.

1. First of all we'll reproject all coordinates into SRID=25833 ETRS89 / UTM zome 33N
   A short rationale accounting for this: OSM data are always related to SRID=4326 WGS84 that is a geografic reference system based on latitude and longitude angles, thus implying that the corresponding unit of measure are degrees, that cause several troubles in computing lengths. areas and distances.
   Adopting instead an appropriate planar aka projected reference system for a map covering just a very small portion of the Earth surely is a wiser choice.
2. Geofabrik Shapefiles often mix together several different classes of features, thus making difficult assigning individual styling rules to each class.
   Creating individal Spatial Tables for each class (or may be in some cases for a selected group of classes) surely is a convenient choice.
3. Not all OSM data are relavant for creating the OSM Berlin Map, so we'll discard all uninteresting features.

Rearranging OSM data in a better form isn't at all difficult and just requires a pinch of SQL processing.

---

Step #1: creating the berlin_osm.sqlite database

Create a brand new, empty database naming it berlin_osm.sqlite And now attach to it the database you've previously created by importing all relevant GeoFabrik Shapefiles. You simply have to press the button shown on he side figure for doing this. If you wish better you can get the same result by executing an SQL statememt like the following: ATTACH DATABASE 'c:/osm/berlin_osm_shp.sqlite' AS 'a';

As you can directly check, now you have: an initially empty berlin_osm.sqlite database (the final sample to be delivered). and attached to it there is the other dababase containing all the imported Shapefiles. Such a layout will make the task of creating and populating all the Spatial Tables really simple and direct. We just need to execute few SQL statements. I suggest you to prepare some SQL script so to fully automate the task, but if you want you can simply cut&paste all the following statements executing individually each of them on the GUI tool.

---

Step #2: creating and populating all required Spatial Tables

CREATE TABLE main.buildings (
  osm_id INTEGER NOT NULL tertiary KEY);
SELECT AddGeometryColumn('buildings', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('buildings', 'geom');
INSERT OR IGNORE INTO main.buildings
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_buildings_a_free_1;

Highlights:

1. we'll create the base Table (in this case just containing the PRIMARY KEY).
2. then we'll add a Geometry column associated to SRID=25833 and of the MULTIPOLYGON type.
3. we'll add a Spatial Index supporting the Geometry for faster access.
4. and finally we'll populate the Spatial Table by copying all the selected features from the corresponding Table into the attached DB
   • Note: we'll use ST_Transform() for re-projecting the coordinates from two different SRIDs.

CREATE TABLE main.green_areas (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('green_areas', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('green_areas', 'geom');
INSERT OR IGNORE INTO main.green_areas
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_landuse_a_free_1
WHERE fclass IN ('grass', 'meadows', 'scrub', 'heath');

Highlights:

• in this case we'll apply a WHERE filter on the fclass column so to copy just a few selected classes of features.

CREATE TABLE main.forests (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('forests', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('forests', 'geom');
INSERT OR IGNORE INTO main.forests
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_landuse_a_free_1
WHERE fclass IN ('forest');

CREATE TABLE main.agriculture (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('agriculture', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('agriculture', 'geom');
INSERT OR IGNORE INTO main.agriculture
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_landuse_a_free_1
WHERE fclass IN ('farmland', 'farmyard', 'orchard', 'vineyard');');

CREATE TABLE main.cemeteries (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('cemeteries', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('cemeteries', 'geom');
INSERT OR IGNORE INTO main.cemeteries
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_landuse_a_free_1
WHERE fclass IN ('cemetery');

CREATE TABLE main.religion (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('religion', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('religion', 'geom');
INSERT OR IGNORE INTO main.religion
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_pofw_a_free_1;

CREATE TABLE main.water_bodies (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('water_bodies', 'geom', 25833, 'MULTIPOLYGON', 'XY');
SELECT CreateSpatialIndex('water_bodies', 'geom');
INSERT OR IGNORE INTO main.water_bodies
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_water_a_free_1
WHERE fclass IN ('water', 'reservoir');
  
CREATE TABLE main.rail (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('rail', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('rail', 'geom');
INSERT OR IGNORE INTO main.rail
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_railways_free_1
WHERE fclass IN ('rail');
  
CREATE TABLE main.light_rail (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('light_rail', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('light_rail', 'geom');
INSERT OR IGNORE INTO main.light_rail
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_railways_free_1
WHERE fclass IN ('light_rail');

CREATE TABLE main.tram (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('tram', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('tram', 'geom');
INSERT OR IGNORE INTO main.tram
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_railways_free_1
WHERE fclass IN ('tram');

CREATE TABLE main.subway (
  osm_id INTEGER NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('subway', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('subway', 'geom');
INSERT OR IGNORE INTO main.subway
SELECT osm_id, ST_Transform(geometry, 25833)
FROM a.gis_osm_railways_free_1
WHERE fclass IN ('subway');

CREATE TABLE main.motorway (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('motorway', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('motorway', 'geom');
INSERT OR IGNORE INTO main.motorway
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_roads_free_1
WHERE fclass IN ('motorway', 'motorway_link');

CREATE TABLE main.trunk (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('trunk', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('trunk', 'geom');
INSERT OR IGNORE INTO main.trunk
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_roads_free_1
WHERE fclass IN ('trunk', 'trunk_link');

CREATE TABLE main.rd_tertiary (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('rd_tertiary', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('rd_tertiary', 'geom');
INSERT OR IGNORE INTO main.rd_tertiary
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_roads_free_1
WHERE fclass IN ('tertiary', 'tertiary_link');

CREATE TABLE main.rd_residential (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('rd_residential', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('rd_residential', 'geom');
INSERT OR IGNORE INTO main.rd_residential
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_roads_free_1
WHERE fclass IN ('living_street', 'residential', 'service', 'unclassified');

CREATE TABLE main.paths (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('paths', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('paths', 'geom');
INSERT OR IGNORE INTO main.paths
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_roads_free_1
WHERE fclass IN ('path');

CREATE TABLE main.cycleways (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('cycleways', 'geom', 25833, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('cycleways', 'geom');
INSERT OR IGNORE INTO main.cycleways
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_roads_free_1
WHERE fclass IN ('cycleway');

CREATE TABLE main.railway_stations (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('railway_stations', 'geom', 25833, 'POINT', 'XY');
SELECT CreateSpatialIndex('railway_stations', 'geom');
INSERT OR IGNORE INTO main.railway_stations
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_transport_free_1
WHERE fclass IN ('railway_station', 'railway_halt');

CREATE TABLE main.tram_stops (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('tram_stops', 'geom', 25833, 'POINT', 'XY');
SELECT CreateSpatialIndex('tram_stops', 'geom');
INSERT OR IGNORE INTO main.tram_stops
SELECT osm_id, name, ST_Transform(geometry, 25833)
FROM a.gis_osm_transport_free_1
WHERE fclass IN ('tram_stop');

Nothing worth to be noted ... we'll simply continue exactly as in previous cases.

CREATE TABLE main.suburbs (
  osm_id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL);
SELECT AddGeometryColumn('suburbs', 'geom', 25833, 'POINT', 'XY');
SELECT CreateSpatialIndex('suburbs', 'geom');
INSERT OR IGNORE INTO main.suburbs
SELECT osm_id, name, ST_Transform(ST_Centroid(geometry), 25833)
FROM a.gis_osm_places_a_free_1
WHERE fclass IN ('suburb');

Highlights:

• a rather exceptional case: we'll use ST_Centroid() so to transform POLYGONs into POINTs.
• this is because we are not interested in drawing suburbs as areas in out Map; we intentd instead to print their names as textual labels at smaller scales.

---

Where you are: next steps

All right, we've finally built a SpatiaLite database containing all interesting OSM features covering the city of Berlin conveniently distributed between several Spatial Tables, each one corresponding to a specific class. Remeber: our final goal is to get a very detailed and nicely looking Map, one capable to zoom in and out and to pan with fairly good response times at all scales from the higher to the lower ones. Now we need to define all the styling rules appropriate for each layer, that is the hard-core of all this OSM Berlin Map tutorial. So you should now return to the main index page then proceeding to the next step.

---

Back to OSM Berlin Index