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
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
