Linfiniti Geo Blog

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.

I’m off to the 2nd QGIS hackfest event in Vienna in a week’s time. I decided to skip the expensive trip to FOSS4G2009 in Sydney this year and go to the QGIS hackfest rather. Many thanks to the sponsors and donors who have made my travel and accommodation for the QGIS hackfest expenses possible. There are also several other developers who will be receiving support for their travel costs. If you are sitting on a pile of cash you have no idea what to do with, why not wonder over to the QGIS Sponsorship page and sponsor or donate to the project. We have already raised 74% of our EUR5000 target which is absolutely brilliant – it would be great to see that figure go up to 100%!

The last few weeks have seen a lot of interesting activity taking place in QGIS. I finally committed the render caching stuff that I have been playing with for a little while which greatly speeds up layer re-ordering and various other activities – especially when working with large datasets. Juergen has been a ‘bug fixing machine’ as usual and adding many nifty new features. A few weeks back he added query / subsetting support for shapefiles, and in the last few days he added support for multiple svg directories – which I added a gui for today so that arbitary svg directories could be used. At the same time Marco has been augmenting the already excellent digitising tools and working away on the print composer. All this beavering away at the codebase makes me really excited to be attending the code fest and even more excited about the upcoming 1.4 release (which we will do after the hackfest). Oh here is a little screenie of QGIS rendering with some svg icons out of my Gnome icon directory:

Anyway back to the grindstone, can’t wait for next week…

I recently replaced my aging MacBook (the white plasticky one) with a new Sony Vaio VGN-NW15G (yes that is quite a catchy name). I immediately split up the partition and installed Ubuntu Karmic in the bigger section, leaving a small Vista (which it came pre-installed with) partition which I will use occasionally for building QGIS installers for windows. When I bought the laptop the salesman told me I could upgrade to Windows 7 since it was close to the Win 7 release for the ‘bargain’ fee of USD $15 (around ZAR 120). Imagine my joy after going through an arduous form filling, all my personal details sharing process on the sony Vaio website to arrive at this screen:

Suddenly that ‘free’ upgrade for a small handling fee is feeling like a big rip off at $USD45 …. I guess I’ll be (occasionally) using vista for the next 3 years….

Aside from this irritation and a rather limited battery life, I must say that this laptop makes a first class Ubuntu Karmic 64 bit platform – everything ‘just works’. And I mean everything from webcam, wifi, sd slot, plug an play when connecting to LCD projectors….its a real pleasure to have such a well supported laptop to run my beloved Linux operating system on.

Another busy week…and not much time for blogging, so I will just put this presentation online that I gave to a Global Change thinktank meeting I attended in Pretoria.

Also a few weeks back someone I met from INPE, Brasil mentioned Spring as a potential point of collaboration with QGIS. Tonight I finally got a chance to take a look at their site only to be dissapointed when looking at their license to find that it is free as in beer but not open source or Free in the ‘libre’ sense of the word

I usually upgrade to the next-gen ubuntu release a month or two before it comes out so I can test QGIS and other software on the new platform. Today I was looking for a visual query designer for PostgreSQL because I wanted to make some complex queries with multiple joins. I’m ok for dealing with a few simple joins when hand writing my SQL but when things get complex, having a visual query builder is a great time saver.

I tried a whole bunch of options today:

- pgdesigner (linux) crashed all the time and was unusable
- SQL Maestro (win) a very nice looking app but commercial and runs only under windows.
- Navicat (linux via wine) looked reasonable at first but had some severe rendering issues preventing me from being able to see the relation lines between entities. It also seemed to have trouble actually running the SQL it generated. Also it is commercial software and I prefer FOSS.
- OO Base (linux) with the sjdbc driver. I was able to test connect, but try as I might I couldnt get it to save my connection and let me enter the visual design mode.

Finally I took a relook at PGAdmin III. I have used it on and off over the years but usually settle back to using psql from the command line. Imagine my surprise to see that in version 1.10 that ships with Karmic, it now includes a visual query builder.

This is a real notch in the belt for postgres as we are now able to provide a simple solution for novice users to build their own queries visually!

Yesterday I gave a short presentation on QGIS (available here) to an international group of CEOS delegates that were on a visit to South Africa. As well as QGIS I tried to promote GRASS, PostgreSQL/PostGIS and MapServer/Geoserver as a viable alternative stack to the typical Oracle / ArcSDE / ArcServer / ArcGIS that organisations tend to blow so much money on.