[mapserver-users] dynamic sql mapserver

[Pietro Giannini]

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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>