Background

Tomorrow I am doing a presentation on some of the more advanced features of QGIS. In the last presentation I gave, one of the things that interested people was the ability to ship Spatialite databases that contain multiple vector datasets. So I though I would build spatialite tools on my ubuntu karmic 64 bit laptop. In the interests of my short memory span I am writing down the procedure I followed here. Note that I already have the standard gnu toolchain (via sudo apt-get install build-essential) and all the standard FOSSGIS libs such as proj4, geos and so on. Also note that if you have a Windows, OS X or i386 based Linux machine, the fine folks as spatialite provide pre-built binaries. Lastly I will point out that I am looking at using the command line tools only in this discussion since I want to be able to use these tools in a batch environment – the spatialite home page has a graphical user interface which may be a better option if you are not happy using the command line…

Build the spatialite library

First of all I needed to build libspatialite:

cd dev/cpp/
wget http://www.gaia-gis.it/spatialite/libspatialite-2.3.1.tar.gz
mkdir spatialite
cd spatialite
tar xvfz ../libspatialite-2.3.1.tar.gz
cd libspatialite-amalgamation-2.3.1
./configure --prefix=/home/timlinux/apps/ --with-geos-lib=/usr/lib --with-proj-lib=/usr/lib
make install

Build the spatialite tools

Spatialite tools are a companion set of command line utilities that allow you to do things like import shapefiles into your database (which is what we are after! I built the spatialite tools like this:

cd ~/dev/cpp
wget http://www.gaia-gis.it/spatialite/spatialite-tools-2.3.1.tar.gz
tar xcfz spatialite-tools-2.3.1.tar.gz
cd spatialite-tools-2.3.1
export CPPFLAGS=-I/home/timlinux/apps/include
./configure --prefix=/home/timlinux/apps/ \
  --with-geos-lib=/usr/lib --with-proj-lib=/usr/lib \
  --with-spatialite-lib=/home/timlinux/apps/lib
make install

Initialise the database

Next you need to get the spatialite initial database. If you are a PostGIS user, this step is somewhat the same as running the lwpostgis and spatialrefsys sql scripts on a new PostgreSQL database to turn it into a PostGIS enabled one.

wget  http://www.gaia-gis.it/spatialite/init_spatialite-2.3.sql.gz
gunzip init_spatialite-2.3.sql.gz
touch brazil.sqlite
spatialite brazil.sqlite < ~/Downloads/init_spatialite-2.3.sql

Load your data

With that out of the way you can run the spatialite tools (in my case I put ~/apps/bin in my path for easy access) to load a shapefile into your database. See the Spatialite Home Page for more details.

 ~/apps/bin/spatialite_tool -i -d brasil.sqlite -s 4326 \
 --type POINT -t cidades -c CP1252 -shp Brasil_Cid

When it runs it produces output like this:

SQLite version: 3.6.16
SpatiaLite version: 2.3.1
Inserted 5564 rows into 'cidades' form 'Brasil_Cid.shp'

I loaded a couple more datasets e.g. a polyline roads layer:

spatialite_tool -i  -d brasil.sqlite -s 4326  --type LINESTRING \
 -t rodovias -c CP1252 -shp BR_RODO2002
SQLite version: 3.6.16
SpatiaLite version: 2.3.1
Inserted 39667 rows into 'rodovias' form 'BR_RODO2002.shp'

Here is a last example, loading a polygon layer:

spatialite_tool -i -d brasil.sqlite -s 4326  --type POLYGON -t brasil -c CP1252 -shp brasil
SQLite version: 3.6.16
SpatiaLite version: 2.3.1
Inserted 1 rows into 'brasil' form 'brasil.shp'

One thing to note is that I used a latin locale (CP1252) in these exampless - if your dataset contains simple ascii characters only then user ASCII for the locale. The spatialite home page lists other supported locales.

Viewing in QGIS

QGIS has included out of the box support for spatialite databases for some time now.

Heres what my spatialite database looks like in QGIS:

For the geeks out there

One last thing I did was to update my QGIS build to use my hand built sqlite lib rather than the one that comes bundled in the QGIS sources. Note this is not really needed and I did it just to test QGIS building against an external copy of Spatialite. I did this by running ccmake and setting the INTERNAL_SPATIALITE flag to off. Then I pressed c to reconfigure and then populated the spatial lite include and lib paths. One more 'c' to configure and then 'g' to generate the make files and then I could build install my QGIS copy.