PostgreSQL Queries pgAdmin versus Function call

Question

I'm attempting to write a spatial query function that identifies the records associated with a specific spatial geometry at some area criteria

Firstly, when I execute the following command in pgAdmin I do receive results. Here is the command:

    SELECT *  FROM temporary.table1 WHERE 
        public.ST_Area(public.ST_GeogFromWkb(geom))/1000000 <= area;

When I add the SAME SELECT to my function which is called from VB.NET code through our Devart PostgreSQL library I receive the following error:

spatial_ref_sys not found

This usually indicated one has used a postgreSQL function that requires specifying an SRS. The function ST_GeogFromWkb has ONE (1) functional prototype which can be called -- one without the SRS. Our geom column values are in WKB format hence I am using the aforementioned function. The ST_Area also takes only one argument -- the geometry. So I'm not sure why I'm receiving the SRS error.

Note: the function below was created using the pgAdmin properties GUI for creating a function.

The function _fselectbyarea(integer):

   CREATE OR REPLACE FUNCTION _fselectbyarea(area integer DEFAULT 100)  
        RETURN SETOF table1 AS  
   $BODY$BEGIN
       RETURN QUERY SELECT *  FROM temporary.table1 WHERE 
       public.ST_Area(public.ST_GeogFromWkb(geom))/10000000 <= area;
   END;
   $BODY$
   LANGUAGE plpgsql STABLE
   COST 100
   ROWS 1000;

   ALTER FUNCTION _fselectbyarea(integer)  
        ORDER TO user_group;
        COMMENT ON FUNCTION _fselectbyarea(integer) IS 'my comment here';

Granted there are come concerns with the function above... a) When I remove the default ROWS and COST values the GUI will still include them in the resultant sql. These are default values which cannot be controlled. b) I elected to use STABLE as the volatility mode since I'm querying one table.

Assumptions: I assume when I run the SELECT in pgAdmin there must be an assumed SRS so it executed when I execute the GeogFromWkb function. However when I execute the function above there is no SRS specified so I receive the error: spatial_ref_sys not found. I'm thinking that when I run the SELECT in pgAdmin the SRS is set in the environment so it works; however, when I execute the function the environment has no knowledge of an SRS and errors.

When the function is called I am executing the query for ANYWHERE on the earth so I can't pin point my SRS to a specific UTM zone when I make a function call from the application. I do have code that takes Lat/Lon and computes the UTM zone but I was trying to avoid doing additional computation to compute the UTM zone. If I must use it I'll just have to create a lookup table to get the PostgreSQL SRS id.

Questions: Is my assumption correct about the resultant SRS error I receive? Any tweaks to my function which would improve the query?

Answer 1

It seems to be the same issue as in this question

The user executing the function does not have the public schema in its search path, so any PostGIS internal function will fail. Should you remove the public prefix in your query, the failure will occur before, i.e. when calling st_area. To fix it, add public to the user search path, or to the function:

CREATE OR REPLACE FUNCTION _fselectbyarea(area integer DEFAULT 100)  
   RETURN SETOF table1 AS  
$BODY$
BEGIN
   RETURN QUERY SELECT *  FROM temporary.table1 WHERE 
   ST_Area(ST_GeogFromWkb(geom))/10000000 <= area;
END;
$BODY$
    LANGUAGE plpgsql STABLE
    COST 100
    ROWS 1000
    SET search_path = public;  

It works in PGAdmin as the user running the query has the public schema in its search path. Let's note that the SRS is not set in an environmental variable, it just has to be 4326. Even if it is not specified as an input but has an hard coded value, the SRS is still used internally.