Hi all,
Following a first discussion on sqlite-dev that was probably not the
right place to post, I've been invited to repost here for a broader
audience :)
I am a developer on QGIS and I am investigating the possible use of
SQLite / Spatialite to extend QGIS relational-oriented features.
For now, we have what we call "data providers" that allow to open / read
/ modify geographic data from different data sources, more or less
structured data from regular files or from local or remote databases.
Some database concepts are little by little put into QGIS, but some of
us feel this is not exactly the right place for that.
So I am considering the use of the virtual table mechanism of SQLite to
embed a powerful SQL engine in QGIS.
The idea would be to expose each type of GIS layer as a virtual table in
SQLite. Then the user could use them for advanced queries such as
(spatial) joins.
GIS layers can already be RDBMS, like Postgresql/Postgis, MSSQL, Oracle
spatial, etc.
There have been discussions on QGIS ML about that, and we are concerned
about the performances of such an approach [1] [2] [3]
The main concern is about how to "translate" a main query that must in
the end be split into queries to different databases. And especially
regarding the use of native indices of such databases.
>From previous answers on sqlite-dev, using dedicated fields
estimatedCost and estimatedRows in xBestIndex could be enough to orient
the planner if native indices on regular columns are present (and if the
virtual table knows that)
For geometry column(s) that might be more complicated if I am correct.
For a query such as:
SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom)
where VA are virtual tables of say a PostGIS table and a Shapefile
respectively,
there is no way to inform xBestIndex to use the native spatial indices
of VA or VB during the query.
Native spatial indices must be locally copied and explicitly used with
spatialite like :
SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) AND
VA.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'VA'
AND search_frame = VB.geom
)
Avoiding such explicit syntax and index duplication would require
something like the implementation of GIST [4] in Sqlite, and having more
generic constraints passed to xBestIndex, I guess. Not very easy.
Are there other possibilies that I am missing ?
The other concern is about accessing the parsed SQL query before
executing it. This could be used to process the query in order to :
- collect information on it : table names, column names and types,
especially detecting geometry columns
- bypass SQLite execution if the query is actually to be made on tables
of the same database
- possibly do SQL->SQL transformations
Apparently accessing this parse tree is often asked for here, and some
said [5] it could be nice to have for sqlite 4. Is it still something
considered for next versions ?
[1]
https://github.com/mhugo/QGIS-Enhancement-Proposals/blob/master/QEP-3-virtual-layers.rst
[2]
http://osgeo-org.1560.x6.nabble.com/1-many-relation-enhancements-td5168023.html#a5168822
[3]
http://osgeo-org.1560.x6.nabble.com/QEP-RFC-sqlite-virtual-tables-tt5168850.html
[4] http://gist.cs.berkeley.edu/
[5] https://www.mail-archive.com/sqlite-users%40sqlite.org/msg43159.html
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
