0

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?

  • 2
    Spending time improving questions, even if just beautifying them, is a gift to the community. Pleasant to read questions/answers will be read in the future and will help other people solving their issues, while a poorly crafted one might solve your issue but will be of no use to others. As for the user status, note that editing posts has no impact for most editors. – JGH Sep 11 '18 at 13:51
  • 1
    What does Show search_path; return. And, yes, as JGH says, editing is something people do to improve the site, you don't get any benefit from it -- well a gold star after 600, whoppee :-) – John Powell Sep 11 '18 at 14:00
  • 1
    i agree but often times there is cadre of individuals who are post editing junkies, For the author of the post it can be a bit frustrating seeing responses and yet all it is are subjective cosmetic changes. I relish JGH's input and insight as there was true value from his input. – user9491577 Sep 11 '18 at 15:46
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.

  • First off, JGH thanks for a response. So frustrating with all the code beautifiers lurking about. When I issue a Show search_path i reveal: [temporary, production, public], so public is actually in my search path, The ST functions can only be installed in one schema in a database, We have three schema in our db as you can see above. I will try setting the search path in the function as you mention. – user9491577 Sep 11 '18 at 13:50
  • I don't know DevArt. Some ways of accessing the DB will add extra security, so even if the user has the proper search path, the function might still not be able to access them (Foreign Data Wrapper is a known one) – JGH Sep 11 '18 at 13:53
  • 1
    If the search path is set properly, you shouldn't have to specify public for the ST_ functions, regardless of where you are calling them. (indeed, it is a bad practice to specify public.st_... as it just make the function fails in internal postgis function rather that at the source) – JGH Sep 11 '18 at 13:55
  • JGH has nailed it completely. It is an ugly hack to have to put the schema name in front of a function. – John Powell Sep 11 '18 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.