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 $$
SELECT * FROM locations
WHERE ST_Contains(ST_GeomFromText('POLYGON((polystring))', 4326), point_geom);
$$ 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!

2 Answers 2


Let's start off with what a regular query should look like:

FROM locations
              '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))',

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

CREATE OR REPLACE FUNCTION spGeoPoly(polystring text) RETURNS setof points
  FROM locations
    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:

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;

You need the PostgreSQL geometry functions.

Specifically: <@ means Contained in or on?, thus calling: SELECT point '(1,1)' <@ circle '((0,0),2)' returns true.

  • This question asks about PostGIS' geometry type, and not PostgreSQL geometry types
    – Mike T
    Commented Mar 14, 2014 at 4:05
  • @MikeT Yes, but PostGIS is built on top of the PostgreSQL types. I use these built-in types and functions myself in several applications, and have found that using the built-in types is more convenient than the string parsing approach show here. I'm not familiar enough with the history of PostGIS development to say if/when/how the types defined for each become interoperable.
    – brichins
    Commented Mar 14, 2014 at 18:21
  • @brichins, No PostGIS types has nothing to do with the PosgreSQL types. I don't even think there is some simåle cast between them. You have to use the PostGIS types to use the PostGIS functions. Commented May 18, 2014 at 6:18
  • @NicklasAvén As I said, I don't really know whether the types are related (apparently not). I am still curious whether the native geometry operators work with PostGIS though - the list of PostGIS operators appears to define complementary operators without overloading existing ones. I do not have an existing PostGIS installation to experiment with, but would be curious to know whether the native operators do in fact work on PostGIS types, regardless of whether the types are related.
    – brichins
    Commented May 20, 2014 at 17:20
  • @brichins no the postgresql operators will not work on postgis type but as you say PostGIS has it's own that in many cases mean the same. But not alwayas. The last operator in your link also change meaning between PostGIS 1.4 and 1.5 if I recall right to mean only bbox comparasion. Commented May 20, 2014 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.