Creating Geometry Collection from set of Pointz using PostGIS?

Question

I have a large number of 3D points that are stored as the postGIS Pointz type. I'd also like to create a geometry collection from these points, but I'm struggling to do it directly in SQL. I've tried the following, but this only puts one point into the geometry collection:

UPDATE public.geometry_collection
SET geometry_collection_data = 
    ST_ForceCollection(
        (SELECT ALL object.geometric_point -- this is the pointz type
        FROM object,point,geometry_collection
        WHERE object.pk IN
            (
            SELECT object_pk 
            FROM point
            WHERE point.geometry_collection_pk = geometry_collection.geometry_collection_pk
    )) 
);

Is it possible to select pointz and insert the selection into a geometry collection column?

Answer 1

I think you want something like this,

UPDATE public.geometry_collection
SET geometry_collection_data = (
  SELECT ST_Collect(object.geometric_point)
  FROM object
  INNER JOIN point
    ON point.geometry_collection_pk = geometry_collection.geometry_collection_pk
  CROSS JOIN geometry_collection
);

That gets things into MULTIPOINT Z, if you want GEOMETRYCOLLECTION Z wrap it again in ST_ForceCollection, like this..

SELECT ST_AsText(ST_ForceCollection(ST_Collect(pt)))
FROM ( VALUES
  (ST_MakePoint(0,0,0)),
  (ST_MakePoint(0,0,0))
) AS t(pt);

                       st_astext                        
--------------------------------------------------------
 GEOMETRYCOLLECTION Z (POINT Z (0 0 0),POINT Z (0 0 0))
(1 row)