0

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  
SET  
road_class=2  
FROM parcels p INNER JOIN road2 r ON   
st_intersects(p.geom,r.geom);  

UPDATE parcels  
SET  
road_class=1  
FROM parcels p INNER JOIN mi.r1 r ON   
st_intersects(p.geom,r.geom);  

Same results with:  
FROM parcels p,r1 r   
WHERE  
st_intersects(p.geom,r.geom); 

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

layers

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):

WITH 
  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 = (
    CASE
      WHEN parcels.<unique_id> IN (TABLE type_1)
      THEN 1
      ELSE 2
    END
)

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. – Ferran Ferrer Feb 5 '18 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 , – Ferran Ferrer Feb 5 '18 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 ',' – ThingumaBob Feb 5 '18 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 ,) – ThingumaBob Feb 5 '18 at 13:58
  • 1
    Sorry, just add a +1 and forgot to accept it. ;-) – Ferran Ferrer Feb 5 '18 at 14:27

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.