Loading sample data

Data

To load some data into SpatiaLite, first of all you need them. Luckily, there are planty of free data sources available, here we will take the most popular and perhaps largest of the kind, OpenStreetMap data. The full planet set is huge, well over 40 Gb, so feel to take something smaller. In this case, I will use my country, Latvia. Full compressed set takes 51 Mb, very convenient.

Geofabrik.de provides downloads for OpenStreetMap data, grouped by countries and regions. Open http://download.geofabrik.de/ and choose what you like best.  If you want map of some particular area, go to https://www.openstreetmap.org/export, there you can get the curent screen view.

Formats

There are several formats available at Geofabrik.de, we will use shp files (downloadable in .zip archive format). Download and extract somewhere on your machine.

SpatiaLite allows to read external data without incorporating it into SQLite database. But then not all functions are available, so we will load all data into the database.

Loading from GUI

Launch the GUI SpatiaLite, from the File menu click Create a new (empty) SQLite DB. Point to the location on disk where you want the db to be located. Some other totorials tell that you must run some preliminary SQL statements to prepare the database for spatial data, but for at least the SpatiaLite-GUI version 2.0.0 this is not necessary. It seems that everything is done automagically, as new and empty SpatiaLite database takes 5.6 Mb, not several tens of Kb for an empty SQLite database.

From the menu icons, find the button with the globe and small green arrow, it has the tooltip Load shapefile. Click, point in the file dialog to some shapefile from the downloaded and extracted dataset, and fill the import options.

Options dialog for shapefile import into the SpatiaLite database
  • Table name – this will be table name of the imported data. One table, one layer. Give some short and concise name. OSM data have lengthy names, that mens more typing later when writing queries.
  • GeomColumn name – the name for spatial data column. The shorter, the better.
  • SRID – identifier of the spatial refence system. They are standartized across many data sources and use the same ids. OpenStreetMap uses the same system as Google, WGS 84 (a.k.a. WGS 1984, EPSG:4326), whose SRID is 4326.
  • Charset encoding – shp data is accompanied with textual data in dbf files. For Latvian, UTF-8 worked correctly, so most probably it will do for other languages that use charsets other than Western European.
  • Coerce 2D geometries – leave empty for now.
  • Apply geometry compression – leave empty for now.
  • With spatial index – leave empty. This creates spatial index, that considerably increases read and seek perfomance, but decreases data insert/update speed. As we are loading bulk data, it is wiser to create index after the loading is complete. For this file, it was 31 secs without index against 70 secs with index.
  • Geometry type – leave automatic; data in shp will be used to determine spatial element types.
  • Primary Key Column – set to use osm_id field from the data. SQLite does not make big difference between char and integer types, so this saves one table column.
  • Interpretation of DBF DATE values – leave as is.

 

Repeat the process for other shapefiles in the downloaded data set. One shapefile for each layer.

Loading using command-line

Open terminal window. Create a new SpatiaLite database typing spatialite testdb.db. It will print out a list of supported extensions and create a new database.

Then load shapefiles with .loadshp command. One command for each shapefile. The example below uses the same import parameters as in the GUI case.

spatialite> .loadshp C:/Downloads/latvia/gis.osm_buildings_a_free_1 buildings UTF-8 4326 Geometry osm_id geom_type=AUTO

! Note:

  • On Windows, for paths use only forward slashes.
  • DO NOT use file extension for shapefiles.

 

Make one command for each shapefile, and execute in batch; for large files, you can go and take a coffee.

For sake of reference, below is a printout of help for .loadshp command.

.loadshp <args>   Loads a SHAPEFILE into a SpatiaLite table
                  arg_list: shp_path table_name charset [SRID] [column_name]
                      [pk_column] [geom_type] [2d | 3d] [compressed]
                      [with_spatial_index] [text_dates]
                      geom_type={ AUTO | LINESTRING[ Z | M | ZM ]
                                 | MULTILINESTRING[ Z | M | ZM ]
                                 | POLYGON[ Z | M | ZM ]
                                 | MULTIPOLYGON[ Z | M | ZM ] }

Leave a Reply

Your email address will not be published. Required fields are marked *