1

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?

3

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.

  • i've updated my question. I also need to find the name of the Polygon of where that point is in. Sorry for that :D – sqlisql Jul 31 at 11:59
  • ... i think it's not that hard considered that you have the point of the centroid. You should be able to find in which polygon it is situated. – Maximilien jaffrès Jul 31 at 12:02
  • Yea, it could be read from a visual interpretation, but can i query it somehow? – sqlisql Jul 31 at 12:03
  • updated answer, but the meaning of my comment was that you also can try by yourself ^^ – Maximilien jaffrès Jul 31 at 12:09
  • Thanks mate, you helped me alot. I'm just getting started with postgis and I'm a little bit lost. :) – sqlisql Jul 31 at 12:12
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);
  • Yea, im kind of a noob in postgis, so i don't really know what the code for that should look like. :) And i need one single center point from all the polygons combined. – sqlisql Jul 31 at 11:44
  • @sqlisql updated you the answer. – mazinga Jul 31 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.