Generate list of neighboring polygons QGIS

Question

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:

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'

Like this? > https://www.qgistutorials.com/en/docs/find_neighbor_polygons.html — Mapperz, Apr 09 '19 at 03:22

PieterB · Accepted Answer · 2019-04-10T14:43:43.530

11

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

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.
Open the tab 'Index' and click on 'Add index', select PrecinctID in the dropdownlist.

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"

edited Apr 10 '19 at 14:43

answered Apr 09 '19 at 07:57

PieterB

5,287
22
37

2

really a good one – Taras Apr 09 '19 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 09 '19 at 14:02
@BenA. I forgot an alias. I edited my answer. – PieterB Apr 09 '19 at 14:57
@PieterB How long should it take for the code to execute? It has been going for a few minutes now. Is it normal for it to take a while? There are about 2000 voting precincts. – Ben A. Apr 09 '19 at 15:45
@PieterB What do you mean? It is in a .qgz file if that helps. – Ben A. Apr 09 '19 at 15:51
.qgz is just the project-file. Your data is stored outside that. Just look in the properties of the layer by right click > properties > information. There you will see some details – PieterB Apr 09 '19 at 15:54
I just did a test with the same features in geopackage and it just took 2 sec. – PieterB Apr 09 '19 at 15:59
It says ESRI shapefile. This is where I got the file if this helps. – Ben A. Apr 09 '19 at 16:00
@PieterB QGIS 3. I can't find the "add index" button. Where is that? – Ben A. Apr 09 '19 at 16:35
in the left lower corner of the tab 'indexes' – PieterB Apr 09 '19 at 16:36
When you say table, are you referring to the attribute table? I can't find the index tab there. – Ben A. Apr 09 '19 at 16:38
no, it is a menu-item in DB-manager – PieterB Apr 09 '19 at 16:41
@PieterB After following the instructions, when I click execute, I get an error window without an error message in it. – Ben A. Apr 09 '19 at 16:49
did you change 'layername' to the appropriate name? – PieterB Apr 09 '19 at 16:51
@PieterB Yes, I changed it. – Ben A. Apr 09 '19 at 16:53
Close DB-manager. Open it again and navigate to your geopackage and unfold it. Open a new sql-window. And try to run the code again. – PieterB Apr 09 '19 at 16:55
@PieterB The same error window is still showing. – Ben A. Apr 09 '19 at 16:57
@PieterB Would it be possible for you to send me the CSV that you got in your output? – Ben A. Apr 09 '19 at 16:59
@PieterB I edited my post to include a Python error message that is shown when I run the code. – Ben A. Apr 09 '19 at 17:03
I am no python-programmer. I can see you are working with a Mac. Perhaps it is a bug... – PieterB Apr 09 '19 at 17:05
I did some study on the data. It seems that it are mulitpolygons. So the results are deviating. You will have to make some preprocessing on your data. But this is out of the scope of this question. – PieterB Apr 09 '19 at 17:07
@PieterB As I am unable to obtain that list on my end, and you seem to have been able to generate it on your end, would it be possible for you to send me that list? Thanks. – Ben A. Apr 09 '19 at 17:10
1

Let us continue this discussion in chat. – Ben A. Apr 09 '19 at 17:19
2

Comments are not for extended discussion; this conversation has been moved to chat. – Mapperz Apr 10 '19 at 02:50

Generate list of neighboring polygons QGIS

1 Answers1

Linked