sqlite - Concatenate or group by values using spatial join in QGIS?

I have a layer (SQLlite) of city lots and would like to accomplish the following:

For each city lot,

• concatenate the list of unique lot id's which are bound within a radius of 30m from the given lot.


• or a spatial join which "groups by" both source lot id and lot ids within a 30m radius.

I am stumped, as spatial join will only aggregate data.

Also, I don't have administrator privileges on my computer so cannot install plugins.

(Using QGIS 2.6)

Answer

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)