[Mapserver-users] create postgis layer with mapscript gets invalid sql

[Twan Kogels]

Hello Steven,

Wow, it's a strange solution, but it works. I just lowered the case of 
"FROM" to "from" and i got another error. But lucky this one was easy:
============
MapServer Error in prep_DB(): Error executing POSTGIS DECLARE (the actual 
query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
pc6::text,asbinary(force_collection(force_2d(pc_geom)),'NDR'),OID::text 
from pc6 WHERE (pc6=) and (pc_geom && setSRID( 'BOX3D(160744 382852,161194 
383302)'::BOX3D,find_srid('','pc6','pc_geom') ))'

Postgresql reports the error 'ERROR: parser: parse error at or near ")" at 
character 135 '
============

It appears that the filter needs to be put between "(" and ")", after doing 
this my query works and the points show perfect on the map.

For the archive here's my working mapscript code:
============
                         $lo=ms_newLayerObj($mymap);
                         $lo->set("connectiontype", MS_POSTGIS);
                         $lo->set("connection", "user=postgres dbname=pc");
                         $lo->set("data", "pc_geom from pc6");

                         $filter="(pc6='$postcode')";
                         if($filter!="") $lo->setFilter($filter);

                         $lo->set("name", "postcode_layer");
                         $lo->set("status", MS_ON);
                         $lo->set("type", MS_LAYER_POINT);

                         $lo->setMetaData("DESCRIPTION", "pc6");
                         $lo->setMetaData("RESULT_FIELDS", "pc6");

                         $co=ms_newClassObj($lo);
                         $co->set("name", "mysym");
                         $temp=$mymap->getSymbolByName('star');
                         $co->set("symbol", $temp);

                         $lo->set("classitem", "pc6");
                         $lo->set("labelitem", "pc6");
                         $co->label->set("color", $mymap->addColor(0,0,0));
                         $co->label->set("position", MS_AUTO);
                         $co->label->set("partials", 0);
                         $co->label->set("size", MS_TINY);
                         $co->set("template", "ttt_query.html");
============

Best regards,
Twan Kogels

At 15:26 19-6-2003 -0700, you wrote:
>Twan:
>
>Not too long ago, I had a very similar problem with the MapServer CGI
>(same version as you: 3.6.5) and PostGIS layers. By sheer dumb luck, I
>discovered that the 'from' keyword in the layer's 'data' string had to
>be all lower case. That is, I found that by using 'from' (instead of
>'FROM') in my layer's 'data' string, the problem went away. Caveat: My
>'data' string was simpler than yours (mine didn't have any USING
>clauses), so you might have to experiment further to get it to work.
>
>Hope this helps,
>-SM
>--
>============================================
>Steven Monai
>Database & Application Developer
>Spatial Mapping Ltd.
>484 2nd Avenue
>Prince George, BC, Canada
>V2L 2Z7
>
>http://www.spatialmapping.com
>mailto:stevem at spatialmapping.com
>phone: 250 564 1928
>fax: 250 564 0751
>============================================
>
>
>Twan Kogels wrote:
> > Date: Thu, 19 Jun 2003 15:11:42 +0200
> > To: mapserver-users at lists.gis.umn.edu
> > From: Twan Kogels <twan at twansoft.com>
> > Subject: [Mapserver-users] create postgis layer with
> > mapscript gets invalid sql
> >
> > Hello,
> >
> > I'm trying to create a postgis layer with mapscript (php)
> >
> > ==========
> >                       $lo=ms_newLayerObj($mymap);
> >                       $lo->set("connectiontype", MS_POSTGIS);
> >                       $lo->set("connection", "user=postgres
> > dbname=pc");
> >                       $lo->set("data", "pc_geom FROM pc6
> > USING UNIQUE oid USING SRID=-1");
> >
> >                       $filter="pc6='$postcode'";
> >                       if($filter!="") $lo->setFilter($filter);
> >
> >                       $lo->set("name", "postcode_layer");
> >                       $lo->set("status", MS_ON);
> >                       $lo->set("type", MS_LAYER_POINT);
> >
> >                       $lo->setMetaData("DESCRIPTION", "pc6");
> >                       $lo->setMetaData("RESULT_FIELDS", "pc6");
> >
> >                       $co=ms_newClassObj($lo);
> >                       $co->set("name", "mysym");
> >                       $temp=$mymap->getSymbolByName('star');
> >                       $co->set("symbol", $temp);
> >
> >                       $lo->set("classitem", "pc6");
> >                       $lo->set("labelitem", "pc6");
> >                       $co->label->set("color",
> > $mymap->addColor(0,0,0));
> >                       $co->label->set("position", MS_AUTO);
> >                       $co->label->set("partials", 0);
> >                       $co->label->set("size", MS_TINY);
> >                       $co->set("template", "ttt_query.html");
> > ==============
> >
> > But when i call draw() i get a strange error:
> > ==============
> > Warning: MapServer Error in prep_DB(): Error executing
> > POSTGIS DECLARE (the
> > actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
> > pc6::text,asbinary(force_collection(force_2d()),'NDR'),oid::text from
> > Dy at XÐ WHERE (pc6=) and ( && setSRID( 'BOX3D(160744 382852,161194
> > 383302)'::BOX3D,-1) )'
> >
> > Postgresql reports the error 'ERROR: parser: parse error at
> > or near "" at
> > character 115 '
> >
> > More Help:
> > ..
> > ==============
> >
> > I suspect there's a problem with the query which postgis
> > uses, it contains
> > a string which postgresql does not like and ofcourse complains about:
> > Dy at XÐ, this is causing the trouble. Also strange is the
> > "WHERE (pc6=)
> > and" part, this should be "WHERE (pc6='a zipcode') and".
> >
> > I have checked the database multiple times, i've created the
> > spatial index
> > on pc_geom and a index on oid.
> >
> > I'm using mapserver:
> > MapServer version 3.6.5 OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
> > SUPPORTS=PROJ
> > SUPPORTS=TTF SUPPORTS=WMS_SERVER INPUT=TIFF INPUT=EPPL7 INPUT=JPEG
> > INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
> >
> > and postgis 0.7.5 with postgresql 7.3.2.
> >
> > Queries like:
> > ==============
> > select X(pc_geom) AS geox, Y(pc_geom) AS geoy from pc6 where
> > pc6='5611HL';
> > ==============
> > just run fine.
> >
> > Can somebody offer me a helping hand?
> >
> > Best regards,
> > Twan Kogels
> >
>
>
>_______________________________________________
>Mapserver-users mailing list
>Mapserver-users at lists.gis.umn.edu
>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users