Lee Hachadoorian on Jan 31st 2012
I spent this weekend getting the SpatiaLite GUI installed, with help from SpatiaLite Users Google Group. SpatiaLite is an open source source geodatabase built on top of SQLite, itself an open source database. As a lightweight, “server-less” database, SQLite is frequently used as an embedded database by other applications, including Firefox, Google Chrome, Skype, iTunes, Clementine (my preferred FOSS music player), and Dropbox (on the client side).
I actually keep most of my geographic data in PostGIS these days. But there is a portability issue. You can’t just copy a database, or a table, from your Postgres server and send it to someone else or use it on another machine. The receiver has to be using Postgres/PostGIS also, and you have to dump/restore the data using database tools, rather than just copying a file using the filesystem. It seems that most people share geographic data using shapefiles, an ESRI specification supported by many open source projects. (The legal status of the shapefile specification is a little unclear to me. ESRI published a technical document describing it in detail, clearly intending for it to become an industry standard, but it seems that they still legally own the standard. I don’t know what the practical impact of this is.)
Shapefiles have, in my opinion, three strikes against them.
- It is a multi-file format, and worse contains an inconsistent number of files, so one shapefile is actually 3 to 12 filesystem files with the same base name (although in practice most shapefiles will contain 5ish filesystem files).
- Attribute field names are limited to eight characters because it uses DBF to store attribute data.
- Support for the DBF format is declining. It was dropped from 2007 versions of Microsoft Excel and Access, although you can still edit these formats with FOSS tools like OpenOffice/LibreOffice.
In addition, SpatiaLite and the ESRI personal geodatabase format have a major advantage, which is that they allow combining several data sources (like shapefiles) into one database file—one SQLite file or Microsoft Access file. Before I started using FOSS GIS, I would organize most of my projects using the MS Access based geodatabase, which both kept my project folders tidy and made it easy to transport or share my project data, since it was all contained in one file. (An added advantage was that I once wrote an application for editing attribute data in Access, which made it easier and quicker than editing in ArcGIS.)
The ESRI personal geodatabase never became an industry standard, possibly because ESRI shortly thereafter introduced the file geodatabase format and immediately suggested everyone switch to using that, and probably because so many products (both proprietary and open source) already supported shapefiles.
But for the reasons I listed above, shapefiles are kind of a pain in the ass. So when I’ve done my analysis on PostGIS, and I want to bring my data with me (to a meeting or a conference, for example), the last thing I want to do is export it to shapefiles. SpatiaLite offers a nice one-file solution to organization and portability, and SpatiaLite has a lot of spatial analysis features built in, so that you can do some limited analysis even without a separate GIS software.
SpatiaLite is available for download for Windows (32-bit, 64-bit). Binaries are not available for Linux, but SpatiaLite is available from the UbuntuGIS PPA (version 2.4.0) or the UbuntuGIS Unstable PPA (version 3.0.0 beta). Unfortunately, SpatiaLite GUI, a graphical front end which makes working with your SpatiaLite database easier, is not available in the PPA, which meant I had to compile it from source. What follows are instructions for compiling/installing SpatiaLite GUI for Ubuntu 10.04.
Installing SpatiaLite GUI
Unfortunately when you compile from source, the package manager doesn’t take care of all the dependencies for you. So when you attempt to configure the install, you will probably get messages that a dependency is missing. If that dependency is in the repositories, your package manager will take care of any further dependencies. If that dependency is not in the repositories, you have to download its source, configure, and note whether it has any missing dependencies, and so on. I already have Quantum GIS installed from the UbuntuGIS Unstable PPA, so many spatial and graphical packages were already in place for me. With QGIS installed, a partial dependency tree for spatialite-gui is as follows:
→libspatialite (2.4.0 available in ubuntugis-stable, 3.0.0 beta available in ubuntugis-ustable)
→freexl (install libfreexl-dev from ubuntugis-unstable or download source from SpatiaLite website)
→libgaiagraphics (download source from SpatiaLite website)
→libgeotiff (install libgeotiff-dev from ubuntugis-unstable)
→libcairo (install libcairo2-dev from standard repositories)
Note that when compiling from source, the installer needs access to the C header files, so even if you have libgeotiff and libcairo installed, configuring libgaiagraphics will fail if you don’t have the associated dev package installed, as I indicate above. Similarly, you wont be able to configure spatialite-gui without libfreexl-dev.
I’m going to begin at the end by saying that I was unable to successfully install SpatiaLite GUI 1.5, the newest version. People on SpatiaLite Users helped me figure out that I needed SpatiaLite 3.0.1 instead of SpatiaLite 3.0.0 beta, which meant compiling SpatiaLite from source instead of using the (slightly) older version in the PPA. I tried this, but while it fixed one problem, I ran into another, and at this point I decided the previous stable version of SpatiaLite GUI was good enough. Furthermore, where possible, I prefer to use software from repositories and PPAs, and I wasn’t sure how the slightly newer version of SpatiaLite would interact with QGIS (which depends on it). So note (a) these instructions are for installing SpatiaLite GUI 1.4.1, and (b) when there is a choice between compiling a newer version from source or installing an older version from a repository/PPA (as with FreeXL), these instructions favor using the repository/PPA.
Begin by installing libgeotiff-dev, libcairo-dev, and libfreexl-dev using Synaptic or APT. Download the source for libgaiagraphics (http://www.gaia-gis.it/gaia-sins/libgaiagraphics-0.4b.tar.gz) and SpatiaLite GUI 1.4 (http://www.gaia-gis.it/gaia-sins/spatialite-gui-sources/spatialite_gui-1.4.1.tar.gz). Download to (or copy to) /usr/local/src and untar (unzip). In the terminal,
cd /usr/local/src/libgaiagraphics-0.4b ./configure make sudo checkinstall
Standardly, when compiling from source, the last command you would issue would be sudo make install. CheckInstall will call make install, but will also add the package to your package manager for easy uninstallation later. Thanks to Compiling things on Ubuntu the Easy Way for the advice. CheckInstall will ask for a description of the package which will get stored in your package manager. I just copied this line from the SpatiaLite website:
Library supporting common-utility raster handling methods
After installing libgaiagraphics, you should be ready to install SpatiaLite GUI.
cd /usr/local/src/spatialite_gui-1.4.0 ./configure make sudo checkinstall
The description I used, again from the SpatiaLite website, was:
Graphical User Interface (GUI) tool supporting SpatiaLite
You should now have SpatiaLite GUI installed.
Using SpatiaLite GUI (briefly)
A more detailed discussion of using SpatiaLite GUI will have to wait for another day, but briefly, let me describe the first thing you might want to do if you are switching from proprietary tools, getting your existing data into SpatiaLite. When you open SpatiaLite GUI for the first time, you won’t be connected to any SpatiaLite databases. SpatiaLite GUI has only one menu (Files), and instead exposes almost everything on the toolbar. The very first button is for “Connecting to an existing SQLite DB”. It does in fact let you connect to a standard (non-spatial) SQLite database. The DB Connection dialog defaults to searching for files with a *.sqlite extension. Note that many SQLite databases instead use *.db as an extension, and there is no enforced standard, so you might have to select “All files (*.*)” to find a particular SQLite/SpatiaLite database.
But I’m going to assume you don’t have a SpatiaLite database yet. The second button creates a new SpatiaLite database, which is empty of actual geographic data, but has all the supporting tables you need to start adding some. The globe-with-arrow button in the middle of the toolbar can then be used to import shapefiles to your database. In order to import a shapefile, you need to specify the SRID, or spatial reference ID.
Without going into a lot of detail right now about what an SRID even is, if the shapefile has a PRJ (projection) file, you can upload the file (or open with a text editor and copy the contents) to http://prj2epsg.org/search, and the web application will find the closest matching SRID.
Load up a few shapefiles and you’ll have a nice project database to take with you and use in open source tools like Quantum GIS (cross-platform) or even proprietary tools like Cartographica (Mac).