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?
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 :-)