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!