Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 2

You need the PostgreSQL geometry functions.

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

share|improve this answer
    
This question asks about PostGIS' geometry type, and not PostgreSQL geometry types –  Mike T Mar 14 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 Mar 14 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. –  Nicklas Avén May 18 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 May 20 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. –  Nicklas Avén May 20 at 18:35
show 1 more comment

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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