D 2018-08-22T15:43:30.461
L Virtual\sTables\s(misc)
P b909a6392335b23bfdfa8458f2ea91059b4ced60
U sandro
W 22880
<a href="https://www.gaia-gis.it/fossil/libspatialite/home">back to main page</a>
<h1><a id="VirtualShape">VirtualShape</a></h1>
The <b>VirtualShape</b> Interface allows to directly access an external ShapeFile by using standard Spatial SQL queries.<br>
Syntax for creating a VirtualShape Table:
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape ( shp_path , charset_encoding , srid );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape ( shp_path , charset_encoding , srid , text_dates );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape ( shp_path , charset_encoding , srid , text_dates , colname_case );
</verbatim>
Supported arguments and their interpretation:
<ul>
<li><b>shp_path</b> (<i><b>mandatory</b></i>): the relative or absolute pathname leading to the external Shapefile.
<ul>
<li><b>Note</b>: any Shapefile requires at least three individual members sharing the same base name but with different suffixes (<i>*.shp</i>, <i>*.shx</i> and <i>*.dbf</i>).<br>
All suffixes will be automatically handled by VirtualShape, and consequently the <b>shp_path</b> argument <b><u><i>must not specify any prefix</i></u></b>, just the bare base path must be passed.</li>
</ul></li>
<li><b>charset_encoding</b> (<i><b>mandatory</b></i>): canonical name of the charset encoding adopted by the <b>DBF</b> member.
<ul>
<li>Examples of valid names are: <b>ASCII</b>, <b>UTF-8</b>, <b>CP850</b>, <b>ISO-8849-1</b></li>
<li>You can check the full list of supported charset encodings from <a href="https://www.gnu.org/software/libiconv/">here</a>.</li>
</ul></li>
<li><b>srid</b> (<i><b>mandatory</b></i>): the SRID value of all Geometries within the Shapefile.
<ul>
<li>Examples of valid SRID values are: <b>4326</b>, <b>3003</b>, <b>32632</b></li>
</ul></li>
<li><b>text_dates</b> (<i><b>optional</b></i>): this is a <b>BOOLEAN</b> flag:
<ul>
<li>if set to <b>0</b> (<b>FALSE</b>) all fields of the <b>DATE</b> type will be represented as <a href="https://www.sqlite.org/lang_datefunc.html">julian dates</a></li>
<li>if set to any other integer value (<b>TRUE</b>) all fields of the <b>DATE</b> type will be represented as Text Strings as e.g. <b>2018-08-21</b></li>
<li>if not explicitly specified, <b>TRUE</b> will always be assumed (default setting).</li>
</ul></li>
<li><b>colname_case</b> (<i><b>optional</b></i>): this argument specifies if all column names should be considered uppercase or lowercase.<br>
Valid settings are:
<ul>
<li><b>uppercase</b> or <b>upper</b>: all column names will be fully capitalized.</li>
<li><b>lowercase</b> or <b>lower</b>: all column names will be full lowercase.</li>
<li><b>samecase</b> or <b>same</b>: all column names will be exactly preserved as they are defined into the DBF member.</li>
<li>if not specified, or if any other definition definition is found, <b>lowercase</b> will always be assumed (default setting).</li>
</ul></li>
</ul><br>
<table bgcolor="#ffffd0" cellpadding="18"><tr><td>
<b>Note</b>: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape( /home/sandro/myshp , CP1252 , 3003 , 1 , lower );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualShape( '/home/sandro/myshp' , 'CP1252' , 3003 , 1 , 'lower' );
</verbatim>
Both notations will produce exactly the same identical result.
</td></tr></table>
<br>
<table bgcolor="#d0ffd0" cellpadding="18"><tr><td>
<h3>Best practices and known limitations</h3>
<ul>
<li>Virtual Tables are not really genuine Database Tables, they are just pretending to be.</li>
<li>Virtual Tables have very poor speed performances when compared to genuine Database Tables.</li>
<li>Complex SQL queries <b>JOIN</b>ing several Tables can often return odd and misleading resultsets, if one or more of the <b>JOIN</b>ed Tables are of the Virtual type.</li>
<li>The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.</li>
<li>Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.</li>
</ul><br>
<b>Warning</b>: VirtualShape Tables are always subject to <b>READ-ONLY</b> restrictions. Any attempt to to execute an <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement will always return an error.<br>
</td></tr></table>
<br>
<table bgcolor="#e0e0e0" cellpadding="18"><tr><td>
<h3>Related Topics</h3>
<ul>
<li>The <b>ImportSHP()</b> SQL function allows to create and populate a genuine Spatial Table by importing an external Shapefile.</li>
<li>And the <b>ExportSHP()</b> sister function allows to directly export a whole Spatial Table into an external Shapefile.</li>
</ul>
</td></tr></table>
<br>
<hr>
<h1><a id="VirtualDBF">VirtualDBF</a></h1>
The <b>VirtualDBF</b> Interface allows to directly access an external DBF table by using standard Spatial SQL queries.<br>
Syntax for creating a VirtualDbf Table:
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf ( dbf_path , charset_encoding );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf ( dbf_path , charset_encoding , text_dates );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf ( dbf_path , charset_encoding , text_dates , colname_case );
</verbatim>
Supported arguments and their interpretation:
<ul>
<li><b>dbf_path</b> (<i><b>mandatory</b></i>): the relative or absolute pathname leading to the external DBF file.
<ul>
<li><b>Note</b>: the full pathname must be passed, including the final <i>*.dbf</i> suffix.</li>
</ul></li>
<li><b>charset_encoding</b> (<i><b>mandatory</b></i>): canonical name of the charset encoding adopted by the <b>DBF</b> member.
<ul>
<li>Examples of valid names are: <b>ASCII</b>, <b>UTF-8</b>, <b>CP850</b>, <b>ISO-8849-1</b></li>
<li>You can check the full list of supported charset encodings from <a href="https://www.gnu.org/software/libiconv/">here</a>.</li>
</ul></li>
<li><b>text_dates</b> (<i><b>optional</b></i>): this is a <b>BOOLEAN</b> flag:
<ul>
<li>if set to <b>0</b> (<b>FALSE</b>) all fields of the <b>DATE</b> type will be represented as <a href="https://www.sqlite.org/lang_datefunc.html">julian dates</a></li>
<li>if set to any other integer value (<b>TRUE</b>) all fields of the <b>DATE</b> type will be represented as Text Strings as e.g. <b>2018-08-21</b></li>
<li>if not explicitly specified, <b>TRUE</b> will always be assumed (default setting).</li>
</ul></li>
<li><b>colname_case</b> (<i><b>optional</b></i>): this argument specifies if all column names should be considered uppercase or lowercase.<br>
Valid settings are:
<ul>
<li><b>uppercase</b> or <b>upper</b>: all column names will be fully capitalized.</li>
<li><b>lowercase</b> or <b>lower</b>: all column names will be full lowercase.</li>
<li><b>samecase</b> or <b>same</b>: all column names will be exactly preserved as they are defined into the DBF member.</li>
<li>if not specified, or if any other definition definition is found, <b>lowercase</b> will always be assumed (default setting).</li>
</ul></li>
</ul><br>
<table bgcolor="#ffffd0" cellpadding="18"><tr><td>
<b>Note</b>: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf( /home/sandro/myshp , CP1252 , 1 , lower );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualDbf( '/home/sandro/myshp' , 'CP1252' , 1 , 'lower' );
</verbatim>
Both notations will produce exactly the same identical result.
</td></tr></table>
<br>
<table bgcolor="#d0ffd0" cellpadding="18"><tr><td>
<h3>Best practices and known limitations</h3>
<ul>
<li>Virtual Tables are not really genuine Database Tables, they are just pretending to be.</li>
<li>Virtual Tables have very poor speed performances when compared to genuine Database Tables.</li>
<li>Complex SQL queries <b>JOIN</b>ing several Tables can often return odd and misleading resultsets, if one or more of the <b>JOIN</b>ed Tables are of the Virtual type.</li>
<li>The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.</li>
<li>Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.</li>
</ul><br>
<b>Warning</b>: VirtualDBF Tables are always subject to <b>READ-ONLY</b> restrictions. Any attempt to to execute an <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement will always return an error.<br>
</td></tr></table>
<br>
<table bgcolor="#e0e0e0" cellpadding="18"><tr><td>
<h3>Related Topics</h3>
<ul>
<li>The <b>ImportDBF()</b> SQL function allows to create and populate a genuine Database Table by importing an external DBF file.</li>
<li>And the <b>ExportDBF()</b> sister function allows to directly export a whole Database Table into an external DBF file.</li>
</ul>
</td></tr></table>
<br>
<hr>
<h1><a id="VirtualText">VirtualText</a></h1>
The <b>VirtualText</b> Interface allows to directly access an external Text file by using standard Spatial SQL queries.<br>
The Text File is expected to contain a properly formatted Table accordingly to some <b>Delimiter-Separated-Values</b> format (comma separated values <i>*.csv</i>, tab separated values <i>*.txt</i> and alike).<br>
Syntax for creating a VirtualText Table:
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles , decimal_separator );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles , decimal_separator , text_separator );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText ( textfile_path , charset_encoding , first_row_as_titles , decimal_separator , text_separator , field_separator );
</verbatim>
Supported arguments and their interpretation:
<ul>
<li><b>textfile_path</b> (<i><b>mandatory</b></i>): the relative or absolute pathname leading to the external Text file.
<ul>
<li><b>Note</b>: the full pathname must be passed, including an eventual final suffix (<i>*.txt</i>, <i>*.csv</i> and alike).</li>
</ul></li>
<li><b>charset_encoding</b> (<i><b>mandatory</b></i>): canonical name of the charset encoding adopted by the Text file.
<ul>
<li>Examples of valid names are: <b>ASCII</b>, <b>UTF-8</b>, <b>CP850</b>, <b>ISO-8849-1</b></li>
<li>You can check the full list of supported charset encodings from <a href="https://www.gnu.org/software/libiconv/">here</a>.</li>
</ul></li>
<li><b>first_row_as_titles</b> (<i><b>optional</b></i>): this is a <b>BOOLEAN</b> flag:
<ul>
<li>if set to <b>0</b> (<b>FALSE</b>) all column names will be automatically set by using a progressive sequence.</li>
<li>if set to any other integer value (<b>TRUE</b>) all column names will by expected to be defined in the very first row of the Text file.</li>
<li>if not explicitly specified, <b>TRUE</b> will always be assumed (default setting).</li>
</ul></li>
<ul>
</ul></li>
<li><b>decimal_separator</b> (<i><b>optional</b></i>): the character acting as a separator between the integer and decimal parts of a number.<br>
Valid values are any printable character or the special mnemonic notations <b>POINT</b> or <b>COMMA</b>.<br>
If not explicitly specified <b>POINT</b> will always be assumed (default setting)</li>
<li><b>text_separator</b> (<i><b>optional</b></i>): the character acting as a string delimiter.<br>
Valid values are any printable character or the special mnemonic notations <b>SINGLEQUOTE</b>, <b>DOUBLEQUOTE</b> or <b>NONE</b>.<br>
If not explicitly specified <b>DOUBLEQUOTE</b> will always be assumed (default setting)</li>
<li><b>field_separator</b> (<i><b>optional</b></i>): the character acting as a delimiter between consecutive fields.<br>
Valid values are any printable character or the special mnemonic notations <b>TAB</b>.<br>
If not explicitly specified <b>TAB</b> will always be assumed (default setting)</li>
</ul><br>
<table bgcolor="#ffffd0" cellpadding="18"><tr><td>
<b>Note</b>: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText( /home/sandro/myfile.txt , CP1252 , 1 , POINT , NONE , TAB );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualText( '/home/sandro/myfile.txt' , 'CP1252' , 1 , 'POINT' , 'NONE' , 'TAB' );
</verbatim>
Both notations will produce exactly the same identical result.
</td></tr></table>
<br>
<table bgcolor="#d0ffd0" cellpadding="18"><tr><td>
<h3>Best practices and known limitations</h3>
<ul>
<li><b>Be aware</b>: opening big or huge text files could easily require massive RAM allocations.</li>
<li><b>Warning</b>: attempting to access an external Text file by blindly specifying randomly chosen delimiters or separators may easily have catastrophic effects.<br>
Always carefully check the actual payload of the Text file before attempting to access it.</li>
<li>Virtual Tables are not really genuine Database Tables, they are just pretending to be.</li>
<li>Virtual Tables have very poor speed performances when compared to genuine Database Tables.</li>
<li>Complex SQL queries <b>JOIN</b>ing several Tables can often return odd and misleading resultsets, if one or more of the <b>JOIN</b>ed Tables are of the Virtual type.</li>
<li>The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.</li>
<li>Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.</li>
</ul><br>
<b>Warning</b>: VirtualText Tables are always subject to <b>READ-ONLY</b> restrictions. Any attempt to to execute an <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement will always return an error.<br>
</td></tr></table>
<br>
<hr>
<h1><a id="VirtualXL">VirtualXL</a></h1>
The <b>VirtualXL</b> Interface allows to directly access an external MS Excel spreadsheet by using standard Spatial SQL queries.<br>
Syntax for creating a VirtualXL Table:
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualXL ( xls_path );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualXL ( xls_path , worksheet_index );
SELECT CREATE VIRTUAL TABLE my_table USING VirtualXL ( xls_path , worksheet_index , first_row_as_titles );
</verbatim>
Supported arguments and their interpretation:
<ul>
<li><b>xls_path</b> (<i><b>mandatory</b></i>): the relative or absolute pathname leading to the external MS Excel spreadsheet (old binary format <i>*.xls</i>).</li>
<li><b>worksheet_index</b> (<i><b>optional</b></i>): the index identifying one of the worksheets within the Excel document.<br>
MS Excel may contain more worksheets, and each individual worksheet is identified by its own index (<b>zero-based</b> index; the first worksheet always has <b>index=0</b>).<br>
If not explicitly specified the first worksheet will always be assumed (default value).</li>
<li><b>first_row_as_titles</b> (<i><b>optional</b></i>): this is a <b>BOOLEAN</b> flag:
<ul>
<li>if set to <b>0</b> (<b>FALSE</b>) all column names will be automatically set by using a progressive sequence.</li>
<li>if set to any other integer value (<b>TRUE</b>) all column names will by expected to be defined in the very first row of the Spreadsheet.</li>
<li>if not explicitly specified, <b>TRUE</b> will always be assumed (default setting).</li>
</ul></li>
<ul>
</ul></li>
</ul><br>
<table bgcolor="#ffffd0" cellpadding="18"><tr><td>
<b>Note</b>: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualXL( /home/sandro/myspreadsheet.xls , 3, 1 );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualXL( '/home/sandro/myspreadsheet.xls' , 3, 1 );
</verbatim>
Both notations will produce exactly the same identical result.
</td></tr></table>
<br>
<table bgcolor="#d0ffd0" cellpadding="18"><tr><td>
<h3>Best practices and known limitations</h3>
<ul>
<li>Virtual Tables are not really genuine Database Tables, they are just pretending to be.</li>
<li>Virtual Tables have very poor speed performances when compared to genuine Database Tables.</li>
<li>Complex SQL queries <b>JOIN</b>ing several Tables can often return odd and misleading resultsets, if one or more of the <b>JOIN</b>ed Tables are of the Virtual type.</li>
<li>The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.</li>
<li>Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.</li>
</ul><br>
<b>Warning</b>: VirtualXL Tables are always subject to <b>READ-ONLY</b> restrictions. Any attempt to to execute an <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statement will always return an error.<br>
</td></tr></table>
<br>
<table bgcolor="#e0e0e0" cellpadding="18"><tr><td>
<h3>Related Topics</h3>
The <b>ImportXLS()</b> SQL function allows to create and populate a genuine Database Table by importing an external MS Excel spreadsheet.
</td></tr></table>
<br>
<hr>
<h1><a id="VirtualFDO">VirtualFDO</a></h1>
The <b>VirtualFDO</b> Interface allows to directly access a Spatial Table containing <b>FDO binary Geometries</b> exactly as if was a SpatiaLite's own native Table.<br>
FDO is an alternative binary format for Geometries stored within a SQLite Database; you can eventually read the <a href="https://trac.osgeo.org/fdo/wiki/FDORfc16">RFC 16</a> specification for more details.<br>
Syntax for creating a VirtualFDO Table:
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo ( table_name );
</verbatim>
Supported argument and its interpretation:
<ul>
<li><b>table_name</b> (<i><b>mandatory</b></i>): the name of the Table in the current Database containing one or more FDO Geometry Columns to be automatically translated to/from SpatiaLite's own native binary BLOB-format.</li>
</ul><br>
<table bgcolor="#ffffd0" cellpadding="18"><tr><td>
<b>Note</b>: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo( my_parcels );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo( 'my_parcels' );
</verbatim>
Both notations will produce exactly the same identical result.
</td></tr></table>
<br>
<table bgcolor="#d0ffd0" cellpadding="18"><tr><td>
<h3>Best practices and known limitations</h3>
<ul>
<li>Virtual Tables are not really genuine Database Tables, they are just pretending to be.</li>
<li>Virtual Tables have very poor speed performances when compared to genuine Database Tables.</li>
<li>Complex SQL queries <b>JOIN</b>ing several Tables can often return odd and misleading resultsets, if one or more of the <b>JOIN</b>ed Tables are of the Virtual type.</li>
<li>The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.</li>
<li>Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.</li>
</ul><br>
<b>Note</b>: VirtualFDO Tables support unrestricted <b>READ-WRITE</b> operations, this including executing <b>INSERT</b>, <b>UPDATE</b> and <b>DELETE</b> statements.<br>
</td></tr></table>
<br>
<hr>
<h1><a id="VirtualGPKG">VirtualGPKG</a></h1>
The <b>VirtualGPKG</b> Interface allows to directly access Spatial Tables from a <b>GeoPackage Database</b> exactly as if was a SpatiaLite's own native Database.<br>
GeoPackage (<i>aka</i> <b>GPKG</b>) is an alternative Spatial Database built on the top of SQLite; you can eventually read the <a href="http://www.geopackage.org/guidance/getting-started.html">official specification</a> for more details.<br>
Syntax for creating a VirtualFDO Table:
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpkg ( table_name );
</verbatim>
Supported argument and its interpretation:
<ul>
<li><b>table_name</b> (<i><b>mandatory</b></i>): the name of the Table in the current Database containing one or more GPKG Geometry Columns to be automatically translated to/from SpatiaLite's own native binary BLOB-format.</li>
</ul><br>
<table bgcolor="#ffffd0" cellpadding="18"><tr><td>
<b>Note</b>: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
<verbatim>
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpks( my_parcels );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpkg( 'my_parcels' );
</verbatim>
Both notations will produce exactly the same identical result.
</td></tr></table>
<br>
<table bgcolor="#d0ffd0" cellpadding="18"><tr><td>
<h3>Best practices and known limitations</h3>
<ul>
<li>Virtual Tables are not really genuine Database Tables, they are just pretending to be.</li>
<li>Virtual Tables have very poor speed performances when compared to genuine Database Tables.</li>
<li>Complex SQL queries <b>JOIN</b>ing several Tables can often return odd and misleading resultsets, if one or more of the <b>JOIN</b>ed Tables are of the Virtual type.</li>
<li>The best usage you can do of a Virtual Table is to just support basic data import processes, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.</li>
<li>Pretending to use a Virtual Table as a full replacement of importing all data within a genuine Database Table is strongly discouraged, and never is a smart idea.</li>
</ul><br>
<b>Note</b>: VirtualGPKG Tables support unrestricted <b>READ-WRITE</b> operations, this including executing <b>INSERT</b>, <b>UPDATE</b> and <b>DELETE</b> statements.<br>
</td></tr></table>
<br>
<hr>
<br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/home">back to main page</a>
Z b53b6e93862e16f520ded31013b01a4d