[postgis-users] msPOSTGISLayerParseData() and prep_DB() Error

[Anantha Prasad]

Greetings:
I searched the archives and could not find a suitable solution for my
problem: as belows. Any help would be greatly appreciated.
Thanks much.
Prasad
*****************************************************************
Anantha Prasad,
USDA Forest Service,
Northeastern Research Station
 aprasad/at/fs.fed.us
******************************************************************
Background:

Windows 2000 Prof. (Service Pack 3)
Cygwin 1.5.6-1
PSQL  7.4.1-2
PostGIS  0.8.0-1 (Windows Executable Version)
MapServer 4.0.1
Apache 1.3.29
Mappostgis.c - version of June 12/2003

PostgreSQL is working well. The sub-queries in the following DATA works
WITHOUT error when executed in PSQL. However, through PostGIS/MapServer CGI
combination, it reports errors as shown below.
NOTE: I have followed the instructions given in the paper "Developing
Applications with PostGIS (MapServer User Meeting) By Dave Blasby and Chris
Hodgson when converting shapefiles to PostGIS.


 1.  DATA "the_geom from uscnty100"

     Comment: Works without any problem

 2.  DATA "the_geom from
  (SELECT gid, the_geom FROM uscnty100 where ppt > 600)
   as foo  using srid=-1 using unique gid"

   Comment: Gives ERROR Message 2 (see below)

3.    DATA "the_geom from
        (SELECT  gid, map.the_geom,  data.ppt::INTEGER / data.tavg::REAL AS
pptavg
                 from ew20km as map, ew_pred as data
                 where ( map.ew20kmid = data.ew20kmid and data.elv_min >
500 )
         ) as foo using unique gid using srid=-1"

    Comment: Gives ERROR Message 3 (see below)


########################################
ERROR Message 2:

msDrawMap(): Image handling error. Failed to draw layer named
'CLIMATE_PostGIS'.
prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual query)
statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(x
/
x
/
)),'NDR'),gid::text from x
/
x
/
 WHERE
x
/
x
/
 && setSRID('BOX3D(-1943092 -1900894,1356908 1399106)'::BOX3D, -1 )'

Postgresql reports the error ''

Mappostgis.c - version of June 12/2003.
msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data
variable. Must contain 'geometry_column from table_name' or 'geom from
(subselect) as foo' (couldnt find ' from '). More help:

Error with POSTGIS data variable. You specified 'the_geom from (SELECT gid,
the_geom FROM uscnty100 where ppt > 600) as foo using srid=-1 using unique
gid'.
######################################

Error Message 3:

msDrawMap(): Image handling error. Failed to draw layer named
'CLIMATE_PostGIS'.
prep_DB(): 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 (SELECT
gid, map.the_geom, data.ppt::INTEGER / data.tavg::REAL AS pptavg from
ew20km as map, ew_pred as data where map.ew20kmid = data.ew20kmid and data.
) as foo WHERE the_geom && setSRID('BOX3D(-1943092 -1900894,1356908
1399106)'::BOX3D, -1 )'

Postgresql reports the error ''