Finding a center of polygons, and its coordinates

Question

So I've got multiple polygons. I need to find the center of that area (not the center of each polygon) and get the coordinates of that point and also get the name of the polygon in which that point is in. I know that with st_centroid I can calculate the center of each polygon, but how do I get a single center point,name of the polygon that it is in and its coordinates?

Answer 1

If i am not mislead by the doc of St_centroid

You will get this answer by using St_centroid even with multi geometry. this will render the center of each multi ponderated by area of each components

EDIT : adding polygons where centroid is situated

so something like :

WITH polygons AS (
SELECT St_Collect(t1.geom) as collec
FROM t1 )
SELECT ST_Centroid(polygons.collec), 
St_X(ST_Centroid(polygons.collec)) as x, 
St_Y(ST_Centroid(polygons.collec)) as y,
t1.geom as polygon
from polygons, t1
where St_Contains(t1.geom, ST_Centroid(polygons.collec))

NB : This may return several rows because you may have multiple polygons at the centroid position.

Answer 2

Dissolve all the polygon's geometries (st_union(geom)), calculate it's centroid (st_centroid(st_union)), get the coordinates (st_x(st_centroid), st_y(st_centroid)), find the interstecringbpolygon (where st_intersects(st_centrioid, polygons geom).

create table dissolved_polygons as
select st_union(geom) geom from polygon_layer;
create table centroid as
select st_centroid(geom) geom from dissolved_polygons;
alter table centroid
add column coordinate_x numeric(set precision, set scale);
alter table centroid
add column coordinate_y numeric(set precision, set scale);
update centroid
set coordinate_x = st_x(geom),
    coordinate_y = st_y(geom);
select name, id, whatever, geom from 
polygon_layer a
centroid b
where st_intersects(a.geom, b.geom);