D 2013-08-13T15:51:47.598
L tutorial
P 79a84ad020572f3a0bf81004be49dac7aa185c14
U sandro
W 11440
Back to <b>VirtualPG</b> <a href="https://www.gaia-gis.it/fossil/virtualpg/index">home page</a>
<br><br>
<h3>about VirtualPG</h3>
<b>VirtualPG</b> is a <i>loadable dynamic extension module</i> for both <b>SQLite</b> and <b>SpatiaLite</b>; its intended scope is supporting direct data exchange form/to <b>PostgreSQL</b> and <b>PostGIS</b> targets simply using basic, plain <i>SQL statements</i>.<br>
The extension module simply is an ordinary <i>dynamic library</i> (<b>.dll</b> on Windows, <b>.so</b> on Unix and Linux, <b>.dylib</b> on Mac Os X); so the first thing to be done in order to activate this extension is loading the corresponding library from a valid SQLite/SpatiaLite connection.
<verbatim>
SELECT load_extension('libvirtualpg-1');
------
NULL
</verbatim>
<table bgcolor="#ffffe8" width="100%">
<tr><td align="center"><b>Useful hints and suggestions: troubleshooting</b></td></tr>
<tr><td><hr></td></tr>
<tr><td>
This operation could eventually fail for several different reasons: here are the most frequents:
<ul>
<li>You are using some odd version of SQLite3/libsqlite3 forbidding to load any dynamic extensions.<br>
This feature could eventually be disabled at build time; if this is your case you simple have to switch to some different SQLite toolkit supporting dynamic extension or build one by yourself.</li>
<li>The operating system could be unable to correctly locate the dynamic library to be loaded.<br>
The most recent versions of SQLite are smart enough to automatically handle any platform specific suffix: so you are no longer required to specify any <b>.dll</b> or <b>.so</b> suffix. If you wish to do so, you can eventually specify the full path leading to the dynamic library, this including any suffix.<br>
Anyway the default behaviour of <b>load_extension()</b> is the one to search the required dynamic library following the standard platform specific rules.
</li>
<li>The dynamic library itself could be correctly located, but some further depending library could not, thus forbidding to load the main library.<br>
<u>Please note</u>: <b>VirtualPG</b> depends on the PostgreSQL own client library (<b>libpq</b>).</li>
<li>In any case, if you are using some interactive tool such as <b>sqlite3</b>, <b>spatialite</b> or <b>spatialite_gui</b> some useful and meaningful error message will surely be displayed explaining the failure cause.<br>
This could not be supported while attempting to load an extension from some language binding (e.g. <b>.NET</b>, <b>Python</b> or <b>Java</b>).
In this case attempting to identify the real error cause could be really painful due to poor and sloppy diagnostic support.</li>
<li>There are no universal rules dictating where to place your extension modules; a good suggestion is to always place all them in the most appropriate system directory.</li>
</ul>
<b>Important notice</b>: the very recent SQLite <b>3.7.17</b> introduced many interesting advanced features to <b>load_extension()</b>;
earlier versions of SQLite could probably be someway limited.
If this is your case, please consider that:
<ul>
<li>specifying any <b>.dll</b>, <b>.so</b> or <b>.dylib</b> suffix could be strictly required.</li>
<li>as it could be required to explicitly specify the extension entry point.</li>
<li>accordingly to all this, you'll probably be required to pass arguments like to following ones to <b>load_extension()</b> while using some obsolete SQLite3 version.</li>
<verbatim>
SELECT load_extension('/usr/local/lib/libvirtualpg.so', 'sqlite3_virtualpg_init');
------
NULL
</verbatim>
</ul>
</td></tr>
</table><br>
<hr>
<h3>cross-DBMS data exchange: SQL and VirtualPostgres tables</h3>
After successfully loading the <b>virtualpg</b> dynamic extension you are now ready to create <b>VirtualPostges</b> tables.<br>
These are <i>quite almost</i> ordinary DB tables as any other; you can execute any regular SQL statement (<i>SELECT</i>, <i>INSERT</i>, <i>UPDATE</i> or <i>DELETE</i>) by specifying these Virtual Tables as the selected target.<br>
There is only a strong difference distinguishing a <b>VirtualPostgres</b> table; it's physical location isn't within the currently connected SQLite DB, but is instead within some <b>PostgreSQL</b> DBMS.<br>
So a VirtualPostgres table effectively acts as a <i>bridge</i> joining SQLite and Postgres, and allowing to perform any kind of cross data exchange (<i>in both directions</i>) in the easiest way.
<verbatim>
CREATE VIRTUAL TABLE pg_in
USING VirtualPostgres ('host=localhost port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);
</verbatim>
This SQL statement will create into the SQLite DB a table named <b>pg_in</b>; anyway the data physical storage for this table will be into the Postgres DBMS:
<ul>
<li>the first argument to VirtualPostgres is the <i>connection string</i> required in order to establish a connection to the Postgres DBMS server.</li>
<li>second and third arguments respectively are the <i>schema name</i> and the <i>table name</i> identifying the Postgres target table.</li>
</ul>
<verbatim>
CREATE VIRTUAL TABLE pg_in
USING VirtualPostgres ('hostaddr=192.168.1.91 port=5432 dbname=gis user=sandro password=secret', my_schema, my_table);
</verbatim>
Same as above, the time establishing a connection to a Postgres instance running on some different computer on the same local area network.<br>
Please consult the Postgres own documentation for any other detail about <i>connection strings</i>.
<verbatim>
PRAGMA table_info(pg_in);
</verbatim>
You can execute the above PRAGMA directive in order to identify all columns declared by the Postgres table, and their corresponding data-type.<br>
If you are actually using <b>spatialite_gui</b> you can check the same infos by using the appropriate graphics tools.
<verbatim>
SELECT a, b, c, Count(*) AS cnt
FROM pg_in
WHERE e IS NOT NULL
GROUP BY a, b, c
ORDER BY cnt DESC;
</verbatim>
You can execute any kind of <b>SELECT</b> query using a VirtualPostgres table, exactly as if it was a genuine native table.
<verbatim>
CREATE TABLE imported_from_pg AS
SELECT * FROM pg_in;
</verbatim>
And you can obviously create and populate a local copy of the remote Postgres table: now all data are permanently stored within your SQLite DB.
<verbatim>
DROP TABLE pg_in;
</verbatim>
So you can finally drop the VirtualPostgres table, and this will immediately terminate the connection to the Postgres DBMS.<br><br>
<hr>
<h3>data-type mapping and related conversions</h3>
Rather obviously SQLite and Postgres supports very different kind of data-types. The few following rules apply:
<ul>
<li>any kind of <b>INTEGER</b> value is preserved as such.</li>
<li>the same is for <b>FLOAT</b> values.</li>
<li><b>MONEY</b> and <b>NUMERIC</b> values are converted into <b>FLOAT</b> values.</li>
<li><b>CHAR</b> and <b>VARCHAR</b> values are always converted into <b>TEXT</b> values.</li>
<li><b>DATE</b>, <b>TIME</b> and <b>TIMESTAMP</b> values are always converted into <b>FLOAT</b> values corresponding to the <a href="http://www.sqlite.org/lang_datefunc.html">JulianDay</a> notation supported by SQLite.<br>
You can then invoke the appropriate function between <b>Date()</b>, <b>Time()</b> or <b>DateTime()</b> so to get back the standard SQL notation for dates and times.</li>
<li><b>BOOL</b> values are always converted into <b>INTEGER</b> values: <b>0</b> corresponding to <b>FALSE</b>.</li>
<li>Postgres supports multiple values to be stored into the same column (<b>ARRAY</b>): SQLite has no similar capability, so any ARRAY value will simply be converted into a <b>TEXT</b> string, e.g. as in <b>'{1,2,3}'</b> or <b>'{alpha,beta,gamma,delta}'</b>.</li>
<li>any binary value (as e.g. <b>BYTEA</b>) will be simply converted into a <b>TEXT</b> value containing the corresponding hexadecimal notation, e.g. as in <b>'0101000020E8640000C4FC34BA8448244150C1B5D541B85341'</b>.</li>
</ul><br>
<hr>
<h3>handling PostGIS geometries</h3>
PostGIS own geometries simply are represented by a <b>BYTEA</b> value to be interpreted accordingly to <b>EWKB</b> format specifications.
<verbatim>
CREATE TABLE from_postgis AS
SELECT * FROM pg_in;
UPDATE from_postgis SET geometry = GeomFromEWKB(geometry);
SELECT DISTINCT ST_GeometryType(geometry), ST_Srid(geometry), CoordDimension(geometry)
FROM from_postgis;
------------
LINESTRING|25832|XY
SELECT RecoverGeometryColumn('from_postgis', 'geometry', 25832, 'LINESTRING', 'XY');
------------
1
</verbatim>
This sequence of SQL statements (SpatiaLite support is strictly required) will transfer a fully qualified Geometry table from PostGIS to SpatiaLite:
<ul>
<li><b>step 1</b>: first we'll transfer the whole PostGIS table into SQLite; geometries are still represented by their <b>EWKB</b> hexadecimal notations.</li>
<li><b>step 2</b>: then we'll use the <b>GeomFromEWKB()</b> SQL function so to get the corresponding <b>SpatiaLite's BLOB</b> for each geometry.</li>
<li><b>step 3</b>: now we simply have to discover the Type, SRID and Dimensions declared by these geometries.</li>
<li><b>step 4</b>: and finally we can now recover a full qualified SpatiaLite's own (<i>native</i>) Geometry column.</li>
</ul><br>
<hr>
<h3>exporting data from SQLite to Postgres/PostGIS</h3>
For rather obvious security reasons, all VirtualPostgres tables are declared to be <i>read-only</i> by default.<br>
This actually means that you are enabled to execute any possible <b>SELECT</b> query, but any <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> operation affecting the Postgres DBMS is strictly forbidden.
<verbatim>
CREATE VIRTUAL TABLE pg_out
USING VirtualPostgres ('hostaddr=192.168.1.91 port=5432 dbname=gis user=sandro password=secret', my_schema, my_table, W);
</verbatim>
Anyway you can explicitly request a VirtualPostgres table to support unconstrained <i>read-write</i> operations, this including any <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement affecting the Postgres DBMS.<br>
In this case you simply have to pass a further fourth argument into the <b>USING</b> clause exactly corresponding to the letter <b>W</b>.
<verbatim>
DELETE FROM pg_out;
INSERT INTO pg_out (fld_a, fld_b, fld_c, geometry)
SELECT fld_a, fld_b, fld_c, AsEWKB(geometry)
from some_table;
</verbatim>
In the above example we'll delete first any already existing row from the PostGIS table; then we'll simply transfer the whole table from SpatiaLite into PostGIS.<br>
Please note, we'll invoke the <b>AsEWKB()</b> SQL function in order to convert any SpatiaLite's own <b>BLOB</b> geometry into the equivalent <b>BYTEA EWKB</b> notation required by PostGIS.<br>
<table bgcolor="#ffffe8" width="100%">
<tr><td align="center"><b>Caveat: AsEWKB()</b></td></tr>
<tr><td><hr></td></tr>
<tr><td>
Any previous version of SpatiaLite (this including the current <b>4.1.1</b>) supported a buggish <b>AsEWKB()</b> implementation completely unable to create valid EWKB Geometries to be passed to PostGIS.
I presume that nobody really attempted to use this function before, so this bug passed completely unnoticed since now.<br>
Using the latest version of the code available from the Fossil repository is strictly required so to really get a working AsEWKK().
</td></tr>
</table>
<br>
Back to <b>VirtualPG</b> <a href="https://www.gaia-gis.it/fossil/virtualpg/index">home page</a>
Z a09e810d88d5c0afedb937f2ea1b40e7