D 2018-08-21T18:17:34.747
L Virtual\sTables\s(misc)
U sandro
W 6288
<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 externale 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; 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 be always 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 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>uppercase</b> will be always assumed (default setting).</li>
</ul></li>
</ul><br>
<table bgcolor="#ffffa0" cellpadding="10"><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>
<hr>
<h1>VirtualDBF</h1>
The <b>VirtualDBF</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 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>shp_path</b> (<i><b>mandatory</b></i>): the relative or absolute pathname leading to the externale DBF file.
<ul>
<li><b>Note</b>: the full path 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 be always 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 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>uppercase</b> will be always assumed (default setting).</li>
</ul></li>
</ul><br>
<table bgcolor="#ffffa0" cellpadding="10"><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>
<a href="https://www.gaia-gis.it/fossil/libspatialite/home">back to main page</a>
Z f315825848ce19031af56f08e96aa444