Collaborating in geospatial context since 2000!

Monday, July 28, 2008

SpatiaLite: An Alternative Spatial Database

We all know about ESRI's Personal GeoDatabase (MS Access), Refraction's PostGIS, Oracle Spatial, etc... but how many folks are familiar with SpatiaLite? SpatiaLite is the spatial extension to SQLite and after a few initial rounds of testing, I think we have a decent light-weight alternative to the bigger players in the spatial DB game.

First off, what is SQLite?
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is the most widely deployed SQL database engine in the world. It is used in countless desktop computer applications as well as consumer electronic devices including cellphones, PDAs, and MP3 players. The source code for SQLite is in the public domain.
This means that the SQLite engine is deployed in more computing environments than any other database technology in the world.

Alessandro Furieri, the author of SpatiaLite and VirtualShape, created these extensions knowing this! Good job Alessandro!

The latest release of SQLite will include better R-Tree indexing and a wide variety of other enhancements that conform to the OpenGIS Standard for Spatial Data Processing. See below for the SpatiaLite 2.1 Quick Intro.
  • SQLite is a popular DBMS, simple, robust, easy to use and really lightweight
  • each SQLite database is simply a file; you can freely copy it, compress it, send it on a LAN or WEB with no complication at all
    They are also portables; the same database file will work on Windows, Linux, MacOs etc
  • the SpatiaLite extension enables SQLite to support spatial data too [aka GEOMETRY], in a way conformant to OpenGis specifications
    • supports standard WKT and WKB formats
    • implements SQL spatial functions such as AsText(), GeomFromText(), Area(), PointN() and alike
    • the complete set of OpenGis functions is supported via GEOS, this comprehending sophisticated spatial analysis functions such as Overlaps(), Touches(), Contains() and so on
    • supports full Spatial metadata along the OpenGis specifications
    • supports importing and exporting from / to shapefiles
    • supports coordinate reprojection via PROJ.4 and EPSG geodetic parameters dataset
    • supports locale charsets via GNU libiconv
    • implements a true Spatial Index based on the still experimental SQLite's RTree extension
  • the VirtualShape extension enables SQLite to access shapefiles as VIRTUAL TABLEs
    • you can then perform standard SQL queries on external shapefiles, with no need for importing or converting them
  • the GUI tool supports all this, in an user friendly way
How to get started with SQLiteGeo (SpatiaLite, VirtualShape, and SQLite3 combo):
  1. First, try downloading SQLiteGeo. Simply download the ZIP file or Linux Version and extract it anywhere on your local file system.
  2. Follow the tutorial to import, index, and analyze your data.
Version 2.1 GUI Tool:
  1. There is also a GUI tool that will keep you from having to go through the command line. Click Here for all available options!
  2. Download the Windows version here and the Linux version here. Extract the files to your local system and go! There is no installation...
Also, try the SQLite ODBC Driver. I have been able to import/export data through Access and Excel without very many problems.

I'm certain that there are a wide variety of uses for this self contained, file-based, spatial database technology so please feel free to comment on any interaction you may have had with it.

Labels: , ,


Blogger Adam Estrada said...

For SpatiaLite support issues, please participate in the Google Group.


8:32 AM EDT

Blogger baa said...

Nice post!

3:25 AM EST


Post a Comment

<< Home