Mathematica and Spatial Database

Warning: this post uses undocumented Mathematica command and modifies the Mathematica installation. Don't try this at home.

For any GIS software, evaluating spatial relationships, such as equal, disjoint, within, intersects, etc., is a fundamental requirement. Also, R-tree support is a must for any large spatial data set. Currently, these sets of functions are not built in Mathematica. It is difficult to perform complex GIS analysis inside Mathematica. One way is to call external libraries through Mathlink/Jlink. Another way is connecting to a spatial database, such as PostGreSQL, Oracle Spatial through database connection. Spatial database? What about Spatialite, it is a complete spatail DBMS built as an extension to the extreme light-weighted database SQLite. 

From this Wolfram|Alpha tweet analysis post, it shows Mathematica actually ship with SQlite.

I installed the Mathematica 8 linux trail version.

db = Database`OpenDatabase["/tmp/test.sqlite"];
Database`QueryDatabase[db, "SELECT sqlite_version();"]
{{3.6.1}}

Here is SQLite library from Mathematica
/usr/local/Wolfram/Mathematica/8.0/SystemFiles/Kernel/Binaries/Linux/libsqlite3.so

For security reason, the dynamic loading extension is disabled. And we need to compile our own copy of libsqlite3.so. The detail is explained here: the pre-packaged 'libsqlite' trap.

Grab the source code for SQLite website, build the new library with:

CFLAGS="-DSQLITE_ENABLE_LOAD_EXTENSION=1" ./configure

then make a copy of the original libsqlite3.so, and overwrite it with the new version.

Database`QueryDatabase[db, "SELECT sqlite_version();"]
{{3.7.5}}

Then we can try with the Spatialite already installed:

Database`QueryDatabase[db, "SELECT load_extension(‘/usr/lib/libspatialite.so’);"]
Database`QueryDatabase[db, "SELECT spatialite_version();"]
{{"2.4.0"}}

Try a spatial SQL command:

Database`QueryDatabase[db, "SELECT X(GeomFromText('POINT(-85 39)',4326));"]
{{-85.}}

Wow, it is working!

Here is a tutorial on Spatialite, you can get the idea of what kind of functions are supported by Spatialite. If you are familiar with the spatial database, you can build a rather functional GIS system inside Mathematica with the support of Spatialite.