[postgis-users] building a polygon with variables

[Stephen Woodbridge]

On 5/8/2012 10:26 AM, Gold, Jack L (US SSA) wrote:
> I’m not even sure how to express this properly, but I’ll try. I’m trying
> to build a query which will select positions within a specified polygon.
> I have the query:
>
> SELECT * FROM positions WHERE ST_Within(geometry(positions.location),
> ST_GeomFromText(‘POLYGON((l b, r b, r t, l t, l b))’::text, 4326));
>
> I want to be able to replace l, b, r, and t with variable values in a
> plpgsql function like so:
>
> $BODY$
>
> DECLARE
>
> l integer;
>
> r integer;
>
> b integer;
>
> t integer;
>
> BEGIN
>
> SELECT pli_config.filter_left_longitude INTO l FROM pli_config;
>
> SELECT pli_config.filter_right_longitude INTO r FROM pli_config;
>
> SELECT pli_config.filter_bottom_latitude INTO b FROM pli_config;
>
> SELECT pli_config.filter_top_latitude INTO t FROM pli_config;
>
> SELECT * FROM positions
>
> WHERE st_within(geometry(positions.location),
> st_geomfromtext('POLYGON((l b,r b,r t,l t,l b))'::text, 4326));

'POLYGON(('||l||' '||b||','||r||' '||b||','....

> END;
>
> $BODY$
>
> The problem I have is I don’t know the proper syntax to replace the
> variables in the single-quoted statement or even if it is possible. Any
> ideas?
>
> --Jack Gold
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users