Re-order multipart polygons in PostGIS

Question

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.

Answer 1

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.

Answer 2

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,