Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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'
share|improve this question
    
Add GeometryType(ST_ConcaveHull(ST_Collect(geom), 0.85)) != LINESTRING to WHERE clause ? For more information about GeometryType see postgis.refractions.net/docs/GeometryType.html –  simplexio Dec 12 '12 at 8:06
    
Thanks for the suggestion. I still get an aggregation error though when using this approach: 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:38
    
You could try replace from table_distance as d with your orginal select and change orginal select to select from it. Somthing like SELECT _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
    
I have problems with ST_ConcaveHull(geom,target_percent) when target_percent less than 0.98 (!); you using 0.85, try 0.98 only for check changes at error messages. –  Peter Krauss Dec 31 '13 at 10:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.