5

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)

  • 1
    You don't need administrator privileges to install plugins. – Alexandre Neto Feb 5 '16 at 21:33
8

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)

|improve this answer|||||
  • Could you preface the answer with the path through which the SQL should be entered in the QGIS interface? I imported the two point layers as tables into a spatialite database through the database manager and executed the SQL . Is this the best way to proceed ? I have approximately 65,000 lots in the dataset. – Dealec Feb 5 '16 at 16:19
  • @Dealec Yes I guess the DB manager of QGIS is a correct way to proceed (i can't tell if its the best thus; but in QGIS I would probably have use that too). If your query take too long you can try to tweak a little bit your DB with some PRAGMA statments like PRAGMA cache_size=2048000 (its in number of pages and the default value is generally 2000). – mgc Feb 5 '16 at 17:47
  • How do I ensure that the database manager is using the spatial index it created when I imported the layer? – Dealec Feb 5 '16 at 19:11
  • @Dealec in Spatialite you have to specify explicitly in the WHERE part to look only in the search_frame of your feature so the end of the query will look like ...WHERE b.id = 1 AND b.id != a.id AND a.rowid IN (SELECT rowid FROM SpatialIndex WHERE f_table_name = 'city_lots' and search_frame = st_buffer(b.GEOM, 30)) AND ST_Distance(a.GEOM, b.GEOM) < 30; (In this order it only computes ST_Distance() for features matching the spatial index condition) – mgc Feb 5 '16 at 19:24
  • 1
    @Dealec I modified my answer to respond to your second request in SQL (I tested it with 65000 random points and fetched the result in a few seconds thanks to spatial index) – mgc Feb 5 '16 at 20:29
2

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.

|improve this answer|||||

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.