convex hull problem / "geom-type or SRID not allowed"

[Dries Bierens]

Hello,

I am tracking a couple of animals using VHF collars. As my fieldwork progresses, I'd like to calculate changes in the the convex hull of my (growing) dataset.

The approach I took was to create a table for the convex hulls and staging table with a trigger to calculate the hull for each row upon an insert into that staging table.

CREATE TABLE m_convex(
  id INTEGER PRIMARY KEY,
  area REAL);
SELECT AddGeometryColumn('m_convex', 'geometry', 28992, 'POLYGON', 2);

CREATE TABLE m_staging (id INTEGER PRIMARY KEY);
SELECT AddGeometryColumn('m_staging', 'geometry', 28992, 'POINT', 2);

CREATE TRIGGER update_mconvex AFTER INSERT ON m_staging FOR EACH ROW
  BEGIN
    INSERT INTO m_convex (geometry)
      SELECT ST_ConvexHull(ST_Collect(ms.geometry))
      FROM m_staging As ms;
  END;

INSERT INTO m_staging (id, geometry)
  SELECT m.id, m.geometry FROM marianne As m;

This last command gives the error message "geom-type or SRID not allowed". I checked all column names and geometry types and can't find the source of this error myself.

As an aside, the table "marianne" was created with the following code:

CREATE TABLE marianne(
  id INTEGER PRIMARY KEY,
  x INTEGER,
  Y INTEGER);

SELECT AddGeometryColumn('marianne', 'geometry', 28992, 'POINT', 2);

INSERT INTO marianne (id, x, y)
  SELECT id, x, y
  FROM marianne_tmp;

UPDATE marianne SET geometry = MakePoint(x, y, 28992);

I'm using spatialite-gui 2.0.0-devel.

I appreciate any help on this problem. Suggestions for different ways to get the result I'm after are also welcome.

[a.fu...@lqt.it]

Hi Dries,

you've declared a table containing a Polygon Geometry:

SELECT AddGeometryColumn('m_convex', 'geometry', 28992, 'POLYGON', 2);

then the following INSERT fails reporting "geom-type or SRID not
allowed"

INSERT INTO m_convex (geometry)
SELECT ST_ConvexHull(ST_Collect(ms.geometry))
FROM m_staging As ms;

the most obvious cause explaining for this issue is that
after all you are actually attempting to insert a Geometry
of some forbidden type.
And here is the most plausible explanation: you are
expecting that ST_ConvexHull() must always return a
Polygon, as in:

SELECT ST_AsText(
ST_ConvexHull(
ST_GeomFromText('MULTIPOINT(1 1, 2 2, 1 2)', 4326)));
----------------------
POLYGON((1 1, 1 2, 2 2, 1 1))

anyway sometimes ST_ConvexHull() can legitimately return
non-Polygon results, as the following snippet exemplifies:

SELECT ST_AsText(
ST_ConvexHull(
ST_GeomFromText('MULTIPOINT(1 1)', 4326)));
--------------------
POINT(1 1)

bye Sandro

[Dries Bierens]

Thank you Sandro for pointing me in the right direction. I solved my problem by changing the trigger as follows:

CREATE TRIGGER update_mconvex AFTER INSERT ON m_staging FOR EACH ROW
BEGIN

  INSERT INTO m_convex(id, datetime, geometry)
  SELECT id, datetime,
    CASE
      WHEN id <=2 THEN NULL
      ELSE ST_ConvexHull(ST_Collect(geometry))
    END
  FROM m_staging;
  UPDATE m_convex SET area = ST_Area(geometry);
END;