Re-order multipart polygons in PostGIS

I have a table with multpart polygon geometry with coordinate system EPSG:4326. I can identify, what are geometries with more than 1 polygons in it. I want to re-order it in a way that the largest polygons comes first and then the smallest for multipart geometries.

How can I do this in PostGIS, using any query?

I have a multipart geometry e.g.

``````MULTIPOLYGON (((40 40, 20 45, 45 30, 40 40)), ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35), (30 20, 20 15, 20 25, 30 20)))
``````

This geometry has three polygons,

1- ((40 40, 20 45, 45 30, 40 40))

2- ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)

3- (30 20, 20 15, 20 25, 30 20))

These three polygons have different area e.g.

1- 10 square meters

2- 15 square meters

3- 12 Square meters

What is want is to re-order the geometry from highest area to lowest area i.e. the output should be ..

Output

``````MULTIPOLYGON (((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)), ((30 20, 20 15, 20 25, 30 20)),  ((40 40, 20 45, 45 30, 40 40)))
``````

Update

Please forget about ring in the polygon and just consider three polygons. This example was just to give an idea of what i am trying to achieve.

• You have only two polygons in your geometry, not three. `( 30 20, 20 15, 20 25, 30 20 )` is a hole in another polygon. May 20, 2019 at 12:25
• @Cyril, I feel that hole matters at least a bit because it a) holes do not have area, and b) hole cannot be simply converted into a polygon because the result is an invalid geometry due to nested shells. Check with `select st_isvalidreason(st_geomfromtext('MULTIPOLYGON (((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)), ((30 20, 20 15, 20 25, 30 20)), ((40 40, 20 45, 45 30, 40 40)))'))`. In GeometryCollection that would be doable. I fixed the syntax in question but geometry is still invalid. May 20, 2019 at 13:17
• What are you really interested in? ST_Area gives automatically the area of the polygon so that area of holes is not included. But is it important for you to know the area of the hole as well? May 20, 2019 at 15:02
• Cyril, I am sorry but I would like to understand your test. PostGIS does not accept your select if I just copy-paste it. If I modify it into `select st_astext (st_makevalid (st_geomfromtext ('MULTIPOLYGON (((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)), ((30 20, 20 15, 20 25, 30 20)), ( (40 40, 20 45, 45 30, 40 40))) ')))` I get back `MULTIPOLYGON(((20 35,45 20,30 5,10 10,10 30,20 35),(30 20,20 25,20 15,30 20)),((40 40,45 30,20 45,40 40)))` which has two polygon members, one with a hole and one without. I guess that this is not the result that you want to show for me. May 20, 2019 at 19:37

To be noticed: Area and length measurements in EPSG:4326 and do not give reasonable results but that's dealt with many other quesions https://gis.stackexchange.com/search?q=epsg%3A4326+area+

Re-ordering the multipolygon members by area can be done by exploding the multipolygon with ST_Dump, ordering the primitive polygons by the result of ST_Area and finally building a new multipolygon from the sorted primitives with ST_Collect. I do not know how reliable that is because by definition the order of members in geometry collections, including MultiPolygons, does not matter.

Test 1 by sorting members by area in ascending order:

``````SELECT ST_AsText(ST_Collect(b.primitive_geom)) AS collected_geometry
from (
SELECT (a.p_geom).geom as primitive_geom, ST_Area((a.p_geom).geom) as sub_area
FROM (
SELECT ST_Dump(
ST_GeometryFromText('MULTIPOLYGON ((( 220 500, 220 540, 260 540, 260 500, 220 500 )), (( 280 460, 280 540, 360 540, 360 460, 280 460 )))'))
as p_geom) AS a ORDER BY sub_area ASC) AS b;

Result:
MULTIPOLYGON(((220 500,220 540,260 540,260 500,220 500)),((280 460,280 540,360 540,360 460,280 460)))
``````

Test 2 by sorting members by area in descending order:

``````SELECT ST_AsText(ST_Collect(b.primitive_geom)) AS collected_geometry
from (
SELECT (a.p_geom).geom as primitive_geom, ST_Area((a.p_geom).geom) as sub_area
FROM (
SELECT ST_Dump(
ST_GeometryFromText('MULTIPOLYGON ((( 220 500, 220 540, 260 540, 260 500, 220 500 )), (( 280 460, 280 540, 360 540, 360 460, 280 460 )))'))
as p_geom) AS a ORDER BY sub_area DESC) AS b;

Result:
MULTIPOLYGON(((280 460,280 540,360 540,360 460,280 460)),((220 500,220 540,260 540,260 500,220 500)))
``````

Finally a test with your geometry that is actually a multipolygon with two polygons, one with a hole and another without.

``````SELECT ST_AsText(ST_Collect(b.primitive_geom)) AS collected_geometry
from (
SELECT (a.p_geom).geom as primitive_geom, ST_Area((a.p_geom).geom) as sub_area
FROM (
SELECT ST_Dump(
ST_GeometryFromText('MULTIPOLYGON (((40 40, 20 45, 45 30, 40 40)), ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35), (30 20, 20 15, 20 25, 30 20)))'))
as p_geom) AS a ORDER BY sub_area ASC) AS b;

Result:
MULTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20)))
``````

I guess that you just did not notice that one polygon ring is a hole.

• thanks for the answer. This is somehow worked for me. May 22, 2019 at 9:32

1) reordering the wrong geometry type multipolygon can not be!

2) reorder them...

```SELECT id, area, ST_CollectionExtract((ST_Multi(ST_Collect(geom))),3) geom FROM source_table GROUP BY id, area, geom ORDER BY id, area, geom DESC;```

P.S.

So let's summarize.

Together, through my presentation we get the following picture.

3) Arguing further, using this set of geometry, you can get three different views, taking into account various transformations.

3.1 Representation of the first basic as in Kirill - which means the presence of incorrect geometry, that all three "full" polygons are aligned in one layer, in that situation you need to dissolve all polygons into one layer, https://gis.stackexchange.com/a/785/120129

3.2 Presentation of the second (OP Output) - which means the presence of correct geometry, and in his case, a large boligon COVERS the internal polygon and all of them are also “full”.

3.3 Presentation of the third user 30184 (OP Input) - which means the presence of invalid geometry - "holes" and it is shown in the his screenshot.

I think that everything turned out right with us, and let this material in the future help the young GIS specialists to sort out this situation.

Respectfully,

• i have updated my question. Please take a look now. May 20, 2019 at 10:49