Friday 5 May 2017

Join by attribute / spatialite with SQL / left outer join in QGIS


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)



Answer



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



No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...