Le portail francophone de la géomatique

-- En 2 etapes:
-- 1) découpage des cercles avec st_polygonize

-- creation des cercles
drop table if exists circle;
create table circle as
    select * from (values
        (1, st_buffer('POINT(0 0)'::geometry, 3)),
        (2, st_buffer('POINT(3 3)'::geometry, 2)),
        (3, st_buffer('POINT(5 1)'::geometry, 3))
        ) as v(id, geom);

alter table circle add primary key (id);
create index circle_geom_gist on circle using gist(geom);
analyse circle;

-- creation des polygones issus du découpage
drop table if exists pg_cut;
create table pg_cut as
with tmp as (
    select st_union(st_boundary(geom)) as geom
    from circle
), tmp1 as (
    select st_dump(st_polygonize(geom)) as dmp
    from tmp
) select (dmp).path[1] as newid, (dmp).geom
from tmp1;

-- 2) association entre nouveaux et anciens polygones, avec st_pointOnSurface
-- et index spatial sur pointOnSurface (pas nécessaire ici, mais a faire si bcp de polygones)

-- clé primaire sur la nouvelle table
alter table pg_cut add primary key (newid);

-- index sur point on surface
create index pg_cut_pos_gist on pg_cut using gist(st_PointOnSurface(geom));
analyse pg_cut;

-- association spatiale nouveaux-ancien:
select new.newid, sum(old.id)::float/count(old.id) as moyenne
from circle old join pg_cut new on st_intersects(st_pointonsurface(new.geom), old.geom)
group by new.newid;
-- newid    moyenne
-- 1    2
-- 2    3
-- 3    1
-- 4    1.5
-- 5    2
-- 6    2
-- 7    2.5