Spatially enable a SQLite database

Spatially enable a SQLite database

# 0. enter the SQLite CLI interface
dm295@ubuntu:~$ sqlite3

# 1. load the spatial extension
sqlite> .load /usr/local/lib/mod_spatialite.so sqlite3_modspatialite_init

# 2. open the target database
sqlite> .open '/tmp/geocoding_cache.db';

# 3. turn off the synchronous pragma for performance
sqlite> PRAGMA synchronous=0;

# 4. spatially initialize the database
sqlite> SELECT InitSpatialMetadata();

# 5. add a 2D Point column named 'geometry' to the 'geocoded' table
sqlite> SELECT AddGeometryColumn ('geocoded', 'geometry', 4326, 'POINT', 2);

# 6. fill geometry values
sqlite> UPDATE geocoded SET Geometry=MakePoint(longitude, latitude, 4326);

# 7. exit the SQLite CLI interface
sqlite> .exit