I have a list of IDs (polygons) and in a table (i.e. table zones) I've all the possible permutations of these IDs. In another table (i.e. zonesid) I've their corresponding geometries [geometry(MultiPolygon,4326)].
table zones:
index | zone1 | zone2
-------+--------+--------
0 | 100 | 100
1 | 100 | 101
2 | 100 | 102
3 | 101 | 100
4 | 101 | 101
5 | 101 | 102
6 | 102 | 100
7 | 102 | 101
8 | 102 | 102
table zonesid:
index | zone_id | geom
-------+--------+--------
0 | 100 | geom100
1 | 101 | geom101
2 | 102 | geom102
Now I'd need to find which areas are adjacent and write a 1 next to the pair.
I've read the question Finding neighbouring polygons - postgis query and I think I need something similar, even if in this case I need to make it indicating the exact pair.
In the above example let's say that just 100 and 102 are adjacent. It should be:
table zones:
index | zone1 | zone2 | adiacent
-------+--------+---------+--------
0 | 100 | 100 | 0
1 | 100 | 101 | 0
2 | 100 | 102 | 1
3 | 101 | 100 | 0
4 | 101 | 101 | 0
5 | 101 | 102 | 0
6 | 102 | 100 | 1
7 | 102 | 101 | 0
8 | 102 | 102 | 0
I've started with:
ALTER TABLE zones
ADD COLUMN adjacent bigint;
UPDATE zones set adjacent=1, time=2
FROM (
SELECT (*)
FROM zonesid as a,
zonesid as b,
zones as c,
zones as d
WHERE ST_Touches(a.geom, b.geom)
AND c.zone1 != d.zone2
) as subquery
WHERE c.zone1 = subquery.zoneid
But... I'm struggling in getting how to refer correctly to the table zonesid to compare the pairs and then get which they are.
A colleague of mine helped me (Thanks again!). I post the answer that works for me, in case it could be useful to someone else:
with adjacent_pairs as (
select
a.zone_id zone_id_1,
q.zone_id zone_id_2
from zonesid a
cross join lateral (
select zone_id
from zonesid b
where
st_dwithin(a.geom, b.geom, 0)
and a.zone_id != b.zone_id
) q
)
update zones a
set adjacent = 1
from adjacent_pairs b
where
a.zone_a = b.zone_id_1
and a.zone_b = b.zone_id_2;
update zones
set adjacent = 0
where adjacent is null;