I am wondering if/how PyQGIS can intercept the QgsVirtualLayerProvider::getFeatures() requests of a VirtualLayer, and pull out just the spatial extent from the QgsFeatureRequest to dynamically formulate a subselect for one piece of a query, to be used as an 'IN' clause of a larger, more complex query ...
I effectively want to select polygon ids from one spatial layer, and use their ids on the fly to select features from another, much larger time series table. Simply making a view joining the two by id doesn't work: for every polygon id, there may be hundreds of rows.
So I want to explicitly pick apart the QgsFeatureRequest into spatial vs attribute components, and execute well-crafted queries appropriately.
I want to be issuing queries to PostGIS that look something like:
select b.price, b.block_id, g.the_geom from hc_models.block_ts b, join tiger_latest.tabblock g on b.block_id = g.tabblock_id
where b.month = ‘2005-01-01’ and b.block_id in
(select tabblock_id from tiger_latest.tabblock WHERE "the_geom" && st_makeenvelope(-122.45845691920965237,37.75162120370001873,-122.44739741920977849,37.75793370369994761,4269)
the subselect against tiger_latest.tabblock will yield a few dozen rows, and the subsequent query against hc_models.block_ts will hit a combined month+id index.
Can this be done in a Virtual Layer? I see a tantalizing comment in the "Index Support" description for Virtual Layers:
"For 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 to restrain the query to a particular region of space. This bounding box is passed to the underlyng provider by a QgsFeatureRequest with a "filterRect".
How to use this? Or should I just bite the bullet and start with a raw QgsPluginLayer?
