VirtualOGR

Back to main page

What is VirtualOGR ?

Just a very quick recall of some basic notions about the underlying architecture.
SQLite has the capability to dynamically load an external library (i.e. a DLL on Windows, a shared library on Linux, and so on); obviously SpatiaLite as well inherits such capability from SQLite. Formally speaking, SpatiaLite itself simply simply is an extension library extending the basic capabilities of SQLite.

An extension library could add more features to SQLite in two different ways:

• implementing some further SQL function, not initially supported by the basic core alone.
• implementing a Virtual Table driver, thus allowing the SQL engine to directly access an external datasource (i.e. data placed somewhere else outside the DB itself).

GDAL (Geospatial Data Abstraction Library) is a well known open source library supporting lots of different datasources. The OGR Simple Feature Library is directly included within GDAL, and is specifically focused on supporting datasources of the Vector type.

The next-to-come GDAL 1.10 (currently available from the trunk repository) will support VirtualOGR (thanks to Even Rouault highly appreciated development work).
This practically means that the whole OGR library will automatically become a loadable extension to SQLite; and any datasource supported by OGR will be directly accessible by SQL on SQLite/SpatiaLite as if it was an ordinary table within the DB.
You can find many technical details about VirtualOGR on the appropirate documentation page.

Why is VirtualOGR expected to be useful ?

About seventy different data formats are currently supported by OGR. If you are interested to learn more, you can usefully consult the complete list.
Very shortly said: OGR has the capability to access lots of widespread data formats: ESRI Shapefiles, GML, DWG, DXF ...
And OGR can access the most popular Spatial DMBSes as well: PostgreSQL, Oracle, SQL Server, MySQL ...

So the VirtualOGR mechanism offers an easy and painless way enabling SQLite/SpatiaLite to directly access lots of different datasources (practically any possible data format or storage system you can usually encounter in the GeoSpatial industry).
And this one surely is a really good new for many users, because this way transferring data (in both directions) between SpatiaLite and another DBMS will become an easy and quite trivial ordinary administration affair.

Preparing the test environment

Bad new: in order to perform some test you are surely required to build (by yourself) both SpatiaLite and GDAL starting from the current development (aka trunk) sources available from the corresponding code repositories. Anyway the situation will become much more simple during 2013, when both softwares will become widely available as pre-built packages in mainstream distributions.

I've personally used the following test environment:

• Debian Squeeze 32 bit
• GDAL 1.10dev
• libspatialite 4.0.0
• spatialite_gui 1.6.0

Please note: on my own Debian system I installed all custom-built libraries (libspatialite and libgdal) on the default target /usr/local/lib
So I had then to explicitly declare the following variable in order to include this directory into the search path (this step should not be strictly required using a different Linux distro, e.g. Fedora):

export "LD_LIBRARY_PATH=/usr/local/lib"

Very quick VirtualOGR how-to (showing a practical example)

Step #1 we'll start loading libgdal as a dynamical extension to SQLite; if we were on Windows we obviously had to load a file named libgdal.dll (or something like; it usually depends on packagers choices)
Step #2 now we'll try to connect a PostGIS table l09, located in the schema merano of the database gis the first argument PG:dbname=gis selects a connection to PostgreSQL the second argument 0 simply intends read-only mode the third argument merano.l09 specifies the schema and table names
Step #3 just a simple test query to check if anything works as expected
Step #4 we'll now check the Geometry-Type and SRID of geometries contained into the PostGIS table. For some odd reason the SRID is reported as -1 (undefined); anyway a direct query on PostGIS reports SRID=25832 (Could be I've missed to set some magic argument into the connection string ?)
Step #5 now we'll create a genuine SpatiaLite own table
Step #6 we'll then add a Geometry column to the previously created table
Step #7 and finally we'll create a Spatial Index on this geometry column
Step #8 now we simply have to execute a very trivial INSERT INTO ... SELECT SQL statement, and the whole table will be directly imported into SpatiaLite from PostGIS
Step #9 end of the story: now we simply have to disconnect the PostGIS table and we've completely finished our job with full success. Really amazing as simple as drinking a glass of water !!!

---

Back to main page