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

[Steven Monai]

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@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@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
>