[postgis-users] GeomFromText in a user function.

[Charlie]

Mike Toews <mwtoews <at> gmail.com> writes:

> 
> On 27 April 2011 05:46, Charles E. Deaton <CDeaton <at> corp.realcomp.com> 
wrote:
> > I have been trying to create a user function that I can call as needed by
> > passing in bbox coordinates.
> 
> Are you trying to select items in a box-shaped geometry? There are a
> few better ideas to construct a simple box polygon that use fewer than
> eight parameters:
> 


After determining that our mail server had blacklisted the most of the domains 
associated with this group and the mail service I will try my personal email.

I did manage to figure out the use of $$ and it at least allowed me to create 
the function, I will attach it to the bottom. The only issue I have now (that 
I am aware of) is a parseing error. I am only trying to pull one value from a 
record where the geometry column (point made of longitude and latitude ) fall 
within a polygon that I pass in. The value that I am trying to retrieve is the 
latitude (to make is simple for now) which is stored in a numeric(12,6) 
column. What am I missing?

CREATE OR REPLACE FUNCTION fn_Poly
(numeric,numeric,numeric,numeric,numeric,numeric,numeric,numeric)
RETURNS table (LATITUDE numeric)
AS $$
BEGIN
RETURN QUERY SELECT "LATITUDE"
FROM "Residential" r
WHERE (ST_Contains(GeomFromText('POLYGON(($1 $2,$3 $4,$5 $6,$7 $8,$1 $2))', 
3078),r.geom)) = 't';
END;
$$ LANGUAGE PLPGSQL;

SELECT fn_Poly(-83.4247255325317,42.5838643754422,-
83.4247255325317,42.6047787681066,-83.3897066116333,42.6047787681066,-
83.3897066116333,42.5838643754422);

ERROR:  parse error - invalid geometry
HINT:  "POLYGON((" <-- parse error at position 9 within geometry
CONTEXT:  SQL function "geomfromtext" statement 1
SQL statement " SELECT "LATITUDE" FROM "Residential" r WHERE (ST_Contains
(GeomFromText('POLYGON(($1 $2,$3 $4,$5 $6,$7 $8,$1 $2))', 3078),r.geom)) = 't'"
PL/pgSQL function "fn_poly" line 3 at RETURN QUERY

Charles.