[postgis-users] Querying MapServer PostGIS layer

Thu Apr 13 19:37:44 PDT 2006

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

On Apr 13, 2006, at 8:34 AM, Dave Stone wrote:

> Thanks for that Steve. Would that it were that simple! I fell into  
> that trap when converting the data-source from PostGIS to  
> shapefile; I left the columns in lower-case. But the query template  
> did display -- it just couldn't fill in table values because it  
> didn't interpret the column-names as such. With the PostGIS layer I  
> don't even get boiler-plate text showing on the page. The  
> (abridged) template is:
>
> <!-- query template -->
> <html>
> <head>
>   <title>MapServer - ItemQuery</title>
> </head>
>
> <body bgcolor="#ffffff">
>
> <font size="+2" face="arial,helvetica"><b>Case 1: Simple ItemQuery  
> With QueryMap</b></font>
>
> <p>
>
> This is just a simple dump of a few attributes for an individual  
> location
> in the test suites parishes layer:
>
> <p>
>
> <b>Place Name:</b> [placename]<br>
> <b>Qualifying Name:</b> [qualifyingname]<br>
> <b>Location Type:</b> [locationtype]<br>
> <b>Grid Reference:</b> [locationgridref]<br>
> <b>Religious Order:</b> [religiousorder]<br>
>
> <p>
> - - - - - - - etc.
>
> Is there a limit to the length of column names? I noticed that the  
> above were truncated to 10 chars. by pgsql2shp. Any sources for  
> debugging info? I've looked in the error logs, but they only seem  
> to be recording parts of the app which (to my mind) are working.
>
> Thanks again,
>
> Dave
>
>
> Stephen Woodbridge wrote:
>> Dave,
>> Shapefile attribute columns are all UPPERCASE and Postgres/postgis  
>> attribute columns are all lowercase so you need to make sure that  
>> the reference to the attribute columns in your template match the  
>> case expected by the data source.
>> -Steve W
>> Dave Stone wrote:
>>> Please excuse if this post has been asked before; I'm new to the  
>>> list and finding it difficult to locate all the hidden gems of  
>>> wisdom!
>>>
>>> I'm having a problem with querying a MapServer layer consisting  
>>> of points stored in a PostGIS table. The plan is simple enough:  
>>> I'm displaying the dots together with a boundary and an  
>>> annotation layer, and I want to click on a dot and have a page  
>>> come up showing non-spatial attributes from the PostGIS record  
>>> for that location.
>>>
>>> What happens when I click a dot is I get a completely blank page  
>>> titled 'MapServer Message' (but no message). The relevant bit of  
>>> the MapServer .map file is:
>>>
>>>   LAYER # query layer using the loccoords PostgreSQL table
>>>     CONNECTIONTYPE postgis
>>>     NAME          saint_locations
>>>     CONNECTION    "user=erdb13 dbname=stdraft"
>>>     DATA          "geom from loccoords using unique locationref  
>>> using srid=27700"
>>>     STATUS        ON
>>>     DEBUG         ON
>>>     TYPE          POINT
>>>     TOLERANCE     25
>>>     CLASS
>>>       NAME        "Locations"
>>>       MINSCALE    2000000
>>>       TEMPLATE    "result.html"
>>>       DEBUG       ON
>>>       STYLE
>>>         SYMBOL      "circle"
>>>         SIZE        5
>>>         COLOR       255 0 0
>>>         OUTLINECOLOR 0 0 0
>>>       END
>>>     END
>>>
>>> This whole idea is based on an example in the MapServer tutorial  
>>> (http://hypnos.cbs.umn.edu/tutorial/section3.html, choose Ex. 
>>> 3.1). The example works fine, so why doesn't my version?  The  
>>> answer seems to be that the layer being queried in the example is  
>>> a shapefile whereas mine is in PostGIS.  To test this suspicion,  
>>> I amended the layer definition in the .map file as follows:
>>>
>>>   LAYER # query layer using the loccoords PostgreSQL table
>>> #   CONNECTIONTYPE postgis
>>>     NAME          saint_locations
>>> #   CONNECTION    "user=erdb13 dbname=stdraft"
>>>     DATA          "/var/www/html/saints/data/parish/locs"
>>>     STATUS        ON
>>>
>>> 'locs' is a shapefile produced by dumping (with pgsql2shp) the  
>>> 'loccoords' table in the first DATA statement above.
>>>
>>> With only this trivial change, the query template 'result.html'  
>>> works as intended. But since I want to use the PostGIS  
>>> functionality, I need to find out what's wrong with the first  
>>> LAYER spec. above.
>>>
>>> Any suggestions most gratefully received,
>>>
>>> Dave
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users