Sites' geometries

[LePetitTim]

On Geotrek-admin outdoor module, it is possible to create site with multiple geometries of multiple kinds
On the interface of Geotrek-admin, every single geometries is stocked as main geometry inside a collection geometry (GeometryCollection). Postgis explaination / (fr)

However, with GeometryCollection as it is stocked inside the database, it's possible to generate site with complex collection geometry :
GEOMETRYCOLLECTION(MULTIPOINT(1 1, 2 2), POLYGON((1 1, 2 2, 1 2, 1 1))))

Here, a collection is inside the collection. It will be impossible for the interface of Geotrek-admin to compute it during modification and on export of shapefiles, it will be showed on lists, geotrek-randov3, etc... nonetheless.
It should be stocked as
GEOMETRYCOLLECTION(POINT(1 1), POINT(2 2), POLYGON((1 1, 2 2, 1 2, 1 1))))

Why don't we allow to edit and export it ?

Geometries of sites should not be clustered. One site has x geometries independents. If you need to create a site with x geometries interdependent, it means this site is multiple sites.

How to forbid complex GeometryCollections ?

Django :

1. Add a migration which flatten every GeometryCollection
2. Add a validator on site model which will not create sites when their geometries is not simple GeometryCollections.

SQL :

For now, with all the search i did with postgis, i did not find a solution to constrain adding a complex geometrycollection.
The furthest, i has been, was this constraint :

ALTER TABLE outdoor_site ADD CONSTRAINT is_collection_of_collection (NOT ST_ISCollection(ST_GeometryN(ST_NORMALIZE(site.geometry ),1)));

ST_IsCollection
ST_GeometryN
ST_NORMALIZE

But here the problem is, normalize do not for exemple put any LINESTRING after collections.

For exemple :

SELECT ST_ISCollection(ST_GeometryN(ST_NORMALIZE(ST_GeomFromEWKT('GEOMETRYCOLLECTION(LINESTRING(1 1, 2 2), MULTIPOINT(1 1, 2 2))')),1))

st_iscollection 
-----------------
 f
(1 row)

The normalized/canonical form is :

1. GeometryCollection
2. MultiPolygon
3. Polygon
4. MultiLineString
5. LineString
6. MultiPoint
7. Point

[amandine-sahl]

Avec la fonction st_dump il est possible de récupérer le nombre d'élement et de tester le nombre. En tout cas ST_GeometryN extrait le premier élément de la collection et ce ne sera pas forcement celui que l'on souhaite, vu la façon dont ST_Normalize agit.

--  Exemple de test du nombre de dimension des objets (Il y a surement mieux)

CREATE OR REPLACE FUNCTION test_is_collection(geom geometry)
    RETURNS bool
    LANGUAGE plpgsql
AS $function$
    BEGIN
        IF EXISTS (
            SELECT (st_dump(geom)).path[1] a
            GROUP BY a
            HAVING count(*)>1
        ) THEN 
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END;
$function$

SELECT test_is_collection(ST_GeomFromEWKT('GEOMETRYCOLLECTION(LINESTRING(1 1, 2 2), MULTIPOINT(1 1, 2 2, 2 2), MULTIPOINT(1 1, 2 2))'))
test_is_collection 
-----------------
t
(1 row)
SELECT test_is_collection(ST_GeomFromEWKT('GEOMETRYCOLLECTION(LINESTRING(1 1, 2 2))'))
) a
test_is_collection 
-----------------
f
(1 row)