8

I have a layer with polygons (let's call it "woods") that is intersected by "n" line-objects ("roads"). Every road has an unique attribute roadid.

I need to add ALL the roadid's (not only the first QGis finds) to the woods-polygons-layer they intersect for latter use. Preferably all the roadid's should be in one new attribute column of the "woods", divided by e.g. ",".

If there was only one road in every wood i could use the "join attribute by position" tool to get roadid's on the polygons. The wanted attribute is an unique string so it doesn't help to sum/average/min/max the fields, and there is no option to bind them together as strings divided by ",".

Edit: There is no solution without the help of plugins or external programms by now (see comment by Matthias Kuhn below).

(QGIS 2.10.1-Pisa)

|improve this question
  • Please clarify what do you need at the end of the process. A field containing multiple separated values or only one value? And if a single value is needed what value must be kept when you have multiple match? (eg: keep longest road, the more overlapping one, smallest id, etc...) – MarHoff Feb 2 '16 at 12:41
  • I need all the values, question updated - thanks for the hint. – Papierwolf Feb 2 '16 at 13:23
  • 1
    This is tipically the kind of task i would perform inside PostGIS using an LEFT OUTER JOIN... if by chance you use PostGis I can provide a quick answer but using only QGIS might need much more steps. – MarHoff Feb 2 '16 at 13:38
  • 1
    This is something that is not (yet) possible with QGIS. In general this is discussed under the term "aggregate functions" and I am confident that we will see this in a not-too-far-in-the-future release. Meanwhile I would recommend to use a database (e.g. postgres) solution or there may be a tool in processing which I'm not aware of right now. – Matthias Kuhn Feb 2 '16 at 14:29
  • 2
    You may intersect the polygon layer with the polyline layer, This way you get polygon and polyline ids which intersect. You may write a Python script/plugin to collect polyline ids to polygon ids, but it is not the best solution from a relational point of view. – Zoltan Feb 2 '16 at 15:01
6

Use Spatialite Database!

It is a lightweight file based spatial DB supported out of the box by QGIS.

  1. First set-up a spatialite DB following theses instructions

  2. Push your two tables to this spatialite DB using QGIS DB manager

  3. Assuming that your tables are called "polygon" and "line" run the following SQL command in DB manager query interface.

SELECT polygon.id,
polygon.lib, -- Place here any field releveant for you (they must also be in grouping clauses, see below)
group_concat(line.id,',') as list_id_line -- this function concatenate the id of every line that touch you polygon
FROM polygon LEFT OUTER JOIN line
ON Intersects(polygon.geom,line.geom) -- Spatial Dabatabase Rule !
GROUP BY polygon.id, polygon.lib -- theses are the grouping clauses
  1. Enjoy!

More explanations and fun by reading about SQLite aggregate functions here and spatialite functions here

|improve this answer
  • Thanks MarHoff, this looks promising! I will have a closer look at your solution tomorrow and give you your well-deserved "accept answer" and upvote. – Papierwolf Feb 4 '16 at 14:48
  • Indeed check if it work before ;) I was actually pretty amazed by Spatialite potential as server-less option. For I work on PostGis you gave me a nice occasion to try it! – MarHoff Feb 4 '16 at 16:10
  • Your solution works just fine. Thanks again for providing the sources and the script. They really help a beginner to understand how this works. I'll try to update the question and tags again to help others find this question and your answer. – Papierwolf Feb 8 '16 at 14:08
  • You are welcome! And I love the new title. IMHO it might be fair also to replace the tag "expression" with "spatialite". Bye ;) – MarHoff Feb 8 '16 at 14:33
  • 1
    Learn from my mistakes: Keep in mind, that you have to check "Create Spatial Index" when importing data to SpatiaLite (or do so later by right-clicking the tables). Your SQL-Queries including geometrical operations will take ages if you forget to do so. – Papierwolf Feb 9 '16 at 14:53

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.