[Qgis-user] Slooooow spatial joins

[Karl Magnus Jönsson]

Hi!
How about just execute some SQL in an algorithm or model since you have the data in Postgis? And do the spatial join directly in the database. Like (not tested):
UPDATE polygonlayer p
SET p.field = pt.field
FROM pointlayer pt
WHERE st_within(pt.geom,p.geom);

Karl-Magnus Jönsson

Från: Qgis-user <qgis-user-bounces at lists.osgeo.org> För Ryan Peel
Skickat: den 18 maj 2021 12:24
Till: Nyall Dawson <nyall.dawson at gmail.com>
Kopia: qgis-user <qgis-user at lists.osgeo.org>
Ämne: Re: [Qgis-user] Slooooow spatial joins

I can appreciate that but it forces to create another layer/file I think.  I really don't want to have to re-import my entire table back into postgis just to get this one column.

On Mon, May 17, 2021, 10:29 PM Nyall Dawson <nyall.dawson at gmail.com<mailto:nyall.dawson at gmail.com>> wrote:
On Tue, 18 May 2021 at 11:59, Ryan Peel <ryanpeel at gmail.com<mailto:ryanpeel at gmail.com>> wrote:
>
> I have tried using MMQGIS plugin, refFunctions geomwith function and the aggregate function.  I am using 3.18 QGIS
>
> Despite having spatial indexes on both tables, when I try to use any of these operations to assign the value of a field in the polygon layer to one in the point layer, it goes very slow.  Like, ridiculously slow and of no value.  And, I only have 600 records in the points layer and just a few thousand records in the polygon layer.
>
> What can I do to speed this up?

The absolute fastest way to join is to use the Processing tool: Join
Attributes by Location -- no plugins necessary :D

Nyall
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20210518/f1dc6eb0/attachment.html>