[postgis-users] trying to implement a large (gnarly) SQL statement within a Map f ile

[Menke, Kurt]

Hi,
I am developing a PHP client with MapServ 3.6 serving out data stored in
PostGIS.  I have tabular and spatial census data in the same database in
PostgreSQL 7.3.  I want to join the tabular data to the census tract feature
class but cannot figure out why my statement is not working.  Below is the
data statement in my Map file and the error I receive.   
 
Map File data statement
DATA "the_geom FROM 
                      (SELECT 
                      sum(nm00001.P001001) as total_pop, 
                      nmgeo.COUNTY, 
                      nmgeo.SUMLEV, 
                      nmgeo.TRACT, 
                      nmtract00.the_geom, 
                      nmtract00.gid 
                      
                  FROM 
                      nm00001 
                      INNER JOIN nmgeo ON (nm00001.LOGRECNO =
nmgeo.LOGRECNO) 
                      INNER JOIN nmtract00 ON (nmgeo.TRACT =
nmtract00.tract) 
                  WHERE 
                    (nmgeo.COUNTY = '035') AND 
                    (nmgeo.SUMLEV = '158') 
                    group by
nmgeo.TRACT,nmgeo.COUNTY,nmgeo.SUMLEV,nmgeo.TRACT, 
                    nmtract00.the_geom,nmtract00.gid) 
                    
                    as foo using unique gid using SRID=-1" 
 
 
 
Client error message

Warning: MapServer Error in prep_DB(): Error executing POSTGIS DECLARE (the
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(oeh@)),'NDR'),gid::text from `YB7ÿ¿%
WHERE oeh@ && setSRID('BOX3D(350386.808620038
3527974.4288665,529613.191379962 3707025.5711335)'::BOX3D, -1 )' 

Postgresql reports the error 'ERROR: parser: parse error at or near "" at
character 80 '

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.php 

Mappostgis.c - version of Nov 15/2002. in /var/www/cgi-bin/bber/bber.php on
line 421

Fatal error: Call to a member function on a non-object in
/var/www/cgi-bin/bber/bber.php on line 422
 
Thanks Kurt
 
~~~~~~~~~~~~~~~~~~~~~~~~~
Kurt A. Menke
GIS Analyst/Programmer
 
Mailing Address  
Earth Data Analysis Center 
MSC01 1110 
1 University of New Mexico 
Albuquerque, New Mexico  87131-0001 
 
Street Address
University of New Mexico
Bandelier West, Room 123
Albuquerque, NM 87131
 
Phone:  (505) 277-3622 (ext. 239) 
FAX:  (505) 277-3614 
email:  kmenke at edac.unm.edu
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20031009/b89503df/attachment.html>