3

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

enter image description here

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

1 Answer 1

4

One sample "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer... (using Natural Earth admin dataset):

SELECT x.name AS LineName,
       Group_Concat(x.admin, ',') AS CountryNames
FROM (
  SELECT *
  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 (
  SELECT *
  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).

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

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.