By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create extension if not exists postgis;
drop table if exists your_table;
create table your_table(
id int generated by default as identity primary key
,cluster_num int
,geom geometry(Point,2154));
insert into your_table values
(1,1,'point(0 0)')
,(2,1,'point(0 100)')
,(3,1,'point(100 100)')
,(4,2,'point(200 200)')
,(5,2,'point(200 300)')
,(6,2,'point(300 300)')
,(7,2,'point(300 200)');
CREATE EXTENSION
DROP TABLE
CREATE TABLE
INSERT 0 7
drop table if exists test;
create table test as
select st_buffer(st_convexhull(st_collect(geom)),9,'quad_segs=1') as geom
from your_table
group by cluster_num;
select st_astext(geom)
from test;
DROP TABLE
SELECT 2
| st_astext |
|---|
| POLYGON((200 191,191 200,191 300,200 309,300 309,309 300,309 200,300 191,200 191)) |
| POLYGON((6.363961030678928 -6.363961030678928,-3.444150891285813 -8.314915792601578,-9 0,-9 100,0 109,100 109,108.31491579260158 103.4441508912858,106.36396103067892 93.63603896932108,6.363961030678928 -6.363961030678928)) |
SELECT 2