2

I use PostGIS and I would like to add a new point to an existing MultiPoint using ST_Collect. The problem is that I got this error message:

ERROR: Geometry type (GeometryCollection) does not match column type (MultiPoint) CONTEXT: PL/pgSQL function inline_code_block line 10 at assignment SQL state: 22023

What do you think? What is wrong in my code? The first ST_Collect runs successfully, but the second one is not.

My code:

DO $$ 
<<first_block>>
DECLARE
  geom Geometry (MultiPoint,0);
  astext varchar;
BEGIN 
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 4)'))); 
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 10)')));
SELECT ST_AsText(geom) INTO astext;
RAISE NOTICE '%',astext;
END first_block $$;

I tried with GeometryCollection instead of MultiPoint:

DO $$ 
<<first_block>>
DECLARE
  geom Geometry (GeometryCollection,0);
  astext varchar;
BEGIN 
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 4)')));
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 10)')));
SELECT ST_AsText(geom) INTO astext;
RAISE NOTICE '%',astext;
END first_block $$;

And I've got this error:

ERROR: Geometry type (MultiPoint) does not match column type (GeometryCollection) CONTEXT: PL/pgSQL function inline_code_block line 9 at assignment SQL state: 22023

I tried with Multipoints too:

DO $$ 
<<first_block>>
DECLARE
  geom Geometry (MultiPoint,0);
  astext varchar;
BEGIN 
geom=ST_Collect(geom,(ST_GeomFromText('MULTIPOINT(1 4)')));
geom=ST_Collect(geom,(ST_GeomFromText('MULTIPOINT(1 10)')));
SELECT ST_AsText(geom) INTO astext;
RAISE NOTICE '%',astext;
END first_block $$;

Error message:

ERROR: Geometry type (GeometryCollection) does not match column type (MultiPoint) CONTEXT: PL/pgSQL function inline_code_block line 10 at assignment SQL state: 22023

  • When you promise to PostGIS that you'll only assign MultiPoint values to a column, it keeps you to that promise. Note that, while legal, assigning a SRID of zero is very much not best practice. Instead use the correct SRID for the coordinate values. – Vince Mar 12 at 11:30
  • I tried with MultiPoint everywhere, but the problem is the same. I added this code to my question. I agree with you regarding the SRID, but now it is irrelevant, I wanted to ask a simple question, so I simplified my code. – zsoooc Mar 12 at 11:55
  • ST_Collect generates a collection, which is invalid for a MultiPoint column. – Vince Mar 12 at 12:30
  • 1
    ...but ST_Union will help you keep your promises. – geozelot Mar 12 at 13:12
4

The doc on st_collect describes this situation:

If any of the input geometries are collections (Multi* or GeometryCollection) ST_Collect returns a GeometryCollection (since that is the only type which can contain nested collections). To prevent this, use ST_Dump in a subquery to expand the input collections to their atomic elements

So to use st_collect you would dump the existing multipoints to points, include the new point and finally re-collect them all

select ST_AsText(ST_collect(dump_geom))
FROM (
    select(st_dump('MULTIPOINT(-2 3,1 2)' :: geometry)).geom as dump_geom 
    UNION
    select 'POINT(10 10)' :: geometry
    ) sub;

----------------------------
 MULTIPOINT(-2 3,10 10,1 2)

Or, as suggested by @geozelot, you can rely on st_union which will merge the points for you

 select ST_AsText(ST_Union('MULTIPOINT(-2 3,1 2)' :: geometry, 'POINT(10 10)' :: geometry));

----------------------------
 MULTIPOINT(-2 3,1 2,10 10)
|improve this answer|||||

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.