D 2012-11-03T16:22:31.697
L VirtualOGR
U sandro
W 6945
<h2>VirtualOGR</h2>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=switching-to-4.0#virtualogr">main page</a>
<h3>What is VirtualOGR ?</h3>
Just a very quick recall of some basic notions about the underlying architecture.<br>
SQLite has the capability to dynamically load an external library (i.e. a <i>DLL</i> on Windows, a <i>shared library</i> 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.<br><br>
An extension library could add more features to SQLite in two different ways:<ul>
<li>implementing some further <b>SQL function</b>, not initially supported by the basic core alone.</li>
<li>implementing a <b>Virtual Table driver</b>, thus allowing the SQL engine to directly access an external datasource (i.e. data placed somewhere else outside the DB itself).</li>
</ul> <br>
<b>GDAL</b> (<a href="http://www.gdal.org/">Geospatial Data Abstraction Library</a>) is a well known open source library supporting lots of different datasources.
The <b>OGR</b> <a href="http://www.gdal.org/ogr/">Simple Feature Library </a> is directly included within GDAL, and is specifically focused on supporting datasources of the <b>Vector</b> type.<br><br>
The next-to-come <b>GDAL 2.0</b> (currently available from the <i>trunk</i> repository) will support <b>VirtualOGR</b> (thanks to Even Rouault highly appreciated development work).<br>
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.
<h3>Why is VirtualOGR expected to be useful ?</h3>
About seventy different data formats are currently supported by OGR.
If you are interested to learn more, you can usefully consult the <a href="http://www.gdal.org/ogr/ogr_formats.html">complete list</a>.<br>
Very shortly said: OGR has the capability to access lots of widespread data formats: ESRI Shapefiles, GML, DWG, DXF ...<br>
And OGR can access the most popular Spatial DMBSes as well: PostgreSQL, Oracle, SQL Server, MySQL ...<br><br>
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).<br>
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 <i>ordinary administration</i> affair.
<h3>Preparing the test environment</h3>
Bad new: in order to perform some test you are surely required to build (by yourself) both SpatiaLite and GDAL starting from the current <i>development</i> (aka <i>trunk</i>) 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.<br><br>
I've personally used the following test environment:<ul>
<li>Debian Squeeze 32 bit</li>
<li>GDAL 2.0dev</li>
<li>libspatialite 4.0.0-RC2</li>
<li>spatialite_gui 1.6.0</li>
</ul><br>
<u>Please note</u>: on my own Debian system I installed all custom-built libraries (<i>libspatialite</i> and <i>libgdal</i>) on the default target <b>/usr/local/lib</b><br>
So I had then to explicitly declare the following variable in order to include this directory into the search path (<i>this step should not be strictly required using a different Linux distro, e.g. Fedora</i>):<br><br>
<b>export "LD_LIBRARY_PATH=/usr/local/lib"</b><br><br>
<h3>Very quick VirtualOGR how-to (showing a practical example)</h3>
<table cellspacing="6" cellpadding="6">
<tr><td><b>Step #1</b><br>we'll start loading <i>libgdal</i> as a dynamical extension to SQLite; if we were on Windows we obviously had to load a file named <i>libgdal.dll</i> (<i>or something like; it usually depends on packagers choices</i>)</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-1.png" alt="load_extension"></td></tr>
<tr><td><b>Step #2</b><br>now we'll try to connect a PostGIS table <b>l09</b>, located in the schema <b>merano</b> of the databases <b>gis</b><br><br><ul>
<li>the first argument <b>PG:dbname=gis</b> selects a connection to PostgreSQL</li>
<li>the second argument <b>0</b> simply intends <i>read-only</i> mode</li>
<li>the third argument <b>merano.l09</b> specifies the schema and table names</li>
</ul></td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-2.png" alt="create_virtual_table"></td></tr>
<tr><td><b>Step #3</b><br>just a simple test query to check if anything works as expected</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-3.png" alt="test_query"></td></tr>
<tr><td><b>Step #4</b><br>we'll now check the Geometry-Type and SRID of geometries contained into the PostGIS table.<br><br>
For some odd reason the SRID is reported as <b>-1</b> (<i>undefined</i>); anyway a direct query on PostGIS reports <b>SRID=25832</b><br>(<i>Could be I've missed to set some magic argument into the connection string ?</i>)</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-4.png" alt="check_type_srid"></td></tr>
<tr><td><b>Step #5</b><br>now we'll create a <i>genuine</i> SpatiaLite own table</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-5.png" alt="create_table"></td></tr>
<tr><td><b>Step #6</b><br>we'll then add a Geometry column to the previously created table</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-6.png" alt="add_geomety_column"></td></tr>
<tr><td><b>Step #7</b><br>and finally we'll create a Spatial Index on this geometry column</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-7.png" alt="create_spatial_index"></td></tr>
<tr><td><b>Step #8</b><br>now we simply have to execute a very trivial<br>
<b>INSERT INTO ... SELECT</b><br>
SQL statement, and the whole table will be directly imported into SpatiaLite from PostGIS</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-8.png" alt="populating_table"></td></tr>
<tr><td><b>Step #9</b><br>end of the story: now we simply have to disconnect the PostGIS table and we've completely finished our job with full success.
<h2>Really amazing</h2>
as simple as drinking a glass of water !!!</td>
<td><img border="1" src="http://www.gaia-gis.it/gaia-sins/virtualogr/ogr-9.png" alt="drop_virtual"></td></tr>
</table>
<br><hr>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=switching-to-4.0#virtualogr">main page</a>
Z bc2ced79baea560727e67be934046ba0