{"action":"created","issue":{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/44","repository_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals","labels_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/44/labels{/name}","comments_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/44/comments","events_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/44/events","html_url":"https://github.com/qgis/QGIS-Enhancement-Proposals/issues/44","id":113789378,"number":44,"title":"QEP 26 Support Of Virtual Layers","user":{"login":"NathanW2","id":381660,"avatar_url":"https://avatars.githubusercontent.com/u/381660?v=3","gravatar_id":"","url":"https://api.github.com/users/NathanW2","html_url":"https://github.com/NathanW2","followers_url":"https://api.github.com/users/NathanW2/followers","following_url":"https://api.github.com/users/NathanW2/following{/other_user}","gists_url":"https://api.github.com/users/NathanW2/gists{/gist_id}","starred_url":"https://api.github.com/users/NathanW2/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/NathanW2/subscriptions","organizations_url":"https://api.github.com/users/NathanW2/orgs","repos_url":"https://api.github.com/users/NathanW2/repos","events_url":"https://api.github.com/users/NathanW2/events{/privacy}","received_events_url":"https://api.github.com/users/NathanW2/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/labels/Draft","name":"Draft","color":"207de5"},{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/labels/Type/Software","name":"Type/Software","color":"fef2c0"}],"state":"open","locked":false,"assignee":{"login":"mhugo","id":1618556,"avatar_url":"https://avatars.githubusercontent.com/u/1618556?v=3","gravatar_id":"","url":"https://api.github.com/users/mhugo","html_url":"https://github.com/mhugo","followers_url":"https://api.github.com/users/mhugo/followers","following_url":"https://api.github.com/users/mhugo/following{/other_user}","gists_url":"https://api.github.com/users/mhugo/gists{/gist_id}","starred_url":"https://api.github.com/users/mhugo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mhugo/subscriptions","organizations_url":"https://api.github.com/users/mhugo/orgs","repos_url":"https://api.github.com/users/mhugo/repos","events_url":"https://api.github.com/users/mhugo/events{/privacy}","received_events_url":"https://api.github.com/users/mhugo/received_events","type":"User","site_admin":false},"milestone":null,"comments":9,"created_at":"2015-10-28T10:41:00Z","updated_at":"2016-03-18T05:12:39Z","closed_at":null,"body":"========================================================================\r\nQGIS Enhancement 26: Support Of Virtual Layers\r\n========================================================================\r\n\r\n**Date** 2014/09/30\r\n**Author** Hugo Mercier\r\n**Contact** hugo dot mercier at oslandia dot com\r\n**Last Edited** 2015/11/10\r\n**Status**  Draft\r\n**Version** QGIS 2.14+\r\n**Sponsor** MEDDE, France - \"Ministry of sustainable development\"\r\n**Sponsor URL** http**//www.developpement-durable.gouv.fr\r\n\r\n# Summary\r\n\r\nThis enhancement proposal is about the addition of \"virtual layers\" to the QGIS core.\r\n\r\nA virtual layer is defined to be very close to what is called a \"view\" in the context of relational database\r\nmanagement systems. It aims at offering a formatted view of pre-existing data originated from one or more layers, usually without copy of data involved.\r\nA view can also bring new data computed on existing data, but without the need to store them explicitly.\r\n\r\nEven if it comes from the database world, this feature does not focus on any particular data provider. It is not restricted to data providers that connect to databases.\r\n\r\nThe list of data representation and manipulation features are growing in QGIS. Such features usually already exists in database engines and are reproduced in QGIS to circumvent the lack of these functionnalities for popular data formats (shapefiles, text-based, etc.).\r\nBut supporting advanced database features by coding them into QGIS is probably not a good idea if one wants to keep a light and robust code base.\r\n\r\nThis is why we propose to embed a database engine. Once existing layers of a QGIS project can be exposed to this database engine, new interesting functionalities can be easily developed. It could also be used to refactor existing QGIS code that deals with database-oriented features.\r\n\r\nFor now the virtual layer is mainly seen for vector layers, but extension to raster layers could be considered in the future.\r\n\r\n# Possible use cases\r\n\r\nThe proposed design is based on these different use cases for vector layers:\r\n\r\n-   create a dynamic point layer based on X,Y(,Z) coordinates\r\n-   allow to filter features based on a SQL query, even for data sources not designed for that (CSV, ODS, etc.), and including spatial operators (Transform, Intersects, etc.)\r\n-   allow to join different layers with complex clauses (including spatial joins) and with different types of joins\r\n-   give access to attributes that are computed, based on the value of other attributes (this may be somehow redundant with the possibilities offered by virtual fields in expressions)\r\n\r\nVirtual layers are designed to be easily usable by the end user as well as by some more advanced users.\r\n\r\n# Proposed Solution\r\n\r\nA first [working prototype](https://github.com/mhugo/qgis_vlayers) has been implemented as a C++ plugin. Design and implementation details in this document results from it.\r\n\r\nSQLite provides a very useful feature designed to embed its database engine in a third party application: virtual table.\r\nIt offers the ability to expose internal data as an SQLite table. Then any operations available on a regular table can also be applied to a virtual table. The implementation can then choose to apply or ignore some of the operations. \r\nA C API (as well as a through [a Python library](https://github.com/rogerbinns/apsw)) allows to create such a virtual table mechanism.\r\n\r\nIt makes a perfect candidate for the implementation of virtual layers in QGIS:\r\n\r\n*   the library is open source\r\n*   Spatialite, as an extension to SQlite is already used as a spatial format, with growing support in GIS applications\r\n*   it brings an embeddable powerful SQL engine\r\n\r\nThis proposal is inspired by the `VirtualOGR`_ driver for Spatialite that allows to open any OGR-supported format as a virtual table.\r\n\r\n\r\n.. _virtual table: http://www.sqlite.org/vtab.html\r\n.. _VirtualOGR: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualOGR\r\n.. _Python API: https://github.com/rogerbinns/apsw\r\n\r\nCore changes\r\n==========\r\n\r\nA new QgsVectorDataProvider (QgsVirtualLayerProvider) will be developed to handle virtual layers. This makes the changes very isolated and will then impact minimally the current code base.\r\n\r\nProvider syntax\r\n------------------\r\n\r\nA new virtual layer can be created out of existing layers, possibly with a query. Every parameters are passed to the QgsVectorLayer constructor \"source\" argument as a string representing an URL with\r\na set of key-value pairs.\r\n\r\nTo reference a layer, use the key `layer`. The value is a string with three fields separated by a colon : `provider:source[:name]`.\r\n\r\n* The first field is the name of the QGIS provider to use\r\n* The second field is the source string. If it contains special characters, and especially \":\", \"&\", \"?\" or \"=\" it must be escaped with the percentage sign (see QUrl.toPercentEncoding)\r\n* The third field is optional and is here to name the layer for reference in an SQL query. If no name is specified, each referenced layer will be named \"vtabN\" with an incrementing N integer.\r\n\r\nA \"live\" layer already loaded by QGIS can also be referenced by using the key `layer_ref`. The corresponding value is `layer_id[:name]`.\r\n\r\nThe `geometry` key is used to set the geometry column name, type and SRID. Its syntax is `column_name[:type:srid]`. The type field can be a string (point, linestring, etc.) or a QGis::WkbType.\r\nSRID is an EPSG code.\r\n\r\nA parameter allows to ignore any geometry column, resulting in an attribute-only layer : `nogeometry`\r\n\r\nThe `uid` key allows to specify which column must be used as an identifier for each feature. This is not mandatory. If no uid is specified, the underlying provider will autoincrement an integer for\r\neach feature.\r\n\r\nThe `query` key allows to use an SQL query to setup the layer. It should also be escaped. Layer references are not strictly necessary. If the query uses names of existing QGIS layers (or their ID),\r\nthey will be automatically referenced. Name and type of the geometry column will also be detected.\r\n\r\nSerialization\r\n-------------\r\n\r\nThe definition of virtual layers can be written on disk and reopened later on (only if the definition does not include \"live\" layers).\r\n\r\nWhen the source parameter contains a path and no other parameters, it is opened from disk.\r\n\r\nWhen the source parameter contains a path and other parameters are set, the virtual layer is created as usual and saved into the given path.\r\n\r\nSQL syntax\r\n----------\r\n\r\nThe supported SQL syntax is the SQL of SQLite. The version shipped with QGIS. No recursive CTE for now.\r\n\r\nSQLite and Spatialite functions are supported.\r\n\r\nThe internal SQL parser tries to detect types of columns (and especially the geometry column). \"CAST\" and geometry casts expressions are supported.\r\n\r\nExamples\r\n--------\r\n\r\nMinimal syntax to set an SQL query out of an existing layer (Python syntax)\r\n\r\n```python\r\nl = QgsVectorLayer( \"?query=SELECT a,b FROM tab\", \"myvlayer\", \"virtual\" )\r\n```\r\n\r\nA join between two shapefiles where the uid and the geometry column are set :\r\n```python\r\nq = QUrl.toPercentEncoding(\"SELECT a.* FROM shp1 AS a, shp2 AS b WHERE Intersects(a.geometry,b.geometry)\")\r\nl = QgsVectorLayer( \"?layer=ogr:/data/myshape.shp:shp1&layer=ogr:/data/myshape2.shp:shp2&query=%s&uid=id&geometry=geometry:2:4326\" % q, \"myjoin\", \"virtual\" )\r\n```\r\n\r\nMinimal syntax to create a virtual layer on a single layer (equivalent to SELECT * FROM)\r\n```python\r\nl = QgsVectorLayer( \"?layer=ogr:/data/myfile.shp\", \"myvlayer\", \"virtual\" )\r\n```\r\n\r\nCreation of a virtual layer and storage in a file :\r\n```python\r\nl = QgsVectorLayer( \"/myvlayer.sqlite?query=SELECT * FROM ta, tb\", \"myvlayer\", \"virtual\" )\r\n```\r\n\r\nOpening of an existing virtual layer:\r\n```python\r\nl = QgsVectorLayer( \"/myvlayer.sqlite\", \"myvlayer\", \"virtual\" )\r\n```\r\n\r\nSpatial index support\r\n-------------\r\n\r\nWhen the query uses a field from one of the referenced layer declared as a primary key, features are accessed via a QgsFeatureRequest with a \"filterFid\". It's then up to the underlying provider to support fast\r\naccess by ID.\r\n\r\nFor spatial indexes, a 'hidden' field named '_search_frame_' is created for each virtual table (i.e. each referenced layer of the virtual layer). The bounding box of the given geometry will be used\r\nto restrain the query to a particular region of space. This bounding box is passed to the underlying provider by a QgsFeatureRequest with a \"filterRect\".\r\n\r\nThe use of spatial indexes must then be explicit.\r\n\r\nFor example :\r\n```SQL\r\nSELECT * FROM pt, poly WHERE pt._search_frame_ = poly.geometry AND Intersects(pt.geometry, poly.geometry)\r\n```\r\n\r\nGUI changes\r\n==========\r\n- A new toolbox / entry in the layer menu will allow to create a new virtual layer\r\n- A new toolbox / entry in the layer menu will allow to open an existing virtual layer\r\n- A new entry in the context menu will allow to create a virtual layer out of the set of layers currently selected in the legend.\r\n- In case the user wants to filter (add a WHERE clause) to a layer with QGIS joins, (s)he will be asked if (s)he wants to first convert its layer to a virtual layer and apply the filter on it.\r\n- A new entry in DBManager will allow to run a query on any loaded layer.\r\n\r\n![](https://raw.githubusercontent.com/mhugo/qgis_vlayers/master/doc/dbmanager_screen.png)\r\n\r\nThe db manager SQL query window will be used if possible when creating a new virtual layer.\r\n\r\n## Python Bindings\r\n\r\nCreation and use of virtual layers will be available through a new QGIS data provider.\r\nThere is no particular additional Python bindings needed.\r\n\r\n## Test Coverage\r\n\r\nUni tests will be available.\r\nThe current prototype implementation includes about 30 test cases.\r\n\r\n## Performance Implications\r\n\r\nVirtual layers are first designed to add new functionalities, hard to obtain without. Speed is not the first priority.\r\n\r\nVirtual layers add an intermediate processing. Values (and geometries) will be converted from source layers to the internal SQLite format and then again to QGIS format.\r\n\r\nIf performances appear to be a problem, a \"bypassing\" strategy (passing pointers to value rather than converting values) could for instance be investigating in the future.\r\n\r\n## Further Considerations/Improvements\r\n\r\n* Virtual layers are read-only for now. For the simple case (see \"Updatable views\" [here](http://www.postgresql.org/docs/9.3/static/sql-createview.html) ), implementing feature update and addition should not be complicated. For the more general case where a virtual layer\r\ncan be a join between layers, the concept of \"updateable\" views found in databases could be ported. It means it would require the user to specify triggers on UPDATE and INSERT.\r\nCreating a virtual layer with the layer_ref key allows to directly access already loaded QGIS layers (including memory layers). But internally, QgsDataProvider is used to access features, where QgsVectorLayer could be used, allowing to expose joins (and edit buffers)\r\n* Spatial indexes must be set explicitly. The SQL parser code may be used in the future as a base for simple SQL transformations where use of geometry predicates (or bounding boxes operators like && in PostGIS) could be detected and spatial indexes added.\r\n* QgsExpression functions are not supported in an SQL query. Addition of such a feature is planned, since SQLite virtual table mechanism allows to add/overload functions.\r\n\r\n## Voting History\r\n\r\n(required)\r\n"},"comment":{"url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/comments/198211094","html_url":"https://github.com/qgis/QGIS-Enhancement-Proposals/issues/44#issuecomment-198211094","issue_url":"https://api.github.com/repos/qgis/QGIS-Enhancement-Proposals/issues/44","id":198211094,"user":{"login":"nyalldawson","id":1829991,"avatar_url":"https://avatars.githubusercontent.com/u/1829991?v=3","gravatar_id":"","url":"https://api.github.com/users/nyalldawson","html_url":"https://github.com/nyalldawson","followers_url":"https://api.github.com/users/nyalldawson/followers","following_url":"https://api.github.com/users/nyalldawson/following{/other_user}","gists_url":"https://api.github.com/users/nyalldawson/gists{/gist_id}","starred_url":"https://api.github.com/users/nyalldawson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nyalldawson/subscriptions","organizations_url":"https://api.github.com/users/nyalldawson/orgs","repos_url":"https://api.github.com/users/nyalldawson/repos","events_url":"https://api.github.com/users/nyalldawson/events{/privacy}","received_events_url":"https://api.github.com/users/nyalldawson/received_events","type":"User","site_admin":false},"created_at":"2016-03-18T05:12:38Z","updated_at":"2016-03-18T05:12:38Z","body":"@NathanW2 should this be made final now that it's implemented and merged?"}}