Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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"
share|improve this answer
    
Thanks for your reply. This seems quite complicated as I´m seeing this for the first time. I guess I would have to make views which extract the points,polygons and lines and then render them with geoserver/openlayers as normal. In this case using a geometrycollection only means that I have one table full of all geometries instead of 3 tables for separate geometry types. Is this right? –  Robert Buckley Jul 13 '12 at 12:06
    
what is then the advantage of using geometrycollections?...1 table instead of 3? –  Robert Buckley Jul 13 '12 at 12:07
    
geometry collections are a catch-all container. I've mostly seen them used as outputs of functions (most recently using ST_MakeValid and ST_Intersection) where the output (that is itself correct) has to return geometries of different types. –  dustymugs Jul 13 '12 at 17:06
    
You typically don't want to be running around with geometry collections as there are many PostGIS functions that can't work with them. This is because geometry collections can contain any geometry type, thus leading to ambiguity. You're better off with three columns (one per geometry type) as that is easier to manage and explicit of what is in the column. –  dustymugs Jul 13 '12 at 17:09
    
Sorry about the complexity of the example. It's complicated because the example is self contained within one SQL statement. –  dustymugs Jul 13 '12 at 17:10

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.

share|improve this answer
    
"have multiple separate geometry columns"...could you explain?...or do you mean separate geometry tables? –  Robert Buckley Jul 10 '12 at 19:37
    
Tables can have multiple geometry columns, which could save you some joins and lookups in comparison to separate tables. –  lynxlynxlynx Jul 10 '12 at 19:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.