Update of "Virtual Tables (misc)"
Not logged in
Home Timeline Branches Tags Tickets Wiki Login
History Page Raw

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

Overview

Artifact ID: fd73f8e7d8513dac8e35dc3e9ec052e1fb0fa3fd
Page Name:Virtual Tables (misc)
Date: 2018-08-26 09:05:59
Original User: sandro
Parent: d2bbf69bff1fe987e5cb01bb6827b6ab2b01527f (diff)
Content

back to main page

List of topics


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:
Note: any Text String, quoted or not, when it appears as a Virtual Table argument has the same 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 the same result.

Best practices and known limitations

  • Virtual Tables are not really genuine Database Tables, they are just emulating the original source as a Database Table.
  • Virtual Tables have very poor speed performances, due to the emulation, as compared to genuine Database Tables.
  • Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, when one or more of the JOINed Tables are of the Virtual type.
  • The best use of a Virtual Table is to preform a basic data import process, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
  • 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 good idea.

Warning: VirtualShape Tables are always subject to READ-ONLY restrictions. Any attempt to to execute an INSERT, UPDATE or DELETE statement will always return an error.

Related Topics

  • The ImportSHP() SQL function allows to create and populate a genuine Spatial Table by importing an external Shapefile.
  • And the ExportSHP() sister function allows the exportation a whole Spatial Table into an external Shapefile.

VirtualDBF

The VirtualDBF Interface allows to directly access an external DBF table by using standard Spatial SQL queries.
Syntax for creating a VirtualDbf 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:
Note: any Text String, quoted or not, when it appears as a Virtual Table argument has the same 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 the same result.

Best practices and known limitations

  • Virtual Tables are not really genuine Database Tables, they are just emulating the original source as a Database Table.
  • Virtual Tables have very poor speed performances, due to the emulation, as compared to genuine Database Tables.
  • Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, when one or more of the JOINed Tables are of the Virtual type.
  • The best use of a Virtual Table is to preform a basic data import process, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
  • 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 good idea.

Warning: VirtualDBF Tables are always subject to READ-ONLY restrictions. Any attempt to to execute an INSERT, UPDATE or DELETE statement will always return an error.

Related Topics

  • The ImportDBF() SQL function allows to create and populate a genuine Database Table by importing an external DBF file.
  • And the ExportDBF() sister function allows to directly export a whole Database Table into an external DBF file.

VirtualText

The VirtualText Interface allows to directly access an external Text file by using standard Spatial SQL queries.
The Text File is expected to contain a properly formatted Table accordingly to some Delimiter-Separated-Values format (comma separated values *.csv, tab separated values *.txt and alike).
Syntax for creating a VirtualText Table: 
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 );
Supported arguments and their interpretation:
Note: any Text String, quoted or not, when it appears as a Virtual Table argument has the same effect. 
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' );
Both notations will produce the same result.

Best practices and known limitations

  • Be aware: opening big or huge text files could easily require massive RAM allocations.
  • Warning: attempting to access an external Text file by blindly specifying randomly chosen delimiters or separators may easily have catastrophic effects.
    Always carefully check the actual structure of the Text file before attempting to access it.
  • Virtual Tables are not really genuine Database Tables, they are just emulating the original source as a Database Table.
  • Virtual Tables have very poor speed performances, due to the emulation, as compared to genuine Database Tables.
  • Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, when one or more of the JOINed Tables are of the Virtual type.
  • The best use of a Virtual Table is to preform a basic data import process, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
  • 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 good idea.

Warning: VirtualText Tables are always subject to READ-ONLY restrictions. Any attempt to to execute an INSERT, UPDATE or DELETE statement will always return an error.

Related Topic

Reasons for lack of a ImportCSV() function:
  • Based on the fact that CSV and TXT/TAB are very loose and variable defined format, successfully opening and reading a Text file usually requires several attempts before you finally "guess" the most appropriate combination of delimiters/separators.
  • Therefore a 2 step import process is advised:
----------------------------------------
-- Step 1: create a virtual table using VirtualText
----------------------------------------
CREATE VIRTUAL TABLE virtual_cities1000 USING VirtualText
(
    'cities1000.txt',   -- absolute or relative path leading to the textfile 
    'UTF-8',            -- charset encoding used by the textfile
    0,                  -- does the first line contains column names [0=no, 1=yes]
    POINT,              -- the decimal separator [POINT or COMMA]
    NONE,               -- the text separator [NONE, SINGLEQUOTE or DOUBLEQUOTE]
    TAB                 -- the field separator [TAB, ',', ':' or other charater]
);
----------------------------------------
At this point, a thorough check of the results is needed:
  • do the column names make sense?
  • are the dates correct?
  • are the special characters correct ? (needed correction of charset encoding)
  • is all the data really needed ?
    • what filters are needed to import the needed data ?
----------------------------------------
-- Step 2: create a final TABLE using the virtual table source
----------------------------------------
CREATE TABLE cities_italy AS SELECT
    COL002 AS name,
    COL006 AS longitude,
    COL005 AS latitude,
    MakePoint(COL006, COL005, 4326) AS geom_wsg84,                          -- create the original POINT as Wgs84
    ST_Transform(MakePoint(COL006, COL005, 4326),32632) AS geom_utm_19n     -- create the  POINT as WGS 84 / UTM zone 32N, which the other tables use
FROM virtual_cities1000 WHERE COL009 = 'IT'                                 -- filter out all non-italien cities
;

----------------------------------------
-- such Geometries, created on the fly, must be registered:
----------------------------------------
SELECT RecoverGeometryColumn
(
    'cities_italy',         -- table-name
    'geom_wsg84',           -- geometry column-name
    4326,                   -- srid of geometry
    'POINT',                -- geometry-type
);
SELECT RecoverGeometryColumn('cities_italy','geom_utm_19n',32632,'POINT');

----------------------------------------
-- Create a Spatial-Index for the Geometries
----------------------------------------
SELECT CreateSpatialIndex
(
    'cities_italy',         -- table-name
    'geom_wsg84'            -- geometry column-name
);
SELECT CreateSpatialIndex('cities_italy','geom_utm_19n');

----------------------------------------
-- Update the metadata for the Database
----------------------------------------
SELECT UpdateLayerStatistics
(
    'cities_italy',         -- table-name
    'geom_wsg84'            -- geometry column-name
);
SELECT UpdateLayerStatistics('cities_italy','geom_utm_19n');

----------------------------------------
DROP TABLE IF EXISTS virtual_cities1000 ;
----------------------------------------

VirtualXL

The VirtualXL Interface allows to directly access an external MS Excel spreadsheet (Legacy binary format *.xls, i.e. not *.xlsm or 'LibreOffice Calc' *.ods files) by using standard Spatial SQL queries.
Syntax for creating a VirtualXL Table: 
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 );
Supported arguments and their interpretation:
Note: any Text String, quoted or not, when it appears as a Virtual Table argument has the same effect. 
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 );
Both notations will produce the same result.

Best practices and known limitations

  • Virtual Tables are not really genuine Database Tables, they are just emulating the original source as a Database Table.
  • Virtual Tables have very poor speed performances, due to the emulation, as compared to genuine Database Tables.
  • Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, when one or more of the JOINed Tables are of the Virtual type.
  • The best use of a Virtual Table is to preform a basic data import process, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
  • 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 good idea.

Warning: VirtualXL Tables are always subject to READ-ONLY restrictions. Any attempt to to execute an INSERT, UPDATE or DELETE statement will always return an error.

Related Topics

The ImportXLS() SQL function allows to create and populate a genuine Database Table by importing an external MS Excel spreadsheet.

VirtualFDO

The VirtualFDO Interface allows to directly access a Spatial Table containing FDO binary Geometries exactly as if was a SpatiaLite's own native Table.
FDO is an alternative binary format for Geometries stored within a SQLite Database; you can eventually read the RFC 16 specification for more details.
Syntax for creating a VirtualFDO Table: 
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo ( table_name );
Supported argument and its interpretation:
Note: any Text String, quoted or not, when it appears as a Virtual Table argument has the same effect. 
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo( my_parcels );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualFdo( 'my_parcels' );
Both notations will produce the same result.

Best practices and known limitations

  • Virtual Tables are not really genuine Database Tables, they are just emulating the original source as a Database Table.
  • Virtual Tables have very poor speed performances, due to the emulation, as compared to genuine Database Tables.
  • Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, when one or more of the JOINed Tables are of the Virtual type.
  • The best use of a Virtual Table is to preform a basic data import process, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
  • 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 good idea.

Note: VirtualFDO Tables support unrestricted READ-WRITE operations, this including executing INSERT, UPDATE and DELETE statements.

VirtualGPKG

The VirtualGPKG Interface allows to directly access Spatial Tables from a GeoPackage Database exactly as if was a SpatiaLite's own native Database.
GeoPackage (aka GPKG) is an alternative Spatial Database built on the top of SQLite; you can eventually read the official specification for more details.
Syntax for creating a VirtualFDO Table: 
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpkg ( table_name );
Supported argument and its interpretation:
Note: any Text String, quoted or not, when it appears as a Virtual Table argument has the same effect. 
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpks( my_parcels );
or
SELECT CREATE VIRTUAL TABLE my_table USING VirtualGpkg( 'my_parcels' );
Both notations will produce the same result.

Best practices and known limitations

  • Virtual Tables are not really genuine Database Tables, they are just emulating the original source as a Database Table.
  • Virtual Tables have very poor speed performances, due to the emulation, as compared to genuine Database Tables.
  • Complex SQL queries JOINing several Tables can often return odd and misleading resultsets, when one or more of the JOINed Tables are of the Virtual type.
  • The best use of a Virtual Table is to preform a basic data import process, or to quickly get an overall glance at a full dataset without necessarily importing it into the Database.
  • 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 good idea.

Note: VirtualGPKG Tables support unrestricted READ-WRITE operations, this including executing INSERT, UPDATE and DELETE statements.


back to main page
Keep in touch ... join the community, subscribe the SpatiaLite Users mailing list
SpatiaLite is developed and maintained by Alessandro Furieri a.furieri@lqt.it
Fossil version 2.13 [e7bba4ff36] 2020-11-01 00:13:51