1

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 municipal limits and ways

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

2 Answers 2

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
8
  • Thanks @JGH. it seems to work but it's taking a long time. Commented Mar 9, 2021 at 15:26
  • 1
    @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
1

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;
2
  • Thanks @dr.jts. the first script works very well and fast, the second return a number of adjacent municipa too hig (es. 385). The layer way has segments stoping/starting at the border Commented Mar 10, 2021 at 8:30
  • @ dr.jts. I think the problem is that the intersection condition between way and municipal must be for the first municipal and the adjacent. The same way must intersects the first municipal and the adjacent. Commented Mar 10, 2021 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.