I'm working with the Natural Earth 10m Admin 1 States Provinces polygon data. This dataset contains multi-polygons for each state / province in the entire world. What I'd like to do is to grab the largest polygon for each state / province, then create a centroid that is inside that polygon for labeling purposes in web cartography.
I went ahead and calculated the area for each polygon using ST_Area(geom::geography) for the dataset and so far my PostGIS query looks like this, which returns the the largest polygon for a single state /province:
SELECT area, name, ST_Centroid(geom) geom FROM ne_10m_admin_1_states_provinces WHERE area IN ( SELECT area FROM ne_10m_admin_1_states_provinces WHERE name = 'state province name' ORDER BY area DESC LIMIT 1 ) However I'm unsure of how to determine the best way to iterate over all features in the dataset to grab the polygon for each state / province with the largest area. Would the best way to do this be to create a function in PGSQL? Or is there a simpler way via plain old SQL?
أكثر...
I went ahead and calculated the area for each polygon using ST_Area(geom::geography) for the dataset and so far my PostGIS query looks like this, which returns the the largest polygon for a single state /province:
SELECT area, name, ST_Centroid(geom) geom FROM ne_10m_admin_1_states_provinces WHERE area IN ( SELECT area FROM ne_10m_admin_1_states_provinces WHERE name = 'state province name' ORDER BY area DESC LIMIT 1 ) However I'm unsure of how to determine the best way to iterate over all features in the dataset to grab the polygon for each state / province with the largest area. Would the best way to do this be to create a function in PGSQL? Or is there a simpler way via plain old SQL?
أكثر...