Find neighboring polygons with maximum of 3 other polygons

Question

I have a case like the following picture

Say I have 9 polygons, and want to get a polygon that is maximum neighbors with 3 other polygons such as polygons 1, 3, 7, 9 (yellow)

I think this is done using ST_Touches in postgis, but I just come up with represent it in postgis code like

select a.poly_name, b.poly_name from tb a, tb b where ST_Touches(a.geom, b.geom)

And say I want to output this like:

 poly_name      poly_name
     1               2
     1               4
     1               5

So how I get idea to done with this?

Answer 1

Your hint with ST_Touches is correct, however to get the amount of neighbor cells from one column related to other records in the same table you either need to run a subquery or call the table twice in the FROM clause.

Given the following grid on a table called tb ..

.. you can filter the cells with three neighbor cells or less like this:

SELECT * FROM tb q1
WHERE (
  SELECT count(*) FROM tb q2
  WHERE ST_Touches(q2.geom,q1.geom)) <=3;

If you want to also list which are the neighbor cells you might wanna first join the cells that touch in the WHERE clause and in a subquery or CTE count the results:

WITH j AS (
  SELECT 
    q1.poly_name AS p1,q2.poly_name p2, 
    COUNT(*) OVER (PARTITION BY q1.poly_name) AS qt
  FROM tb q1, tb q2
  WHERE ST_Touches(q2.geom,q1.geom))
SELECT * FROM j
WHERE qt <= 3;

Demo: db<>fiddle

Further reading:

• Create Hexagons (maybe relevant for your project)
• Window Functions