Preface
Imagine that you have a SQLite database which besides other data has latitude and longitude stored as separate [floating point] columns representing the position in WGS-84 (or any other SRID). And at some point you would want to geographically analyse your data in your favorite desktop GIS ( QGIS for example). It would not be possible until the database is spatially enabled, or in other words, converted to a spatial database.
This post documents the steps needed to spatially enable a SQLite database on Ubuntu 14.04 LTS.
What needs to be installed
SQLite mus be present on the system, that's obvious. Spatialite needs to be as well, in order to get a full fledged spatial DBMS.
I have trouble building/installing Spatialite. But thanks to this guide:
Configuring SQLite, Python and SpatiaLite 4.1.1, everything went smooth eventually. Some URLs in the guide may not be working any longer so you would have to download given packages manually.
There is also an official Linux how-to page to help you with the installation.
Sql2Spatialite
Now, when we have everything in place, lets take a look at how to get a spatial DBMS from SQLite.
So in this example, we have a "/tmp/geocoding_cache.db" SQLite database file. We are going to add a 2D Point column named 'geometry' to the 'geocoded' table and fill it with WGS-84 latitude and longitude.
The following is a series of CLI commands [with comments and prompts as well] used to do the magic:
I have written even a Python CLI script that is doing exactly the same thing. It's kind of a helper script for me [making things go faster] so I do not have to fiddle with the command line.
References
I don't want to take credit for all information in this post. In fact it's a compilation of knowledge spread across the internet. These are the sources used to create the script and for making this article.