[postgis-users] Re: [Mapserver-users] Mapserver query click returns PostGIS query error

[Jonathan W. Lowe]

Rich,

Thanks for your follow-up.  I'm running Postgresql 7.3 on Redhat Linux. 
Maybe the following response from David Blasby is the answer to our
riddle:

"There's a problem with the postgis-mapserver connector with postgresql
7.3.  I know about it, I'm just really busy and havent had time to fix
it.  Hopefully within the next week or so."

...or the following from Stefan Schneberger:

"have look at the postgis mailing-list, there is a patch for mapserver
3.6.3. You will find it here:
http://postgis.refractions.net/pipermail/postgis-users/2002-December/001862.
html"

Sounds like a solution is on the way.

Best,
Jonathan

On Thu, 2003-04-03 at 20:16, Richard Greenwood wrote:
> What's your postgresql running on - linux or cygwin? I am having a similar 
> problem. Mine runs correctly on linux but not on cygwin. There are a few 
> other minor differences between my two setups, so I can't say conclusively 
> that's it is related to OS.
> 
> I posted my issue to the postgis list, and have pasted in below, but thus 
> far I haven't made any headway.
> 
> === my post to postgis-users at postgis.refractions.net ===
> 
> I have a simple mapserver/postgis running with mapserver on windows and 
> postgresql on linux. All the recent discussion of postgis on cygwin 
> motivated me to give it a try.
> 
> I can map from cygwin-postgis, but I can not retrieve attribute data with 
> the mapserver query or nquery modes. I get the following error:
> 
> msPOSTGISLayerGetShape(): Query error. Error executing POSTGIS SQL 
> statement (in FETCH ALL): EXPLAIN VERBOSE SELECT * FROM join_region - Error 
> with POSTGIS data variable. You specified ''.
> 
> My linux setup, which works correctly, is postgresql 7.2.3, postgis 0.7.3
> My cygwiwn setup, which fails, is postgresql 7.3.2, postgis 0.7.4
> 
> Everything else is basically the same.
> 
> =======
> 
> Best regards,
> Rich
> 
> At 06:06 PM 4/3/2003 -0800, you wrote:
> 
> >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
> >_______________________________________________
> >Mapserver-users mailing list
> >Mapserver-users at lists.gis.umn.edu
> >http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 
> Richard W. Greenwood, PLS
> (307) 733-0203
> Rich at GreenwoodMap.com
> www.GreenwoodMap.com