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 = 
        (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?


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)))
) AS t(pt);

(1 row)
| improve this answer | |
  • I've tried it, but there's still nothing in the geometry_collection_data field ... I have spent quite a lot of time searching the web for examples of converting pointz into geometrycollectionz, but so far have not found a single example. – craigL Sep 12 '17 at 4:39
  • @craigL updated check again – Evan Carroll Sep 12 '17 at 6:14
  • Yes, that works, many thanks Evan! Note the CROSS JOIN is not needed... – craigL Sep 13 '17 at 2:59
  • @craigL what do you mean, it's not needed? – Evan Carroll Sep 13 '17 at 3:00
  • Don't need the Cartesian product of the tables, just the values INNER joined on the geometry collection pk. – craigL Sep 13 '17 at 3:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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