[postgis-users] Querying MapServer PostGIS layer

[David Bitner]

Another option when just querying PostGIS like this that for me works
much simpler is to just take out MapServer and directly make a spatial
query to PostGIS by creating a service with you favorite scripting
language.

Just create a script that takes an x and y and creates a query to
PostGIS ordering your data by distance with a limit of 1.  You can
then use AJAX techniques or whatever to grab the information parsed
out however you want.

On 4/20/06, Dave Stone <D.Stone at ed.ac.uk> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>