Concatenate or group by values using spatial join in QGIS?

Concerning your first request (concatenate the list of unique lot id's which are bound within a radius of 30m from the given lot), I guess you can do that with a SQL query in QGIS :

SELECT b.id as ID_ref, group_concat(a.id) AS ID_within_30m
    FROM city_lots a, city_lots b
    WHERE b.id = 1 AND b.id != a.id
    AND ST_Distance(a.GEOM, b.GEOM) < 30;

Which should output something like:

ID_ref | ID_within_30m | -------|---------------| 1 | 5,6,8 |

(There is probably more efficient ways to do this if you are handling a large dataset)

Concerning your second request, if it is about doing the same analysis for each lot, I guess you can do that with a JOIN (and a GROUP BY) in SQL (and using a spatial index):

CREATE TABLE t (startrange INTEGER not null, endrange INTEGER not null);
INSERT INTO t VALUES (1, (SELECT count(rowid) FROM city_lots));
SELECT b.id as ID_ref, group_concat(a.id) AS ID_within_30m
    FROM city_lots a,
         t JOIN city_lots b on (b.rowid >= t.startrange and b.rowid <= t.endrange)
    WHERE a.rowid IN (
      SELECT rowid FROM SpatialIndex
          WHERE f_table_name = 'city_lots'
          AND search_frame = ST_Buffer(b.GEOM, 30))
    -- AND b.id != a.id 
    AND ST_Distance(b.GEOM, a.GEOM) < 30
    GROUP BY b.ID;

Which should output the entire result :
ID_ref | ID_within_30m | -------|-----------------| 1 | 1,5,6,8 | 2 | 2,7,9 | 3 | 3 | .....

(the clause b.id != a.id is commented in order to get a row in ID_ref even if there isn't any other lots in the 30m radius)


Update: As a newcomer to QGIS, I was looking for a solution to this question through pre-defined functions in the QGIS menu, which proved to be impossible. Here's an preface to the accepted answer for others out there who have never used the database manager (DB Manager):

  • Create a Spatialite database with the QGIS browser
  • Import the layer you wish to process (in my case "city_lots") into newly created database. I was initially not able to import the layer due to the presence of multiple geometry types. (see this question). The solution I eventually found was to re-save as a point layer (on lot centroid) before proceeding to import.
  • Open SQL window in database manager and paste SQL code provided by @mgc. At first, the code was processing so slowly (more than an hour) that I had to restart QGIS multiple times to interrupt the execution. Only when I explicitly specified using a spatial index, as specified by @mgc, did the code execute, and it required a few seconds only.
  • The resulting table will appear in the database tree on left of DBManager window. Right-click on the new table and click "Add to Canvas" to add the layer to your map. You can then right-click on the layer and save as CSV if you wish to further process the table using other software.

In short, the accepted answer worked for me though it required several hours (days) of experimenting with the DB manager and fiddling with the code provided by @mgc.

Tags:

Sqlite

Qgis

Recent Posts