I have a line layer representing transport routes.

I would like to add a virtual layer listing every city crossed by each line. I would only need the id of each city to be listed (column Code_INSEE).

The result should be

Line 1 | 12001, 12003, 12122|
Line 2 | 23001, 23002, 23004, 23120,23012|

enter image description here

This question is linked to the solution proposed in this topic: Automatic calculation field based on line modification within QGIS


One sample virtual layer syntax (using Natural Earth admin dataset):

SELECT x.name AS LineName, Group_Concat(x.admin, ',') AS  CountryNames
  FROM Line 
  CROSS JOIN Countries ON
  st_intersects(Line.geometry, Countries.geometry)
) as x
GROUP BY x.name

enter image description here

For your case it will probably be:

SELECT x.name AS LineName, Group_Concat(x.Code_INSEE, ',') AS  CityNames
  FROM lines 
  CROSS JOIN cities ON
  st_intersects(lines.geometry, cities.geometry)
) as x
GROUP BY x.name

(Assuming your Line layer name is lines and the City polygon layer is cities, and the Line number is stored in name field).

  • Perfect ! This is exactly what i needed ! Kudos to you – Arthur Jan 21 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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