1

For a project I have to show the relationship between 2 or more objects. For this I use the code below in QGIS in step 1 I determine the relation in step 2 the items are counted. In the test set I use 5128 objects here QGIS takes quite a long time, the original data set is more than 1 million objects. I wonder would this also be possible in PostgreSQL / PostGIS to make the whole faster and reproducible?

Code for QGIS 3 Step 1


aggregate(
 layer:= ‘Test',
 aggregate:='concatenate',
 expression:=Label,
 concatenator:=', ',
 filter:=touches($geometry, geometry(@parent))
 )

Step 2

aggregate(
 layer:= ‘Test’,
 aggregate:='count',
 expression:=Label,
 filter:=touches($geometry, geometry(@parent))
 )

product

Example

result

In the result you can see that an (label) object has a relation with an other object 1 to N=(Count)

1

In Postgres, you can join and query the same layer: once for the "source", and one for the connected neighbors. Grouping by source ID will let you count the connected neighbors and will also open the door to aggregate functions.

A spatial index is required if you want a descent execution time.

SELECT a.id, count(*), string_agg(b.id::text,',') as neighbors
FROM test a JOIN test  b ON st_touches(a.geom, b.geom)
GROUP BY a.id;
  • Thanks I'll try it with a test data set to see if it will work for me – RobertL Jul 21 at 12:43

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.