# PostGIS - Count adjacent polygons connected by line

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

• your question seems to have lost something Commented Mar 9, 2021 at 14:22

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
``````
• Thanks @JGH. it seems to work but it's taking a long time. Commented Mar 9, 2021 at 15:26
• @DanielePiccolo the `group by geom` is likely expensive. You can try keeping only `a.id` and the `count`, and eventually to join back this result to the source table to get access to other fields
– JGH
Commented Mar 9, 2021 at 15:29
• @DanielePiccolo Also make sure you have spatial index everywhere (and then that you have analyzed the tables)
– JGH
Commented Mar 9, 2021 at 15:31
• Maybe there is something wrong, the column count has numbers too high (es, 300). In your code I don't understand where it considers adjacent polygons. Commented Mar 9, 2021 at 15:52
• it doesn't check for polygon adjacency, but for line connecting them (this assumes a single line doesn't cross multiple municipalities). Check how the lines are crossing the borders... are they crossing? or do the segments stop/start at the border?
– JGH
Commented Mar 9, 2021 at 15:58

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