Le portail francophone de la géomatique

with tmp as (
    select st_clusterintersecting(geom) as arr
    from batiment
), tmp1 as (
    select u.ordinality as idcluster, u as geom
    from tmp, unnest(tmp.arr) with ordinality as u
), tmp2 as (
    select t.idcluster, t2.idcluster as idcluster2, t2.dist, st_unaryunion(t.geom) as geom, t2.geom as geom2
    from tmp1 t
             cross join lateral (
        select b.idcluster, b.geom <-> t.geom as dist, b.geom
        from tmp1 b
        where b.idcluster != t.idcluster
        order by b.geom <-> t.geom
        limit 1
        ) as t2
) select t.idcluster, t.idcluster2, dist, array_agg(b.id) as id_batiments,
         st_shortestline(t.geom, t.geom2) as geomline
from tmp2 t join batiment b on st_contains(t.geom, st_pointonsurface(b.geom))
group by 1,2,3,5; 


idcluster    idcluster2    dist    id_batiments
1           3            49.8    {1}
2           3            97.2    {2}
3           1            49.8    {3,4,5,6,7,8}
4           2            176.    {9}
5           6            78.3    {10,13}
6           7            41.7    {11}
7           6            41.7    {12}
8           1            99.1    {14}
9           8            100.    {15}