[mapserver-users] dynamic sql mapserver

[mark balman]

Hi all

I am trying to figure out the way to create dynamic sql using
mapserver (using the basic demo application under MS4W) and
postgres/postgis. I have created various queries that work ok as
follows:

LAYER
 CONNECTIONTYPE postgis
 NAME "qdistance"
 CONNECTION "user="" password="" dbname="test" host=localhost"
DATA "the_geom from (select * from kbapoly where
st_distance(the_geom,GeomFromText('Point(1 0)',4326)) <6) as myquery
using SRID=4326 using unique gid"
 STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/kbas.html"
  CLASS
  COLOR 255 0 0

What I would like to be able to do is pass user defined variables e.g.
latitude, longitude and distance somehow. I have read various
mapserver/postgis workshop docs by Paul Ramsey and others but cannot
get anything to work. I have defined the Layer thus:

LAYER
 CONNECTIONTYPE postgis
 NAME "qdistance"
 CONNECTION "user="" password="" dbname="test" host=localhost"
 DATA "the_geom from (%sql%) as myquery using unique gid" STATUS OFF
 TYPE POLYGON
 TEMPLATE "templates/kbas.html"
  CLASS
  COLOR 255 0 0

Within the basic.html page I have added the following:

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

When I try adding the sql statement to the text box

select * from kbapoly where st_distance(the_geom,GeomFromText('Point(1
0)',4326)) <6

I keep getting the following message:

msDrawMap(): Image handling error. Failed to draw layer named
'qdistance'. prepare_database(): Query error. Error executing POSTGIS
DECLARE (the actual query) 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.699499165275 -120.200333889816,180.300500834725
119.599332220367)'::BOX3D, find_srid('','(%sql%) as
myquery','the_geom') )' Postgresql reports the error as 'ERROR: syntax
error at or near "%" LINE 1:
...ection(force_2d(the_geom)),'NDR'),gid::text from (%sql%) as ... ^ '
More Help: Error with POSTGIS data variable. You specified 'check your
.map file'. Standard ways of specifiying are : (1) 'geometry_column
from geometry_table' (2) 'geometry_column from (sub query) as foo
using unique column name using SRID=srid#' Make sure you put in the
'using unique column name' and 'using SRID=#' clauses in. For more
help, please see http://postgis.refractions.net/documentation/
Mappostgis.c - version of Jan 23/2004.

Can anyone provide me with some pointers?

Many thanks

Mark