D 2018-08-21T18:17:34.747
L Virtual\sTables\s(misc)
U sandro
W 6288
back to main page
VirtualShape
The VirtualShape Interface allows to directly access an external ShapeFile by using standard Spatial SQL queries.
Syntax for creating a VirtualShape Table:
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 );
Supported arguments and their interpretation:
shp_path (mandatory): the relative or absolute pathname leading to the externale Shapefile.
Note: any Shapefile requires at least three individual members sharing the same base name but with different suffixes (*.shp, *.shx and *.dbf.
All suffixes will be automatically handled; the shp_path argument must not specify any prefix, just the bare base path must be passed.
charset_encoding (mandatory): canonical name of the charset encoding adopted by the DBF member.
Examples of valid names are: ASCII, UTF-8, CP850, ISO-8849-1
You can check the full list of supported charset encodings from here.
srid (mandatory): the SRID value of all Geometries within the Shapefile.
Examples of valid SRID values are: 4326, 3003, 32632
text_dates (optional): this is a BOOLEAN flag:
if set to 0 (FALSE) all fields of the DATE type will be represented as julian dates
if set to any other integer value (TRUE) all fields of the DATE type will be represented as Text Strings as e.g. 2018-08-21
if not explicitly specified, TRUE will be always assumed (default setting).
colname_case (optional): this argument specifies if all column names should be uppercase or lowercase.
Valid settings are:
uppercase or upper: all column names will be fully capitalized.
lowercase or lower: all column names will be full lowercase.
samecase or same: all column names will be exactly preserved as they are defined into the DBF memeber.
if not specified, or if any other definition definition is found, uppercase will be always assumed (default setting).
Note: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
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' );
Both notations will produce exactly the same identical result.
VirtualDBF
The VirtualDBF Interface allows to directly access an external ShapeFile by using standard Spatial SQL queries.
Syntax for creating a VirtualShape Table:
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 );
Supported arguments and their interpretation:
shp_path (mandatory): the relative or absolute pathname leading to the externale DBF file.
Note: the full path must be passed, including the final *.dbf suffix.
charset_encoding (mandatory): canonical name of the charset encoding adopted by the DBF member.
Examples of valid names are: ASCII, UTF-8, CP850, ISO-8849-1
You can check the full list of supported charset encodings from here.
text_dates (optional): this is a BOOLEAN flag:
if set to 0 (FALSE) all fields of the DATE type will be represented as julian dates
if set to any other integer value (TRUE) all fields of the DATE type will be represented as Text Strings as e.g. 2018-08-21
if not explicitly specified, TRUE will be always assumed (default setting).
colname_case (optional): this argument specifies if all column names should be uppercase or lowercase.
Valid settings are:
uppercase or upper: all column names will be fully capitalized.
lowercase or lower: all column names will be full lowercase.
samecase or same: all column names will be exactly preserved as they are defined into the DBF memeber.
if not specified, or if any other definition definition is found, uppercase will be always assumed (default setting).
Note: quoting or not any Text String when it appears as a Virtual Table argument has absolutely no effect.
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' );
Both notations will produce exactly the same identical result.