I've been banging on this for a few hours, but since I'm relatively new to PostgreSQL and PostGIS, I can't find the solution. I'm trying to create a stored procedure (function) which will return all locations whose specified point geometry is within the specified polygon.
Here is my PostgreSQL function:
CREATE OR REPLACE FUNCTION public.spGeoPoly(polystring text) RETURNS setof locations
AS $$
BEGIN
RETURN QUERY
SELECT * FROM locations
WHERE ST_Contains(ST_GeomFromText('POLYGON((polystring))', 4326), point_geom);
END;
$$ LANGUAGE plpgsql;
I've tested this with a hard-coded polystring, which works. However, when I call the function with an input string such as:
SELECT spGeoPoly('50.4 8.2,50.3 9.3,49.9 9.5,49.7 8.8,49.9 7.8,50.4 8.2');
I get an error:
ERROR: parse error - invalid geometry
HINT: "polygon((" <-- parse error at position 9 within geometry
I assume my input text is not getting transformed properly into the format necessary to create the polygon, since position 9 is the start of the polygon parameter string. How can I fix the text parameter? I'm sure there's something simple, but none of the examples I've found for GeomFromText use a parameter, they all use hard-coded values…
I'm using PostgreSQL 8.4.13 and PostGIS 1.5.8.
Edit: I knew it was something simple. Unknown to me, PostgreSQL uses '||' for text concat, so the query should be:
WHERE ST_Contains(ST_GeomFromText('POLYGON((' || polystring || '))', 4326), point_geom);
That makes it work!
Best Answer
Let's start off with what a regular query should look like:
If you want to make a purpose-made function to do a similar task:
The correction to your attempt was to correctly concatenate
polystring
to build the WKT required to make a geometry.And to use it to return all columns of
location
, it needs to be in theFROM
part:Or to get only one of the columns of
locations
(e.g.name
):