PostGIS - Count adjacent polygons connected by line

Question

I have a layer with municipal limits. With the following code I calculated the number of adjacent municipals:

SELECT distinct a.id, count (b.*), a.geom, a.codistat, a.name  
FROM municipal a 
JOIN municipal b 
ON ST_Intersects(b.geom, a.geom)  
where a.id != b.id 
GROUP BY a.id, a.geom, a.codistat, a.name

Now I am looking for the solution to calculate the number of adjacent municipalities that are connected by a road (layer way with only id attribute) to understand the value of the connections between municipalities. In some cases this value will be lower than the one I previously calculated. I have some problems figuring out how to integrate the code. Any help is welcome

Answer 1

You would join the 1st polygon to the line, then the (same) line to the 2nd polygon

SELECT distinct a.id, count (b.*), a.geom, a.codistat, a.name  
FROM municipal a 
 JOIN way w
   ON ST_Intersects(a.geom, w.geom)  
 JOIN municipal b 
  ON ST_Intersects(w.geom, b.geom)  
where a.id != b.id 
GROUP BY a.id, a.geom, a.codistat, a.name

Answer 2

Including non-aggregated columns in the GROUP BY just to return them in the SELECT list is a SQL anti-pattern. One way to avoid this is to use JOIN LATERAL. For the original query this is:

SELECT a.id, a.geom, a.codistat, a.name, num_adj
FROM municipal a 
JOIN LATERAL (SELECT COUNT(1) num_adj 
              FROM municipal b
              WHERE ST_Intersects(a.geom, b.geom)
              ) t ON true;

For the query involving ways I think this should work (not tested):

SELECT a.id, a.geom, a.codistat, a.name, num_adj
FROM municipal a 
JOIN LATERAL (SELECT COUNT(1) num_adj 
              FROM municipal b
              JOIN way w
              ON ST_Intersects(b.geom, w.geom)
              WHERE ST_Intersects(a.geom, b.geom)
                 AND ST_Intersects(a.geom, w.geom)
              ) t ON true;