4

I am looking for a way to create a list of polygons that each polygon touches. I am a complete novice at QGIS and I have no idea where to even start. I am using a map of voting precincts in Utah that looks like this:

The precincts

I want to generate a list that shows the precinct name (the attribute is called PrecinctID). Preferably, the output list would be in the form of a CSV file. How would I accomplish this?

Edit: When following the instructions provided by PieterB, I get an empty error window. The Python error log reads:

Traceback (most recent call last):
              File "/Applications/QGIS3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/gpkg/data_model.py", line 63, in run
              self.model = GPKGSqlResultModel(self.db, self.sql, None)
              File "/Applications/QGIS3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/data_model.py", line 195, in __init__
              c = self.db._execute(None, sql)
              File "/Applications/QGIS3.app/Contents/MacOS/../Resources/python/plugins/db_manager/db_plugins/gpkg/connector.py", line 147, in _execute
              self.connection = spatialite_connect(str(self.dbname))
              File "/Applications/QGIS3.app/Contents/MacOS/../Resources/python/qgis/utils.py", line 595, in spatialite_connect
              con.enable_load_extension(True)
             AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension'
9

You can do this in QGIS with SQL.

Go to Database > DB-manager > Virtual Layers > Project Layers

Open the SQL-window and add following code. Change 'layername' twice to the appropriate layername:

select a."PrecinctID",  group_concat(b."PrecinctID") as neighbours
from layername a, layername b
where touches(a.geometry, b.geometry)
group by a."PrecinctID"
  • Check 'load as a new layer'
  • Uncheck 'Geometry column'
  • Give a layer name
  • click load

Now you get a table which you can save (right-click > save as) as a .csv

It will take a while before the results are computed. Depending on how your features are stored, it can go faster when you put them in a database (eg. geopackage) and set an index on the geometry and PrecinctID.

Importing your data into geopackage:

  • right click your layer and choose Export > Save features as
  • choose Geopackage
  • give your file and layer a name enter image description here

As everything goes fine, the layer will be loaded in QGIS.

After that, open DB-manager and connect to your geopackage (right clik on 'geopackage' > New connection)

Now you have to make some indexes:

  • a spatial index like explained here: https://gis.stackexchange.com/a/265574/7849
  • an index on PrecinctID: in DB-manager select you layer and go to Table > Edit Table.
    enter image description here

  • Open the tab 'Index' and click on 'Add index', select PrecinctID in the dropdownlist. enter image description here

In geopackage the geometry is called 'geom'. So you have to adapt the code a little bit:

select a."PrecinctID",  group_concat(b."PrecinctID") as neighbours
from layername a, layername b
where touches(a.geom, b.geom)
group by a."PrecinctID"

enter image description here

  • 2
    really a good one – Taras Apr 9 at 11:15
  • When I use this, I get the following error: Query preparation error on SELECT "group_concat(b."PrecinctID")" FROM _tview LIMIT 1: near "")"": syntax error – Ben A. Apr 9 at 14:02
  • @BenA. I forgot an alias. I edited my answer. – PieterB Apr 9 at 14:57
  • 1
    Let us continue this discussion in chat. – Ben A. Apr 9 at 17:19
  • 2
    Comments are not for extended discussion; this conversation has been moved to chat. – Mapperz Apr 10 at 2:50

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.