Le portail francophone de la géomatique

create table distance as(
select p1.gid, t.gid as gid2, t.dist
from poly cross join lateral
  (select p2.gid, st_distance(p1.geom, p2.geom) as dist
  from poly p2
  where p1.gid <> p2.gid  and p1.geom <#> p2.geom < 100
  order by p1.geom <-> p2.geom
  ) as t
order by p1.gid, t.dist);

drop table distance_100;
create table distance_100 as
select min(gid) as gid, max(gid2) as gid2, dist
from distance
where dist < 100
group by dist
order by gid;

drop table distance_100_group;
create table distance_100_group as
with t as (
    /* Avec cette étape je tente de faire une "chaine" pour trouver les polygones connectés
            Ex : Si - poly 1 connecté à 2 et 3
                    - poly 2 connecté à 5 et 6
                --> Alors poly  connecté à 2,3,5,6
    */
    SELECT
      a.gid,
      array_remove(ARRAY [a.gid, a.gid2, b.gid2, c.gid2, d.gid2, e.gid2, f.gid2, g.gid2, h.gid2, i.gid2, j.gid2], NULL) as ids,
      array_length(array_remove(ARRAY [a.gid, a.gid2, b.gid2, c.gid2, d.gid2, e.gid2, f.gid2, g.gid2, h.gid2, i.gid2, j.gid2], NULL),1) as long
      FROM distance_100 a
        LEFT JOIN distance_100 AS b ON a.gid2 = b.gid
        LEFT JOIN distance_100 AS c ON b.gid2 = c.gid
        LEFT JOIN distance_100 AS d ON c.gid2 = d.gid
        LEFT JOIN distance_100 AS e ON d.gid2 = e.gid
        LEFT JOIN distance_100 AS f ON e.gid2 = f.gid
        LEFT JOIN distance_100 AS g ON f.gid2 = g.gid
        LEFT JOIN distance_100 AS h ON g.gid2 = h.gid
        LEFT JOIN distance_100 AS i ON h.gid2 = i.gid
        LEFT JOIN distance_100 AS j ON i.gid2 = j.gid
),  t1 as (
    -- Calcul de la longueur max des tableau pour un même gid de départ afin de trouver la plus grande chaine de polygones
    SELECT
      gid,
      max(long) AS max
    FROM t
    GROUP BY gid
), t2 as 
    -- Selection des chaines de polygones les plus longues et explosion des tableaux
    SELECT
      t.gid,
      unnest(t.ids) as ids
    FROM t
      RIGHT JOIN t1 ON t.gid = t1.gid AND t.long = t1.max
)  -- Récupération des geom et union des geom avec un group by gid
 SELECT t2.gid, st_union(p.geom)
 FROM t2
 LEFT JOIN polygon p ON t2.ids = p.gid
 GROUP BY t2.gid