[Mapserver-users] Mapserver query click returns PostGIS query error

[Jonathan W. Lowe]

Hello List:

When serving maps with Mapserver 3.6.3 on RedHat Linux v.8 and pulling data 
from PosgreSQL v.7.3.1 with PostGIS v.0.7.4 using the following map file 
parameters...

LAYER
 NAME "parcels"
 CONNECTION "user=xxx password=xxx dbname=xxx host=xxx port=5432"
 DATA "the_geom from pcl_b83m"
 TYPE POLYGON
 HEADER "parcel_header.html"
 FOOTER "parcel_footer.html"
 CLASS # parcels class
  NAME "property boundaries"
  COLOR 245 245 245
  TEMPLATE "parcel_query.html"
 END
END

..the layer draws perfectly, but when queried (by clicking on the map in query 
mode), the following error returns:

  msPOSTGISLayerGetItems(): Query error. Error executing POSTGIS SQL statement: 
EXPLAIN VERBOSE SELECT * FROM pcl_b_83m

The SQL statement, "EXPLAIN VERBOSE SELECT * FROM pcl_b_83m" runs fine from the 
PostgreSQL SQL interpreter command line.

Both the mapserver and postgis archives had some good suggestions (e.g. use 
of '-1' as an SRID causes problems with some versions, upper case in the SQL 
statement causes some problems, etc.), but none were the solution I needed.

Any suggestions for a next step in debugging this?  I suspect some simple 
missing piece.

Potential clues (or additional debugging problems)...

The following two approaches to displaying the same data produce different 
results:

1)  DATA "the_geom from (select pcl_b_83m.the_geom as the_geom, pcl_b_83m.oid 
as oid, address.name as aname from pcl_b_83m left join address on 
pcl_b_83m.addrid = address.id) as pcl_adr using unique oid using srid=26943"

  ...this one (1 above) successfully displays the polygons.

2a)  CREATE VIEW pcl_view as select pcl_b_83m.the_geom as the_geom, 
pcl_b_83m.oid as oid, address.name as aname from pcl_b_83m left join address on 
pcl_b_83m.addrid = address.id

2b)  DATA "the_geom from pcl_view as pcl_adr using unique oid using srid=26943"

  ...these two (2a and 2b above) do not cause an error, but also do not display 
the data.

So, a view based on the same SQL as a temporary table fails.  (Not a 
showstopper, as one of the choices works, but I would prefer to use the view 
approach to take advantage of caching within PostgreSQL.)

This is included in case it sheds light on the primary question--why the query 
error?

SWAGs welcome.

Thank you!

-- 
Jonathan W. Lowe
email - jlowe at giswebsite.com
website - http://www.giswebsite.com