sqlpostgresqlpostgistouches

Find neighboring polygons with maximum of 3 other polygons


I have a case like the following picture

//gbr

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?


Solution

  • 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 ..

    enter image description here

    .. 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;
    

    enter image description here

    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: