Le portail francophone de la géomatique

with recursive clusters(idcluster, ids, step, maxpts, sumdist, geom) as (
    select gid as idcluster, array[gid] as ids, 1 as step,
           (select round(count(*) * 0.05)::int as numpts from testpt) as maxpts,
           0.0::float as sumdist,
           st_multi(geom)::geometry(MULTIPOINT, 0) as geom
    from testpt

    UNION ALL

    select c.idcluster, c.ids || t.gid, step+1, c.maxpts,
           c.sumdist + t.dist,
           st_multi(st_union(c.geom, t.geom))::geometry(MULTIPOINT, 0) as geom
    from clusters c cross join lateral (
        select tp.gid, c.geom <-> tp.geom as dist, tp.geom
        from testpt tp
        where not(tp.gid = any(c.ids))
        order by c.geom <-> tp.geom
        limit 1
    ) as t
    where c.step <= maxpts - 1
) select sort(ids) as ids,  sumdist, st_convexhull(geom) as geom
from clusters
where step = maxpts
group by sort(ids), 2, 3
order by sumdist;

with recursive clusters(idcluster, ids, step, maxpts, sumdist, geom) as (
    select gid as idcluster, array[gid] as ids, 1 as step,
--            (select round(count(*) * 0.1)::int as numpts from testpt) as maxpts,
           50  as maxpts,
           0.0::float as sumdist,
           st_multi(geom)::geometry(MULTIPOINT, 0) as geom
    from testpt

    UNION ALL

    select c.idcluster, c.ids || t.gid, step+1, c.maxpts,
           c.sumdist + t.dist,
           st_multi(st_union(c.geom, t.geom))::geometry(MULTIPOINT, 0) as geom
    from clusters c cross join lateral (
        select tp.gid, c.geom <-> tp.geom as dist, tp.geom
        from testpt tp
        where not(tp.gid = any(c.ids))
        order by c.geom <-> tp.geom
        limit 1
    ) as t
    where c.step <= maxpts - 1
), tmp as (
    select ids,  step, sumdist, geom,
           row_number() over (partition by step order by sumdist) as rn
    from clusters
    where step > 2
) select row_number() over () as clusterid, step, sumdist, 
         st_setSRID(st_convexhull(geom), 4326)::geometry(polygon, 4326) as geom
from tmp
where rn = 1;