Apr 15 2008
Spatial data in SQLite
Slashgeo pointed me to a very interesting set of projects - SpatiaLite and VirtualShape. They provide a spatial data engine for the sqlite database. Think of it as the PostGIS of SQLite. It looks like this extends sqlite’s spatial capabilities far beyond the sqlite OGR driver.
SpatiaLite provides many of the basic OGC Simple Features functions - transforming geometries between projections, spatial operations of bounding boxes, and some basic functions to disect, analyze and export geometries.
VirtualShape provides the really neat ability to access a shapefile using the SpatiaLite/SQlite interface without having to import a copy - it reads directly off the shapefile by exposing the shapefile and its attributes as a “virtual table”. I can think of a million uses for this. For example, lets say you have a shapefile of US counties and the number of voter in the 2004 election as an attribute in the dbf. You want to find the total voter count in each state:
$ ls -1 counties.*
counties.dbf
counties.prj
counties.shp
counties.shx
$ sqlite3 test.db
sqlite> .load 'SpatiaLite.so'
sqlite> .load 'VirtualShape.so'
sqlite> CREATE virtual table virtual_counties using VirtualShape(counties);
sqlite> select sum(voters) as total_voters, state_name
from virtual_counties
group by state_name
order by total_voters desc;
9830550.0|California
7563055.0|Florida
7346779.0|Texas
...
Now this is fairly straightforward non-spatial SQL but the ability to run it against a shapfile without having to export to an intermediate data format is a very valuable tool.
Links:
When to use SQlite.
A video presentation by Richard Hipp (the author of sqlite).
For a different application offering the same functionality - and more, check out my project JEQL: http://tsusiatsoftware.net/jeql/main.html
In JEQL this same example case would look like:
t = null;
ShapefileReader t file: “counties.shp:;
t2 = select sum(voters) as total_voters, state_name
from t
group by state_name
order by total_voters desc;
Print t2;
JEQL has the advantage of a wide set of spatial functions (as well as lots of others), and the ability to export to many different formats (such as shape, KML, etc).
I also recently learned of SQLiteGeo which this link references and was glad to discover it since I’ve got an geospatial observations oriented schema at http://code.google.com/p/xenia/wiki/XeniaPackageSqlite which this solution fits into similar to PostGIS for PostgreSQL earlier.
Now if I just had a SQLite driver for Geoserver - I thought about using JDBC to work between Sqlite and Geoserver, but I’ve heard the JDBC connection is very slow.
Thanks
Jeremy Cothran
Just wanted to mention that GDAL / OGR supports basic SQL Queries, as well:
http://www.gdal.org/ogr/ogr_sql.html
It looks great to me too. Of course there’s the caveat that SQLiteGeo doesn’t support spatial indexes, so many types of operations will be _slow_ for large datasets. To manipulate shapefiles it seems perfect, though.
If applications like qgis supported sqlite use just easy like esri works with geodatabase. Then SQLite can grow as opensource geodatabase alternative.
Now SQLite support spatial index R-tree.
See
http://www.sqlite.org/draft/doc/rtree.html
hello
i want to ask, how to convert shp to *.db with virtualshape ???
thank’s for the answer…
best regards
adrian
ADRIAN: I don’t know about virtualshape, but you can convert shape files to SQLite using GDAL/OGR command line utility:
ogr2ogr -f sqlite output.db input.shp