[Qgis-user] Running intersection with PostGIS overlay layer without local recreation of index?

[Johannes Kröger (WhereGroup)]

Chris and Tobias, thanks!

I did not test much with both layers in PostGIS since moving data into 
the database temporarily was not possible solution. A quick local tests 
shows "Creating spatial index" even then and yes both layers have 
spatial indexes in the database. :O

"Manually" doing a server-side query instead of using QGIS' processing 
tools was not an option. If it is, of course that would be the most 
efficient approach.

Cheers, Hannes

On 4/17/25 07:42, Tobias Schula via QGIS-User wrote:
> On Thursday, 17 April 2025, 07:36:51 CEST Johannes Kröger (WhereGroup) via
> QGIS-User wrote:
>> 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)
> Is your PostGIS read-only? Otherwise you could load your local layer into the
> database and have your intersection done by the database. Should be way faster
> and you won't download the whole coverage layer. Some time ago there was an
> effort to map QGIS tools to their PostGIS equivalent and have the operations
> run in the database directly instead of locally. But I think it stalled and I
> didn't follow it anymore.
>
> Best Regards
>
> _______________________________________________
> QGIS-User mailing list
> QGIS-User at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
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.kroeger at wheregroup.com
www.wheregroup.com

Geschäftsführer:
Olaf Knopp, Peter Stamm
Amtsgericht Bonn, HRB 9885
-------------------------------