[postgis-users] Querying MapServer PostGIS layer

[Dave Stone]

Hi Paul, thanks for your input. We're running MapServer 4.8.3 and 
PostgreSQL 8.0.7; PostGIS seems to be 1.0.6.

I've checked the error logs and tried to see what messages appear as a 
result of 'atomic' actions at the user interface. I have noted some 
'interesting' features (marked below with asterisks), but I don't really 
understand how to tie together the input by the end-user and the content 
of the log entries. Don't know if you can help?

Cheers,

Dave

Context: the .map file has 3 layers, but only 1 (saint_locations) which 
refers to the PostgreSQL database ('stdraft' ).

Experiment: with the browser displaying the 2 non-PostGIS layers 
(boundaries and labels), and the PostGIS layer ('locations' -- red 
dots), the user chooses from a select list 'mode=query' and clicks one 
of the dots. This should load the query template result.html and display 
a selection of attributes for the selected location.

Result: in fact he sees a blank page titled 'MapServer Message' and no 
message.

Note that if the 'dot layer' is in a shapefile rather than a PostGIS 
database, the action works as expected.

[the browser address displayed with the blank page is:]
http://drayton.ucs.ed.ac.uk/cgi-bin/mapserv?imgxy=300.0+300.0&imgext=211720.600000+606214.306667+382058.600000+776552.306667&map=%2Fvar%2Fwww%2Fhtml%2Fsaints%2Fdata%2Fparish%2Fuig.map&savequery=true&map_web_template=result.html&img.x=407&img.y=382&zoomdir=0&zoomsize=4&layer=parish_labels&layer=parish&layer=saint_locations&mode=query

*** Note the mode is query

The error log entries for the 'click-the-dot' interaction are as follows:

[Error log entries:]
[Mon Apr 17 11:23:53 2006] [error] [client 129.215.70.111] [Mon Apr 17 
11:23:53 2006].459
924 msPOSTGISLayerOpen called datastatement: geom from loccoords using 
unique locationref
  using srid=27700, referer: 
http://drayton.ucs.ed.ac.uk/cgi-bin/mapserv?imgxy=300.0+300.0
&imgext=211720.600000+606214.306667+382058.600000+776552.306667&map=%2Fvar%2Fwww%2Fhtml%2
Fsaints%2Fdata%2Fparish%2Fuig.map&savequery=true&map_web_template=result.html&zoomdir=0&z
oomsize=4&layer=parish_labels&layer=parish&layer=saint_locations&mode=browse

*** Note mode=browse; is this because that was the calling page's mode? 
The data statement is exactly what layer saint_locations has in the .map 
file.

*** For brevity, I've chopped the referer parameter and the 
time/date/Id/etc. from subsequent messages: they're all the same.

MSPOSTGISLayerOpen -- shared connection not available., referer:...

msConnPoolRegister(saint_locations,user=erdb13 
dbname=stdraft,0x9127438), referer:

msPOSTGISLayerFreeItemInfo called, referer:

msPOSTGISLayerWhichShapes called, referer:

msPOSTGISLayerParseData: unique column = locationref, srid='27700', 
geom_column_name = geom, table_name=loccoords, referer:

query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(geom)),'NDR'),locationref::text from 
loccoords WHERE geom && setSRID('BOX3D(320169.126666667 
661006.363333667,334363.96 675201.196667)'::BOX3D, 27700 ), referer:

msPOSTGISLayerFreeItemInfo called, referer:

msPOSTGISLayerClose datastatement: geom from loccoords using unique 
locationref using srid=27700, referer:

msPOSTGISLayerClose -- closing query_result, referer:

msConnPoolRelease(saint_locations,user=erdb13 dbname=stdraft,0x9127438), 
referer:

msConnPoolClose(user=erdb13 dbname=stdraft,0x9127438), referer:

*** This next bit looks a repeat of the stuff above: why is it doing that?

msPOSTGISLayerOpen called datastatement: geom from loccoords using 
unique locationref using srid=27700, referer:

MSPOSTGISLayerOpen -- shared connection not available., referer:

msConnPoolRegister(saint_locations,user=erdb13 
dbname=stdraft,0x9193528), referer:

msPOSTGISLayerFreeItemInfo called, referer:

msPOSTGISLayerWhichShapes called, referer:

msPOSTGISLayerParseData: unique column = locationref, srid='27700', 
geom_column_name = geom, table_name=loccoords, referer:

query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(geom)),'NDR'),locationref::text from 
loccoords WHERE geom && setSRID('BOX3D(211720.6 606214.306667,382058.6 
776552.306667)'::BOX3D, 27700 ), referer:

msPOSTGISLayerFreeItemInfo called, referer:

msPOSTGISLayerClose datastatement: geom from loccoords using unique 
locationref using srid=27700, referer:

msPOSTGISLayerClose -- closing query_result, referer:

msConnPoolRelease(saint_locations,user=erdb13 dbname=stdraft,0x9193528), 
referer:

msConnPoolClose(user=erdb13 dbname=stdraft,0x9193528), referer:

*** the third time around -- this IS significantly different though!

msPOSTGISLayerOpen called datastatement: geom from loccoords using 
unique locationref using srid=27700, referer:

MSPOSTGISLayerOpen -- shared connection not available., referer:

msConnPoolRegister(saint_locations,user=erdb13 
dbname=stdraft,0x9193528), referer:

msPOSTGISLayerFreeItemInfo called, referer:

*** haven't seen the next line before: what does record=0 signify?

msPOSTGISLayerGetShape called for record = 0, referer:

msPOSTGISLayerParseData: unique column = locationref, srid='27700', 
geom_column_name = geom, table_name=loccoords, referer:

*** I suspect this is the killer line -- locationrefs should be like 
'LO/EW/0001' not 0!!

msPOSTGISLayerGetShape: DECLARE mycursor2 BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(geom)),'NDR') from loccoords WHERE 
locationref = 0 , referer:

msPOSTGISLayerClose datastatement: geom from loccoords using unique 
locationref using srid=27700, referer:

msPOSTGISLayerClose -- query_result is NULL, referer:

msConnPoolRelease(saint_locations,user=erdb13 dbname=stdraft,0x9193528), 
referer:

msConnPoolClose(user=erdb13 dbname=stdraft,0x9193528), referer:


Paul Ramsey wrote:
> Dave,
> 
> Turn on statement logging in your PostgreSQL installation and see what 
> SQL mapserver is sending to the back-end.  That may clarify where/why 
> things are failing a little.  Also, what Mapserver version are you using?
> 
> P