1

I have two layers, one is a grid of circles and the other is all Census Block Groups (CBG) in the state of Florida. I created a Virtual Layer out of this code

SELECT CirclesFlorida.name, group_concat(cbgFl.FIPS)
FROM CirclesFlorida
LEFT JOIN cbgFl ON ST_Intersects(circlesFlorida.geometry, cbgFl.geometry)
GROUP BY circlesflorida.name

The purpose is for each circle to have a data column which lists every CBG that falls in that circle.

Is there a faster way to do this? Maybe DB Manager? Currently the query takes 20 minutes to run and another 5 minutes to just open the attribute table of the Virtual Layer.

Taras
  • 32,823
  • 4
  • 66
  • 137
Ben Hendel
  • 449
  • 2
  • 5
  • the db manager sql interface will use a sqlite database on the back-end to do the processing... I would try it out. I don't know if the sql layer is doing the same. – DPSSpatial_BoycottingGISSE Sep 28 '20 at 22:16
  • As an alternative try to construct a model in the Graphical Modeler, it can also work with SQL queries, see this https://gis.stackexchange.com/a/360796/99589. – Taras Sep 29 '20 at 05:22
  • Otherwise you may try running the Virtual Layer via Python console, check this https://gis.stackexchange.com/questions/227203/creating-new-virtual-layer-programmatically-in-qgis – Taras Sep 29 '20 at 05:37
  • @DPSSpatial the sqlite doesn't recognize the ".geometry" attribute as a column. Is it written in a different kind of SQL? How would I write it? – Ben Hendel Sep 29 '20 at 08:05
  • https://docs.qgis.org/3.22/en/docs/user_manual/managing_data_source/create_layers.html#use-of-indexes – bugmenot123 Sep 13 '22 at 08:40
  • The reason to use a virtual layer is so that the layer will update as the underlying data changes. If you don't care about that, you can use the Execute SQL tool (there are actually two - native and GDAL) from the Processing Toolbox. – Tom Brennan Oct 02 '23 at 00:22
  • 1
    Try to use _searchframe_ in your query! This helped me: https://lists.osgeo.org/pipermail/qgis-developer/2021-May/063582.html – Kay Oct 03 '23 at 10:55

0 Answers0