CODEWITHSUNDEEP
postgresqlpostgisplpgsql
annoyed
annoyed

Reputation: 61

Passing a function name as argument

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 ?

Upvotes: 1

Views: 173

Answers (1)

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

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.

Upvotes: 1

Related Questions