[QGIS-Developer] QGIS virtual layer incredibly slow

[Hugo Mercier]

Hi,

One big difference between spatialite and PostGIS is that PostGIS is
able to use spatial indexes transparently, while spatialite cannot.

Could you try with something like " ... AND lie._search_frame_ =
tbap.geometry" ?


On 02/05/2019 11:02, Andreas Neumann wrote:
> Hi,
> 
> One of my colleagues created a QGIS project where they loaded a parcel
> layer with approx. 24000 polygons (data source Postgis) and another
> layer with 3 polygons representing project perimeters of contruction
> sites. That second layer is in a Geopackage.
> 
> Now they want to select all parcels that are intersecting the project
> perimeters.
> 
> The query is straightforward:
> 
> SELECT lie.nummer_grundstueck, lie.gemeinde, tbap.bemerkungen, lie.geometry
> FROM lie_liegenschaft lie, tba_projektperimeter tbap
> WHERE ST_Intersects(lie.geometry, tbap.geometry)
> 
> Now the problem is: this query (24000 parcels, 3 project perimeters) is
> just incredibly slow: about 40 seconds. In a Postgis view this would be
> way less then a second! The worst thing: that virtual layer is updated
> with each and every zoom and pan. Is there no way to cache a virtual layer?
> 
> Can I do something to speed up our virtual layer? The parcel layer only
> chanes once a month and the perimeters only occasionally, so it would be
> best to cache the result within a QGIS session, because there are no
> changes in the geometries of both layers expected.
> 
> I understand that virtual layers somehow have to transfer all data
> formats (through ogr) into SpatiaLite or SQLite to do the query, but
> this is really way too slow for real world usage in my opinion (unless I
> do something wrong in our query).
> 
> If you have any ideas how to speed up virtual layers in QGIS it would be
> much appreciated.
> 
> Thanks,
> Andreas
> 
> 
> _______________________________________________
> QGIS-Developer mailing list
> QGIS-Developer at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>