What is a Virtual Interface
SQLite has the nice capability to support Virtual Tables, that is a powerfull mechanism allowing to greatly extend the basic capabilities of the SQL language.
A Virtual Table is not really a table (it actually is a piece of executable code), but designed in such a way that it supports all the usual SQL operations: CREATE TABLE, DROP TABLE, SELECT, and is some cases INSERT, UPDATE and DELETE (Virtual Tables usually can only support READ-ONLY operations; supporting unrestricted READ-WRITE operations is rather exceptional).
Virtual Tables can be seen as Interfaces, because they are intended to support typical SQL operations on something external to the ordinary Database logic.
There are two big families of Virtual Interfaces:
- Virtual Interfaces allowing to directly access data stored outside the Database, or stored within the Database but adopting some non-native encoding.
- Virtual Interfaces accessing external datasources: VirtualShape, VirtualDBF, VirtualText, VirtualXL, VirtualPostgres
- Virtual Interfaces converting to/from non-native Geometries: VirtualFDO, VirtualGPKG
- Virtual Interfaces allowing to query the results of some more or less complex Algorithm.
- VirtualRouting, VirtualKNN and so on
Virtual Interface Topics
| Topic | Wiki page | Initial introduction |
|---|
| Direct access to external ShapeFiles: VirtualShape | read | since version 1.0 |
| Direct access to external DBF Files: VirtualDBF | read | since version 2.4.0 |
| Direct access to external Text Files (*.txt, *.csv): VirtualText | read | since version 2.2.0 |
| Direct access to external MS Excel spreasheets (*.xls): VirtualXL | read | since version 2.4.0 |
| Direct access to external PostgreSQL/PostGIS DBMS: VirtualPostgres | read | N.A. independent package (2011) |
| Automatic conversion to/from FDO Geometries: VirtualFDO | read | since version 2.3.0 |
| Automatic conversion to/from GeoPackage Geometries: VirtualGPKG | read | since version 4.0.0 |
| Sql based Routing module: VirtualRouting | read | since version 4.5.0 |
| Searching Nearest Neighbors: VirtualKNN | read | since version 4.4.0 |
| Separate complex geometries into distinct elements: VirtualElementary | read | since version 4.2.1 |
| Module that enables SpatiaLite to directly perform XPath queries : VirtualXPath | read | since version 4.1.0 |
| Access different datasources using Gdal/Ogr: VirtualOGR | read | N.A. independent package (2012) |
| Obsolete former Routing module: VirtualNetwork used with spatialite_network, replaced with VirtualRouting | read | since version 4.2.1 |
|