I have created the following function in PL/SQL to make a polygon from a set of linestrings:
CREATE OR REPLACE FUNCTION _myfunction( _fid integer, _rid integer ) RETURNS VOID AS $$ BEGIN BEGIN INSERT INTO _data (fid, rid, cost, geom) ( SELECT _fid, _rid, 500, ST_ConcaveHull(ST_Collect(geom), 0.85) As geom FROM table_distance as d WHERE _fid = fid); END; END; $$ LANGUAGE plpgsql;
I defined the table that this function inserts data into as:
CREATE TABLE _data (fid integer, rid integer, cost double precision); SELECT AddGeometryColumn('_data','geom',900913,'POLYGON',2);
This function works most of the time, except when the result of making a concave hull around
ST_Collect(geom) is a linestring, instead of a polygon. When a linestring is returned, I get the following error message:
********** Error ********** ERROR: Geometry type (LineString) does not match column type (Polygon) SQL state: 22023 Context: SQL statement "INSERT INTO _data (fid, rid, cost, geom) ( SELECT _fid, _rid, 500, ST_ConcaveHull(ST_Collect(geom), 0.85) As geom FROM table_distance as d WHERE _fid = fid)" PL/pgSQL function "_ncg_isodist" line 4 at SQL statement
I would like to disregard the result if a line is returned, but I'm not sure how to do this. I started off by nesting the block in a
BEGIN END; statement, hoping that the function would not crash, but this does not solve the problem.
I then wondered if there was some way of checking either the geometry type that is returned, or some other feature of
ST_Collect(geom) so that I know when a polygon has been created. I noticed the
ST_Dump() function, but I can't figure out how to count the number of values in
ST_Dump() in this query as I get the error message that
aggregate function calls cannot be nested. Any thoughts on how I can solve this?
@simplexio suggested using
GeometryType(ST_ConcaveHull(ST_Collect(geom), 0.85)) != 'LINESTRING' in the
WHERE clause. I tried this, but got a similar error to earlier:
ERROR: aggregates not allowed in WHERE clause LINE 7: AND GeometryType(ST_ConcaveHull(ST_Collect(geom), 0.85)) != 'LINESTRING'