11 May
2020
11 May
'20
2:52 p.m.
Message posté par : Nicolas Ribot
----------------------------------------
Oui c'est pratique cette construction itérative, ca permet de faire des boucles, ou de
parcourir des arbres. (https://www.postgresql.org/docs/11/queries-with.html)
Pour avoir le convexhull pour 3, 4, 5...n points par cluster (ici 50):
-----------------
Code :
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;
-----------------
----------------------------------------
Ce message est accompagné de fichiers, pour les télécharger, suivre le lien ci-dessous.
----------------------------------------
Le message est situé https://georezo.net/forum/viewtopic.php?pid=332868#p332868
Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie
Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement
--
Association GeoRezo - le portail géomatique
https://georezo.net