[postgis-users] dynamic sql

[mark balman]

Hi All

I am trying to create a dynamic sql query that a user can enter latitude
and longitude coordinates together with a specified distance. So far I
have created the following query (not sure if correct but it does work
and have also tried st_distance query)

select * from azev2_1 where distance_spheroid(GeomFromText('Point(-70
20)',4326),the_geom,'SPHEROID["WGS 84",6378137,298.257223563]') <
250000;

What I am trying to achieve is to get this to work using the Mapserver
Demo application (basic version) as a starting point (running under
MS4W. So far this
following Layer definition works:

LAYER
 CONNECTIONTYPE postgis
 NAME "test"
 CONNECTION "user=mark password=mark dbname=ibat_proto host=localhost"
 DATA "the_geom FROM (select * from kbapoly where
st_distance(the_geom,GeomFromText('Point(1 0)',4326)) <6) AS myquery
using unique gid"
 STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/azes.html"
 CLASS
 OUTLINECOLOR 0 0 0
 COLOR  128 128 128
 END
 HEADER "templates/azes_header.html"
 FOOTER "templates/azes_footer.html"
END


What I would like to do is to have the sql statement modified by the
user and passed back with the users parameters (Lat, Long and distance)
I have read and googled a lot of the documentation especially the
PostGIS workshop docs written by Paul Ramsey but alas I am "stuck". I
have tried the following Layer definition:

LAYER
 CONNECTIONTYPE postgis
 NAME "test"
 CONNECTION "user=mark password=mark dbname=ibat_proto host=localhost"
 DATA "the_geom FROM (%sql%) AS myquery using unique gid"
 STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/ibas.html"
 CLASS
 OUTLINECOLOR 0 0 0
 COLOR  128 128 128
 END
 HEADER "templates/ibas_header.html"
 FOOTER "templates/ibas_footer.html"
END

I am a little unsure of where to place the following (so far it is in
the basic.html)

<b>Query: </b><br>
<textarea name="sql" rows=3 cols=50>[sql]</textarea> <p> <hr>

This displays fine but not too sure what to do next to get the query to
work.. In the postgresql log file:

2008-10-07 10:11:46 STATEMENT:  DECLARE mycursor BINARY CURSOR FOR
SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from (%sql%) AS myquery WHERE the_geom &&
setSRID('BOX3D(-179.398998165275 -120.500834889816,180.601001834725
119.298831220367)'::BOX3D, find_srid('','(%sql%) AS
myquery','the_geom') )

2008-10-07 10:11:46 ERROR:  cursor "mycursor" does not exist

2008-10-07 10:11:46 STATEMENT:  CLOSE mycursor

Any assistance would be most welcome

Many thanks

Mark