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.
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