0

I have 2 spatialite polygon layers which represent land parcels and municipalities. Each parcel is within or intersects 0, 1 or 2 municipalities. I am trying to update a field within the parcel layer with the name of the municipality when the centroid of parcel is within the municipality.

First I tried doing this within QGIS using the "Join attributes by location". It's very slow. I failed to make a proper test, and my attempt was for 1M parcels against 300 municipalities. It's about 2/3 done after 24 hrs and I'm afraid to stop it.

In the meantime I've been attempting it with a much smaller sample using SQL in the Spatialite-Gui application.

The following select query works:

Select qtrs.label, rm.rmname
from qtrs join rm on
within(centroid(qtrs.Geometry), rm.Geometry)

however I'm unable to convert it to a working update query. The following executes but puts all of the parcels into the same municipality

update qtrs
    set rmname=
(select rm.rmname
    from qtrs join rm where
    within(centroid(qtrs.Geometry), rm.Geometry));

Everything else I've tried fails with a syntax error

3
  • 1
    SQLite doesn't work like that, your inner select needs to return a single value for each row in the table to be updated. Try something like this: update qtrs q1 set rmname= (select rm.rmname from qtrs q2 join rm on within(centroid(q2.Geometry), rm.Geometry) where q1.id=q2.id); – M Bain Mar 27 '20 at 23:57
  • @MBain That works, with one variation, update qtrs q1 set isn't accepted, it has to remain just update qtrs set with the corresponding changes to the rest.. If you write it as an answer I will accept it – marcp Mar 28 '20 at 16:32
  • Glad you worked it out, a good joint effort. – M Bain Mar 28 '20 at 21:29
1

SQLite doesn't work like that, your inner select needs to return a single value for each row in the table to be updated. Try something like this:

    UPDATE qtrs  
      SET rmname=  
      (SELECT rm.rmname
        FROM qtrs q2 JOIN rm ON
        WITHIN(centroid(q2.Geometry), rm.Geometry)  
        WHERE qtrs.id=q2.id);

Thanks to @marcp for getting the syntax just right.


A note about spatial indexes from the OP. Spatialite, inexplicably, doesn't use them by default. For a query such as this they are crucial. I played around a bit and found a 30% improvement in speed by using the following. I was surprised, though, that there wasn't more of a gain.

update qtrs 
set rmname= (select rm.rmname from qtrs q2 
    join rm on within(centroid(q2.Geometry), rm.Geometry) 
    where  rm.rowid in (
        select ROWID from SpatialIndex
        where f_table_name='rm' and search_frame=qtrs.geometry)
    and qtrs.id = q2.id); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.