Trying to update one table of parcels with st_intersects from another.
Results always update all table records and don't discriminate using st_intersects.
Aim of query is to update road_class value with 1 for parcels closer than 100 meters to roads and with 2 for parcels between 100 and 800 meters of roads. Distance selections like st_dwithin gives very slow queries.

Results of this queries are always to all parcels get same value and results doesn't discriminate for being overlayed or not.

What's wrong?

Both geometries uses same srid, 4326

And Postgresql versions are: -PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit
-POSTGIS="2.4.0 r15853" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER

UPDATE parcels  
FROM parcels p INNER JOIN road2 r ON   

UPDATE parcels  
FROM parcels p INNER JOIN mi.r1 r ON   

Same results with:  
FROM parcels p,r1 r   

Screenshot: parcels in brown.

r1 is diagonal polygon -road buffer of 100 m.

r2 is disgonal cross pattern -road buffer since 800 meters-.

Selected parcel should get value 2, rest value 1


1 Answer 1


Your update query logic is not as you intended. The SET operation has no (implicit or explicit) WHERE condition to select different values. You are simply setting '1' (as a constant), as the return value of the (useless) FROM ... statement.

To get different values based on that condition, you could do something like this (don´t forget to fill in the <unique_id> both in the WITH query and in the CASE statement):

  type_1 AS (
    SELECT p.<unique_id>
    FROM parcels AS p
    JOIN roads1 AS r1
      ON ST_Intersects(p.geom, r1.geom)

UPDATE parcels
SET road_class = (
      WHEN parcels.<unique_id> IN (TABLE type_1)
      THEN 1
      ELSE 2

Getting all unique_ids of rows that intersect with a road buffer of 100m in the WITH query into a 'list' (type_1 table) and check for each row in the parcels table if it's unique_id is in that list; if so, set to '1', if not set to '2'.

I wrote this out of my head and haven´t tested. There might be more efficient solutions, but this should do the job.

  • Basically this solves my issue since allow me to advance. Feb 5, 2018 at 13:31
  • Otherwise, complete logic is more complex since results can be 1,2, or null. But for this I can add a new WITH subselect to mention into CASE statement separated by , Feb 5, 2018 at 13:32
  • @FerranFerrer yes you can do CASE WHEN... THEN... WHEN... THEN... ELSE... END. add additional tmp AS (SELECT...) statements in the WITH block seperated by ','
    – geozelot
    Feb 5, 2018 at 13:39
  • @FerranFerrer oh and consider accepting an answer if it helped you as expected (I saw you answered your own question: you can accept your own answers as well). as I said, there might be other solutions, maybe someone else comes u with a faster one...but just saying ,)
    – geozelot
    Feb 5, 2018 at 13:58
  • 1
    Sorry, just add a +1 and forgot to accept it. ;-) Feb 5, 2018 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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