Workaround, that works well in my specific data and use case:
My input layer only covers small and local parts of the full coverage of
the big PostGIS layer. Because of this I could switch the overlay input
for the intersection from the PostGIS layer to a query layer that
filters the PostGIS layer against the convex hull of the input layer
with ST_Intersects. Now only a small fraction of the features will be
fetched and the complexity depends mostly on the spatial distribution of
the input data, not the overlay layer.
Works very well so far and vastly faster.
I still feel like I am overlooking something very basic though :o)
Cheers, Hannes
On 4/9/25 13:26, Johannes Kröger (WhereGroup) via QGIS-User wrote:
Howdy!
I have a local layer with a few polygon features and a remote PostGIS
layer with millions of polygon features.
When I run native:intersection with the small local layer as input and
the big remote layer as overlay, QGIS will fetch all features from the
big remote layer and create a local spatial index. This uses lots of
bandwidth and takes a long time.
I would have expected QGIS to handle PostGIS layers in a way that
their existing indexes are utilised.
Searching for the processing log message "Creating spatial index", it
seems like
https://github.com/qgis/QGIS/blob/d238f1132bbbf8c479356b9267cff7ee137e5be9/src/analysis/processing/qgsoverlayutils.cpp#L233
is responsible for that. If I read the code correctly, a
QgsSpatialIndex will *always* be created for the overlay layer.
Is there a way to do intersections where existing (PostGIS) indexes
are used?
Cheers, Hannes
PS: A workaround solution could be using some custom Python code
around qgis:postgisexecuteandloadsql I guess.
--
Johannes Kröger / GIS-Entwickler/-Berater
****************************************
WhereGroup Shorts 2025 - am 15. Mai
Im Fokus: PostgreSQL & PostGIS
Online als Zoom-Meeting
https://wheregroup-shorts.de
****************************************
WhereGroup GmbH
c/o KK03 GmbH
Lange Reihe 29
20099 Hamburg
Germany
Tel: +49 (0)228 / 90 90 38 - 36
Fax: +49 (0)228 / 90 90 38 - 11
johannes.kroe...@wheregroup.com
www.wheregroup.com
Geschäftsführer:
Olaf Knopp, Peter Stamm
Amtsgericht Bonn, HRB 9885
-------------------------------
_______________________________________________
QGIS-User mailing list
QGIS-User@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
