James’ recent post about the GIS Interchange File reminded me that I’ve been meaning to discuss some recent activity on the SQLite front in both FDO and OGR.

Traian Stanev recently proposed the creation of an SQLite provider for FDO. He was quickly arm-wrestled into supporting something close to OGC’s Simple Features for SQL specification, and working with Frank Warmerdam hammered out a GIS spec for SQLite that would work for both OGR and FDO. The beauty is that it’s a single file and can be read by any of the existing SQLite tools.

Traian completed initial development of the SQLite provider a couple weeks ago and Frank expanded OGR’s SQLite support to understand this common specification (this work is in the GDAL/OGR trunk for inclusion in the 1.6 release). These implementations have different strengths. The FDO provider was written to be blazing fast, features an in-memory spatial index, and writes to the FDO binary format. The OGR driver was written for maximum portability and allows writing WKT and WKB. Both implementations will read all three geometry formats and understand the dimension and projection information stored in the OGC-derived metadata tables.

You can download a totally unofficial build of the FDO provider from my website if you want to try it out with MapGuide 2.0 or maybe even Autodesk Map 3D 2009. I have successfully tested it in MapGuide with WKT, WKB, and FGF data. Adding this provider to MapGuide is easy:

• Drop the three files in the zipfile into your Server/bin/fdo directory
• Edit your main providers.xml file to include the SQLite provider using the included XML snippet
• Restart MapGuide

You will need some data. Testing can be done with SQLite files from the OGR sample data directory, but you will eventually want to use your own. It’s fairly simple to convert SDF and SHP. Open up a command window in your Server/bin/fdo directory and type something like:

SQLiteConverter.exe c:\src.sdf c:\dest.db

When creating a new data connection to this file, the provider only takes one configuration parameter: the full path to the file. If you run into any bugs, please post them on the FDO Trac instance.

OGR users that are tracking the trunk build can also try this out. With some amazement, I recently found that the enhancements to this driver had already been documented… obviously OGR places a premium on timely docs. ogr2ogr allows you to do a similar import operation, probably something like (untested):

ogr2ogr -f "SQLite" -dsco FORMAT=WKB dest.db src.shp


You can use additional ogr2ogr arguments to ensure that destination spatial reference and geometry type are written to the metadata tables.

Interestingly enough, a common SQLite GIS specification has been kicked around for quite some time. Last year it was discussed on the OSGeo Discuss mailing list, and more recently further discussion was held on the PostGIS mailing list and a wiki page was set up to collaborate on this idea. Obviously, there is considerable interest within the community. My personal hope is that this specification helps the idea of SQLite as a GIS data store take off.

One area where it could be improved is some kind of integration with Alessandro Furieri’s SpatiaLite extension for SQLite that allows common RDBMS GIS functionality in a native SQLite interface. Unfortunately, neither Frank nor Traian had the cycles to integrate this extension’s data format into the specification or the code at this point. Maybe we’ll get lucky and Alessandro will decide to somehow support this spec, but if not I hope there will be some convergence in the long run.

I know that there was more that I wanted to say, but it’s getting late and I don’t even have time to cut the extra gunk out of this post. Happy SQLiting!

-J

Related posts

• OSGeo BC Meeting During GeoWeb (2)
• OSGeo BC Local Chapter First Meeting (0)
• OSGeo BC - Formation and First Meeting (0)
• MapGuide Open Source serves up SQL Server Spatial (0)
• MapGuide Open Source 2.0 beta1: Chock full o’ goodness (2)