Hi Mark. I try...
1st one: add "using SRID=4326" to the layer query; 2nd one: try to send - with some javascript - the entire query as variable, not only the subquery, i.e. : LAYER ... DATA %sql% ... END on the mapfile and <script language='javascript'> function sendquery(subquery){ // code to complete the query string document.forms['mainform'].sql.value = "the_geom from (" + subquery + ") as myquery using unique gid using SRID=4326"; document.forms['mainform'].submit(); } </script> <form name='mainform' ... > <input type='hidden' name='sql' value='[sql]'> <textarea name= 'subquery' rows=3 cols=100></textarea> <input type=button value='send' onclick="javascript:sendquery(document.forms['mainquery'].subquery.value);" </form> on the html template. let us know, ciao ....................................................pg -- Pietro Giannini Bytewise srl - Area GIS 41°50'38.58"N 12°29'13.39"E On Gio, Ottobre 9, 2008 12:00, mark balman wrote: > 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 > _______________________________________________ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users