3

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?

6

You want to one-step it, using the ST_Dump function to get the subcomponents...

WITH geoms AS (
    SELECT name, (ST_Dump(geom)).geom AS geom 
    FROM ne_10m_admin_1_states_provinces
)
SELECT DISTINCT ON (name) name, ST_Centroid(geom) AS geom
FROM geoms
ORDER BY name ASC, ST_Area(geom) DESC;

The dump strips out the individual components. The order by sets up the relation in a good order so that the distinct on can pick off the first entry of each category (name) we care about.

1

I think you can do everything in a single PostGIS query.

First, you need to create a query that splits the multi-polygons into simple polygons. This can be done with something like:

SELECT gid,
    ST_GeometryN(geom, generate_series(1, ST_NumGeometries(geom))) AS geom
FROM ne_10m_admin_1_states_provinces;

where gid is a unique identifier (primary key or other unique field).

Then you can calculate the area for all simple polygons, and keep only the biggest one with something like:

SELECT DISTINCT ON (gid) A.*, ST_Area(geom) area FROM 
    (SELECT gid,
        ST_GeometryN(geom, generate_series(1, ST_NumGeometries(geom))) AS geom
     FROM ne_10m_admin_1_states_provinces) A
ORDER BY gid, area desc;

with the previous query from before as subquery.

Finally, you only need to calculate the centroid on each of them, which can again be done using a subquery:

SELECT gid, ST_Centroid(geom) AS centroid FROM
(SELECT DISTINCT ON (gid) A.*, ST_Area(geom) area FROM 
    (SELECT gid,
        ST_GeometryN(geom, generate_series(1, ST_NumGeometries(geom))) AS geom
     FROM ne_10m_admin_1_states_provinces) A
ORDER BY gid, area desc) B;

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.