Passing a function name as argument

Question

While trying to deal with GEOMETRYCOLLECTION in PostGIS, I've found here what seems to address the conversion of GEOMETRYCOLLECTION into simple GEOMETRY, but it only works with the ST_Intersect internal function.

As I'm quite lazy, I thought it would be nice to make it more powerful by giving the function to be executed as an argument, but it doesn't work this way.

CREATE OR REPLACE FUNCTION ST_GeoCollConv_(geofunction, geometry, geometry) RETURNS boolean AS $$
        DECLARE
                is1collection boolean;
                is2collection boolean;
        BEGIN

                is1collection := GeometryType($1) = 'GEOMETRYCOLLECTION';
                is2collection := GeometryType($2) = 'GEOMETRYCOLLECTION';

                IF NOT is1collection AND NOT is2collection THEN
                        return PERFORM geofunction($1, $2);
                END IF;


                IF NOT is1collection THEN
                        FOR i IN 1..ST_NumGeometries($2) LOOP
                                IF PERFORM geofunction($1, ST_GeometryN($2, i)) THEN
                                        RETURN true;
                                END IF;
                        END LOOP;
                ELSIF NOT is2collection THEN
                        FOR i IN 1..ST_NumGeometries($1) LOOP
                                IF PERFORM geofunction(ST_GeometryN($1, i), $2) THEN
                                        RETURN true;
                                END IF;
                        END LOOP;
                ELSE
                        FOR i IN 1..ST_NumGeometries($1) LOOP
                                IF ST_GeoCollConv_(geofunction, $1, $2) THEN
                                        RETURN true;
                                END IF;
                        END LOOP;
                END IF;

                RETURN false;

        END;
$$ LANGUAGE 'plpgsql';

How can I achieve this ?

yieldsfalsehood · Accepted Answer · 2014-01-15 14:48:17Z

1

Functions aren't first class citizens in postgres (I can't think of an RDBMS where they are) and can't be passed as arguments. The next closest thing is to enumerate all your possible functions (st_overlaps, st_intersects, st_within, etc), pass a flag indicating which function you're talking about, and incorporate that in your if statements, e.g. "if func = 'overlaps' and st_overlaps(geom1, geom2) else if func = 'within' and st_within(geom1, geom2) ...".

Geometry collections can be a bit frustrating to deal with. Are you forced in to using them? Can you use multipolygons instead?

Also note that the function itself is a little excessive. You can use st_dump to enumerate all geometries (works even if you have just a simple geometry) then join the results. For example:

create or replace function st_intersects_gc(geometry, geometry)
returns boolean
as
$$
    select exists (
             select 1
             from st_dump($1) a
                    join st_dump($2) b
                      on st_intersects(a.geom, b.geom)
             limit 1
           )
    ;
$$
language sql
immutable
;

Sample usage (there should also be an example illustrating that this works properly for collections as well but I don't have one on hand):

-- returns false
select st_intersects_gc('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry)

-- returns true
select st_intersects_gc('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry)

In your shoes, I'd write up one of those for each function you're planning on using.

answered Jan 15, 2014 at 14:48

yieldsfalsehood

3,1051 gold badge21 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

annoyed Over a year ago

Thanks yieldsfalsehood, I have more success with your solution than with any experiment I've made so far, particularly if I use the ST_Overlaps function. I dont know yet how to use multipolygons and will investigate in this direction. (not enough reputation for the +1)

Collectives™ on Stack Overflow

Passing a function name as argument

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related