[postgis-users] error updating multipoint: violates check constraint enforce_geotype

[Nicolas Ribot]

> Hi,
>
> Nicolas, thanks for your response.
> I would hope that my query returns multipoints. At least it always has in
> the test runs. For some reason when I let it run on our large dataset, it
> doesn't work.
>
> As a test I decided to skip that one troublesome column and load the other
> points. But I get the same error for a different column now. On a dataset of
> roughly 960k rows, I got the error 8 times.
> I load data into several multipoint columns. For example, I have one column
> that stores long, lat and alt as groups of multipoints with 1k points per
> multipoint and then another column that stores x,y and z as groups of
> multipoints with 1k points per multipoint. I have 6 of those multipoint
> columns.
>
> I am not sure if an ST_Multi around the ST_Union would do anything.
>
> Does anybody have any ideas????
>
> Any help is greatly appreciated, as I don't understand why this is happening
> at all!!
>

Hi,
What are the constraints defined for the table ? (\d lidarpoints in psql).
One row may return null or a geometryCollection, which is rejected by
the constraint.

You could test the update query by changing it to a select, to see
what are the returned type of the st_union:

select distinct st_geometryType(ST_UNION(numtarclassidreflect,
ST_GeomFromEWKT('SRID=4326;MULTIPOINT(2 0 -1436)')))
from lidarpoints
where lidarpts_id = currentRowID;

or maybe add a not null where clause, if it appears some rows are
returning null values.

Nicolas