Monday, June 03, 2013

Spatialite and Me

I had the misfortune to need to connect to a Spatialite enhanced SQLite database, and found myself in some sort of titanic struggle. I first tried using JDBC on OSX and got roundly defeated. For round two I tried with Python on Ubuntu.

For what it's worth (and because this should be documented somewhere, for those who follow) I believe that the following are the steps required to get Spatialite up and running in python on Ubuntu 13.04 (Raring Ringtail) and Ubuntu 12.04. I say 'I believe' because I haven't worked with this in any anger yet :(


If you bother to read through the following then you'll see that my problems were largely caused by the PyPi version of pyspatialite. I think that this software is fundamentally broken in several ways:
it assumes a layout and content of the Spatialite site it references, it does not notify the user clearly when those assumptions are broken, and it caches files and does not remove them if an error was encountered.

The net effect is that every time the Spatialite site is changed (what, no stable url's?) the PyPi download is likely to break and will need repair. And innocent users such as myself will waste a lot of time trying to work out what went wrong.

So if installing on Ubuntu simply use:

sudo apt-get install python-pyspatialite

The downside is that this package is only supported on Quantal and Raring Ringtail.

If you need some sort of automated deployment then clearly you are going to do a lot of work if you want to deploy to anything other than Quantal and Ringtail...

Ubuntu 13.04

I started out on Ubuntu 13.04 and decided that I would try to do some sort of self contained install so that I could make use of virtualenv.

Do not do what I did: 
pip install pyspatialite
For if you do you will be met with:
download_file = pattern.findall(download_page)[0]
IndexError: list index out of range

This is because it would appear that the installer is trying to fetch a file from the Internet: a file that does not exist. From my search on the Internet, this seems to be a regular occurrence with the different versions this software, and may be fixed only to reoccur on the next release :(

If you have the misfortune of making this mistake be aware that the installer has already downloaded files, and it will not try to get replacement copies without manual intervention. More on this in the section on installing on Ubuntu 12.04 below. For I think with a little bit of hacking you could fix this.

Be warned: if you get this error and then attempt to install an older version, you will be met with:
error: conflicting types for ‘spatialite_init’

Do not try to clear ~/.pip/cache – because that's not where the files are being cached :( A pip un-install will not work either, as the software was never installed :(

Having run into the above I simply switched to a new VM, discarding my 'broken' one, and in the new VM I ran:
pip install pyspatialite==2.6.2-spatialite.2.4.0-4

But don't you do this yet: first you need to run:
apt-get install python-dev

Or you will get the following error:
fatal error: Python.h: No such file or directory

Wait: don't run that initial install yet! You also need to run:
apt-get install libgoes-dev

Otherwise you will get:
error: geos_c.h: No such file or directory

And be still that twitching finger: Next you need to run:
apt-get install libproj-dev

Otherwise you will get:
error: proj_api.h: No such file or directory

Then still hold off: for next you need to do:
apt-get install libsqlite3-dev

For if you don't you will be met by a:
fatal error: sqlite3.h: No such file or directory

Then (yes, there's more) you need to
cd /usr/lib
sudo ln -s

For if you don't, you will be met by the somewhat mysterious:
/usr/bin/ld: cannot find -lgeos

This last one is happening because the libgeos-3.3.3 package installed with libgeos-dev forgets to create create this needed symlink (!) It used to, I know, because the Internet tells me so.

Then, finally, don't forget to do a:
apt-get install libspatialite-dev

Otherwise you might be met with a:
fatal error: spatialite.h: No such file or directory

Finally, finally you can run:
pip install pyspatialite==2.6.2-spatialite.2.4.0-4

Don't forget to use sudo if not running as root in all of the above…

Having got to this stage, you can then open up a python shell and try to connect to the database. I say 'try' because you are not yet done!

You need to:
gedit /usr/local/lib/python2.7/dis-packages/pyspatialite/
And change line 50 to be:
version_info = tuple([int(x) if is instance(x, (int, long)) else x for x in version.split(".")])

This is because the version_info contains strings as well as integers, and the original, as written, thus dies at this point:
version_info = tuple([int(x) for x in version.split(".")])

This last step fills me with dread, as who knows how this string is actually used? How might this step bite later on? However, after all of the above, I could now open the two Sqlite databases I have with Spatialite data in python and execute queries against them.

As you can gather, I made each of the mistakes above, and had to resolve where I had gone wrong: a time consuming task. And one that has had me muttering dark words and wondering nasty thoughts about Spatialite.

The alternate process is to simply accept machine wide installation and to do the following:

sudo apt-get install python-pyspatialite

Which gave me my python bindings on more up to date versions of the software without any of the pain I went through doing the pip install.

However, having done all of this I discovered that I actually needed to run Spatialite version 3 on Ubuntu 12.04. So, getting the whole shebang set up on 12.04 became my next task.

Ubuntu 12.04

On 12.04, having been trained by my previous experiences, I simply ran:
sudo apt-get install python-dev
sudo apt-get install libgeos-dev
sudo apt-get install libproj-dev

Then I downloaded pyspatialite 3.0.1 from:
and unpacked it into a directory off of my home directory.

Then I moved to this directory and ran
sudo python setup install
Which crashed and burned with the infamous error message:
download_file = pattern.findall(download_page)[0]
IndexError: list index out of range

I went to the line causing the error (line 97)
and replaced the "pattern.findall(download_page)[0]" with "" (the version of the file I wanted to install) I also edited the url in the following line to read: "", because as it stood it was not a correct url.
I reran
sudo python setup install
and got the dreaded
error: conflicting types for ‘spatialite_init’
I simply deleted the amalgamation subdirectory into which the installer seems to download stuff into and reran:
sudo python setup install

And this time, it all seemed to work. Sweetness and light, and the trilling of angels ensued. More importantly, I was able to open and access the databases I wanted to work with.

It really shouldn't be this hard, should it? Also knowing all of this, I suspect I could get the pip install that I tried on 13.04 working. But life is too short.