D 2018-08-22T08:55:52.276
L Virtual\sTables\s(misc)
P c14fe1d90e3238e66d8c5e1b1690a50c316e8d30
U sandro
W 13210
<a href="https://www.gaia-gis.it/fossil/libspatialite/home">back to main page</a>
<h1>VirtualShape</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 memeber.</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>
</td></tr></table>
<br>
<hr>
<h1>VirtualDBF</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 memeber.</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>
</td></tr></table>
<br>
<hr>
<h1>VirtualText</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 random choosen delimiters and 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>
</td></tr></table>
<br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/home">back to main page</a>
Z 4c001e89bc107ddb0dade1c1ce9fa2cb