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?
EDIT:
@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'
ERROR: aggregates not allowed in WHERE clause LINE 7: AND GeometryType(ST_ConcaveHull(ST_Collect(geom), 0.85)) != 'LINESTRING'
– djq Dec 12 '12 at 14:38from table_distance as d
with your orginal select and change orginal select to select from it. Somthing likeSELECT _fid, _rid ,500, sub.the_geom FROM (SELECT _fid, _rid ,500, ST_ConcaveHull(ST_Collect(geom), 0.85) As geom FROM FROM table_distance as d WHERE _fid = fid) as sub WHERE GeometryType(sub.the_geom) != LINESTRING ...
– simplexio Dec 13 '12 at 7:44