Multi-geometry decomposition

Multi-geometries can be broken down into single parts. There are two ways to do this.

• The first is to extract components one part at a time, using the ST_GeometryN function.

For example, extracting the second part from a MultiPoint created by hand can be done using the following:

        SELECT       
        ST_AsText(ST_GeometryN(ST_Collect(ARRAY[ST_MakePoint(20,50),
        ST_MakePoint(19.95,49.98), ST_MakePoint(19.90,49.96)]),2));

         st_astext 
        --------------------
         POINT(19.95 49.98)

The number of Multi-geometry parts can be revealed using the ST_NumGeometries function:

        SELECT
        ST_NumGeometries(ST_Collect(ARRAY(ST_MakePoint(20,50),
        ST_MakePoint(19.95,49.98), ST_MakePoint(19.90,49.96))));

         3  

• The second option is to expand a Multi-geometry into a set of rows using the ST_Dump function. This returns a special compound type, called geometry dump. The geometry dump type consists of two parts: the path, which denotes the position of a component within the Multi-geometry, and the geom being the actual component geometry:

         SELECT
         ST_Dump(ST_Collect(ARRAY[ST_MakePoint(20,50),
         ST_MakePoint(19.95,49.98), ST_MakePoint(19.90,49.96)]));  

This will result in the following:

        st_dump                     
        --------------------------------------------------
        ({1},010100000000000000000034400000000000004940)
        ({2},01010000003333333333F333403D0AD7A370FD4840)
        ({3},01010000006666666666E633407B14AE47E1FA4840)
        (3 rows) 

To extract actual geometries from the geometry dump, the ST_Dump function should be wrapped in parentheses, and its geom part referenced:

        SELECT 
        (ST_Dump(ST_Collect(ARRAY[ST_MakePoint(20,50),
        ST_MakePoint(19.95,49.98), ST_MakePoint(19.90,49.96)]))).geom;                             geom                   
        --------------------------------------------
        010100000000000000000034400000000000004940
        01010000003333333333F333403D0AD7A370FD4840
        01010000006666666666E633407B14AE47E1FA4840 

Now each of the MultiPoint's components are accessible as a single-part geometry, which can be inserted into another table or used in another function.