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?
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.
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);