How does one control the rendering order of geometries from a postgis geometrycollection?

Question

I have been using PostGIS for a while now but have only been using simple geometries (points,polylines and polygons). For a particular project of mine I am considering using the geometrycollection format. But I need some help understanding the concept. From what I have read, a geometrycollection stores points,polylines and polygons in a single table. When the geometries are displayed, how is the rendering controlled? If I want points and polylines to always be above polygons how would I define this?

Thanks for any comments,

Robert

Answer 1

The rendering of a geometry collection would be controlled by the program doing the rendering, which wouldn't be PostGIS. What PostGIS could do is specify the order of the geometries within the geometry collection.

So, for an example. Let's say we have the following geometry collection

SELECT
    ST_Collect(ARRAY[
                'POINT(0 0)'::geometry,
                'LINESTRING(3 4, 4 5)'::geometry,
                'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
                'POINT(0 0)'::geometry,
                'POINT(0 0)'::geometry,
                'LINESTRING(3 4, 4 5)'::geometry,
                'POINT(0 0)'::geometry,
                'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
                'POINT(0 0)'::geometry
    ]::geometry[]) AS geom

If we dump the geometry collection...

WITH t1 AS (
    SELECT
        ST_Collect(ARRAY[
                    'POINT(0 0)'::geometry,
                    'LINESTRING(3 4, 4 5)'::geometry,
                    'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
                    'POINT(0 0)'::geometry,
                    'POINT(0 0)'::geometry,
                    'LINESTRING(3 4, 4 5)'::geometry,
                    'POINT(0 0)'::geometry,
                    'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
                    'POINT(0 0)'::geometry
        ]::geometry[]) AS geom
),
t2 AS (
    SELECT
        ST_NumGeometries(geom) as num,
        geom
    FROM t1
),
t3 AS (
    SELECT
        generate_series(1, num) AS pos
    FROM t2
)
SELECT
    t3.pos,
    ST_GeometryType(ST_GeometryN(geom, t3.pos)) AS geometry_type
FROM t2
CROSS JOIN t3

You'd get the following output...

pos;geometry_type
1;"ST_Point"
2;"ST_LineString"
3;"ST_Polygon"
4;"ST_Point"
5;"ST_Point"
6;"ST_LineString"
7;"ST_Point"
8;"ST_Polygon"
9;"ST_Point"

You can reorder the geometries of the collection using...

WITH t1 AS (
    SELECT
        ST_Collect(ARRAY[
            'POINT(0 0)'::geometry,
            'LINESTRING(3 4, 4 5)'::geometry,
            'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
            'POINT(0 0)'::geometry,
            'POINT(0 0)'::geometry,
            'LINESTRING(3 4, 4 5)'::geometry,
            'POINT(0 0)'::geometry,
            'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
            'POINT(0 0)'::geometry
        ]::geometry[]) AS geom
)
SELECT
    ST_Collect((dump).geom) AS geom
FROM ((
    -- points
    SELECT
        ST_Dump(ST_CollectionExtract(geom, 1)) AS dump
    FROM t1
) UNION ALL (
    -- linestrings
    SELECT
        ST_Dump(ST_CollectionExtract(geom, 2)) AS dump
    FROM t1
) UNION ALL (
    -- polygons
    SELECT
        ST_Dump(ST_CollectionExtract(geom, 3)) AS dump
    FROM t1 
)) foo

If we dump the reordered collection...

WITH t1 AS (
    SELECT
        ST_Collect(ARRAY[
            'POINT(0 0)'::geometry,
            'LINESTRING(3 4, 4 5)'::geometry,
            'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
            'POINT(0 0)'::geometry,
            'POINT(0 0)'::geometry,
            'LINESTRING(3 4, 4 5)'::geometry,
            'POINT(0 0)'::geometry,
            'POLYGON((3 4, 4 4, 4 5, 3 5, 3 4))'::geometry,
            'POINT(0 0)'::geometry
        ]::geometry[]) AS geom
),
t2 AS (
    SELECT
        ST_Collect((dump).geom) AS geom
    FROM ((
        -- points
        SELECT
            ST_Dump(ST_CollectionExtract(geom, 1)) AS dump
        FROM t1
    ) UNION ALL (
        -- linestrings
        SELECT
            ST_Dump(ST_CollectionExtract(geom, 2)) AS dump
        FROM t1
    ) UNION ALL (
        -- polygons
        SELECT
            ST_Dump(ST_CollectionExtract(geom, 3)) AS dump
        FROM t1 
    )) foo
),
t3 AS (
    SELECT
        ST_NumGeometries(geom) as num
    FROM t2
),
t4 AS (
    SELECT
        generate_series(1, num) AS pos
    FROM t3
)
SELECT
    t4.pos,
    ST_GeometryType(ST_GeometryN(geom, t4.pos)) AS geometry_type
FROM t2
CROSS JOIN t4

You'd get the following output...

pos;geometry_type
1;"ST_Point"
2;"ST_Point"
3;"ST_Point"
4;"ST_Point"
5;"ST_Point"
6;"ST_LineString"
7;"ST_LineString"
8;"ST_Polygon"
9;"ST_Polygon"

Answer 2

The rendering is controlled by the user application, as there is no inherent type ordering in the geometry collection format itself. You could add another dimension to help with ordering (Z, M), but in the end it may be simpler to just have multiple separate geometry columns.