spatialite-gui: OSM Berlin importing the Shapefiles

Back to OSM Berlin Index

First of all you need to download the collection of Shapefiles for the Berlin area supplied by Geofabrik

This is actually is a compressed ziplfile containing several Shapefiles, each one of them shipping specific categories of MapFeatures. We'll not use all of them for building our OSM Berlin Map, so we'll start first by creating and populating a transient work database by importing only the few relevant Shapefiles we really intend to use. This will make easier and simpler building the final database in a second step.

First approach: using the wizards supported by the GUI

This one wiil probably be the most appreciated by the majority of the users because it's (apparently) more user friendly.
However you'll quickly discover that continuosly repeating the same sequences of click-click-click actions could easly become a boring and time consuming task.

First of all be sure that you have available the Shapefiles released by GeoFabrik covering Berlin.

If not, you can dowload the zipfiles containing all them right now from here

Now execute spatialite_gui and create en empty, brand new database.

The side figure shows the button on the toolbar you have to press for importing all interesting Shapefiles one by one.

Now for each Shapefile to be imported set the appropriate arguments, then confirm.

Be always sure to set SRID=4326 and Charset=UTF-8

Repeat the same procedure untill you have imported all the required Shapfiles accordingly to the following list:

gis_osm_buildings_a_free_1
gis_osm_landuse_a_free_1
gis_osm_pofw_a_free_1
gis_osm_railways_free_1
gis_osm_roads_free_1
gis_osm_places_a_free_1</li>
gis_osm_transport_free_1
gis_osm_water_a_free_1
gis_osm_waterways_free_1

Alternative approach: using a SQL script

The preferred method for any professional user. It's only slightly more complex, but it ensures a not comparable efficieny and productivity.

-- importing all OSM features of the Buildings class - MULTIPOLYGON
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_buildings_a_free_1', 'gis_osm_buildings_a_free_1', 'UTF-8', 4326);

-- importing all OSM features of the LandUse class - MULTIPOLYGON
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_landuse_a_free_1', 'gis_osm_landuse_a_free_1', 'UTF-8', 4326);

-- inserting all OSM features of the Places class - MULTIPOLYGON
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_places_a_free_1', 'gis_osm_places_a_free_1', 'UTF-8', 4326);

-- importing all OSM features of the PointOfWorship class - MULTIPOLYGON
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_pofw_a_free_1', 'gis_osm_pofw_a_free_1', 'UTF-8', 4326);

-- importing all OSM features of the Railways class - LINESTRING
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_railways_free_1', 'gis_osm_railways_free_1', 'UTF-8', 4326);

-- importing all OSM features of the Roads class - LINESTRING
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_roads_free_1', 'gis_osm_roads_free_1', 'UTF-8', 4326);

-- importing all OSM features of the Transport class - POINT
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_transport_free_1', 'gis_osm_transport_free_1', 'UTF-8', 4326);

-- importing all OSM features of the Water class - MULTIPOLYGON
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_water_a_free_1', 'gis_osm_water_a_free_1', 'UTF-8', 4326);

-- importing all OSM features of the Waterways class - LINESTRING
SELECT ImportSHP('c:/osm/berlin-latest-free.shp/gis_osm_waterways_free_1', 'gis_osm_waterways_free_1', 'UTF-8', 4326);

Highlights:

the ImportSHP() SQL function will create a new Spatial Table and will populate it by importing an external Shapefile:
- the first argument corresponds to the absolute or relative pathname of the Shapefile (excluding any .shp, .shx or .dbf suffix).
- the second argument corresponds to the name to be assigned to the newly created Spatial Table.
- the third argument corresponds to the charset encoding adopted by the .dbf file (it's always UTF-8 for OSM data).
- the fourth argument corresponds to the SRID adopted by Geometries (it's always 4326 WGS84 for OSM data).

Important notice: due to security concerns the ImportSHP() is always disabled by default. You absolutely require to set the external variable SPATIALITE_SECURITY=relaxed in order to explicitly enable it.
This is not required when using spatialite_gui because the GUI tool will automatically enable all restricted SQL functions.

Lesson to keep in mind

SpaliaLite (and RasterLite2 as well) will never force you to follow a rigidly pre-defined schema.
As far as possible you'll be always absolutely free to choose between:
- the graphic approach based on GUI Wizards
- the pure SQL approach (possibly based on some SQL script of your invention)
Using the one or the other makes no difference, because the result will always be exactly the same.
It's almost a matter of personal taste, of experience and technical skills.
Feel absolutely free to invent your own best strategy for solving problems, and you'll always find the right tool for the job supported by SpatiaLite/RasterLite2.

Back to OSM Berlin Index