Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

back to main page

List of topics

• VirtualShape - Emulation of Shape-Files
• VirtualDBF - Emulation of DBF Tables
• VirtualText - Emulation of TXT/CSV-Files
• VirtualXL - Emulation of MS Excel spreadsheets
• VirtualFDO - Emulation of FDO binary Geometries (GDAL)
• VirtualGPKG - Emulation of GeoPackage-Databases

VirtualShape

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 );

• shp_path (mandatory): the relative or absolute pathname leading to the external 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 by VirtualShape, and consequently the shp_path argument must not specify any suffix, 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, CP1252, ISO-8849-1
  • You can check the full list of supported charset encodings 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 always be assumed (default setting).
• colname_case (optional): this argument specifies if all column names should be considered 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 member.
  • if not specified, or if any other definition definition is found, lowercase will always be assumed (default setting).

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' );

VirtualDBF

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 );

• dbf_path (mandatory): the relative or absolute pathname leading to the external DBF file.
  • Note: the full pathname 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, CP1252, ISO-8849-1
  • You can check the full list of supported charset encodings 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 always be assumed (default setting).
• colname_case (optional): this argument specifies if all column names should be considered 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 member.
  • if not specified, or if any other definition definition is found, lowercase will always be assumed (default setting).

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' );

VirtualText

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 );

• textfile_path (mandatory): the relative or absolute pathname leading to the external Text file.
  • Note: the full pathname must be passed, including an eventual final suffix (*.txt, *.csv and alike).
• charset_encoding (mandatory): canonical name of the charset encoding adopted by the Text file.
  • Examples of valid names are: ASCII, UTF-8, CP850, CP1252, ISO-8849-1
  • You can check the full list of supported charset encodings here.
• first_row_as_titles (optional): this is a BOOLEAN flag:
  • if set to 0 (FALSE) all column names will be automatically set by using a progressive sequence.
  • if set to any other integer value (TRUE) all column names will by expected to be defined in the very first row of the Text file.
  • if not explicitly specified, TRUE will always be assumed (default setting).
• decimal_separator (optional): the character acting as a separator between the integer and decimal parts of a number.
  Valid values are any printable character or the special mnemonic notations POINT or COMMA.
  If not explicitly specified, POINT will always be assumed (default setting)
• text_separator (optional): the character acting as a string delimiter.
  Valid values are any printable character or the special mnemonic notations SINGLEQUOTE, DOUBLEQUOTE or NONE.
  If not explicitly specified, DOUBLEQUOTE will always be assumed (default setting)
• field_separator (optional): the character acting as a delimiter between consecutive fields.
  Valid values are any printable character or the special mnemonic notations TAB.
  If not explicitly specified, TAB will always be assumed (default setting)

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' );

VirtualXL

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 );

• xls_path (mandatory): the relative or absolute pathname leading to the external MS Excel spreadsheet (old binary format *.xls).
• worksheet_index (optional): the index identifying one of the worksheets within the Excel document.
  MS Excel may contain more than one worksheet, and each individual worksheet is identified by its own index (zero-based index; the first worksheet always has index=0).
  If not explicitly specified the first worksheet will always be assumed (default value).
• first_row_as_titles (optional): this is a BOOLEAN flag:
  • if set to 0 (FALSE) all column names will be automatically set by using a progressive sequence.
  • if set to any other integer value (TRUE) all column names will by expected to be defined in the very first row of the Spreadsheet.
  • if not explicitly specified, TRUE will always be assumed (default setting).

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 );

VirtualFDO

SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo ( table_name );

• table_name (mandatory): 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.

SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo( my_parcels );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo( 'my_parcels' );

VirtualGPKG

SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpkg ( table_name );

• table_name (mandatory): 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.

SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpks( my_parcels );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpkg( 'my_parcels' );