PostGIS: Function for Selecting Points in a Polygon

Question

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, but as a 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 using PostgreSQL 8.4.13 and PostGIS 1.5.8.

Guessing... should it be WHERE ST_Contains(ST_GeomFromText('POLYGON(('+polystring+'))', 4326), point_geom); ? — AjV Jsy
That's not it unfortunately, I'm getting ERROR: operator does not exist: unknown + text — alreit

Chris Travers Chris Travers · Accepted Answer · 2013-05-01T15:17:33

As you already know this is a simple fix. I am writing to explain the fix.

Your function is:

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;

The troublesome part is:

ST_GeomFromText('POLYGON((polystring))', 4326)

What this is telling PostgreSQL to do is take the string Polygon((polystring)) and pass it in to the first argument of the ST_GeomFromText function. This is of course not what you mean. You want to construct the polygon using the value of polystring.

To correct this simply concatenate the string:

ST_GeomFromText('POLYGON((' || polystring || '))', 4326)

Then PostgreSQL will be happy.

PostGIS: Function for Selecting Points in a Polygon

1 Answers