[GIS] Function for Selecting Points in a Polygon

point-in-polygonpostgispostgis-1.5postgresqlsql

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:

SELECT *
FROM locations
WHERE
  ST_Within(point_geom,
            ST_GeomFromText(
              'POLYGON((50.4 8.2,50.3 9.3,49.9 9.5,49.7 8.8,49.9 7.8,50.4 8.2))',
              4326));

If you want to make a purpose-made function to do a similar task:

CREATE OR REPLACE FUNCTION spGeoPoly(polystring text) RETURNS setof points
AS $$BEGIN
RETURN QUERY
  SELECT *
  FROM locations
  WHERE
    ST_Within(point_geom, ST_GeomFromText('POLYGON((' || polystring || '))', 4326));
END;$$ LANGUAGE plpgsql;

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 the FROM part:

SELECT *
FROM 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');

Or to get only one of the columns of locations (e.g. name):

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')).name;
Related Question