Error on Itemnquery with PostGIS-layer

Tue Aug 21 12:12:40 EDT 2007

Hi,

just a guess: you could try 'name.name' instead of just 'name'
(tablename.columnname).

Regards,
Sebastian

HarryS wrote:
> How could I add columns to DATA statement ?
>
> I think my mistake is in the query-URL:
>
> this one works on Windows with shapefile-data but not on Linux with PostGIS
> (anything else works fine)
> http://myServer.com/cgi-bin/mapserv?&map=/home/harry/Mapfiles/MyMap.map&mode=itemnquery&qlayer=City-Database&qitem=name&qstring='London'
>
> changing the syntax to this (what should be the same than above)
> http://myServer.com/cgi-bin/mapserv?&map=/home/harry/Mapfiles/MyMap.map&mode=itemnquery&qlayer=City-Database&qitem=name&qstring=('[name]'='London')
>
> results in 
>
> msQueryByAttributes(): Search returned no results. No matching record(s)
> found. 
>
> at least no error but 'London' is definetly in the database and I tried with
> upper case/lower case,...
>
> The PostGIS-databasename is 'geonamesallppl' the tablename is 'name' and
> also the column-name is 'name'.
>
> Any ideas what I am doing wrong ??
>
> Thx, Harry
>
>
> This is my template-file (if it plays a role)
>
> <html>
>   <head><title>Sample Template</title></head>
>   <body>
>      <li> Name is [NAME]
>      <li> Population is [POPULATION]
>      <li> Code is [CODE]
> <tr>
>   <td>[lrn]</td>
>   <td>[NAME]</td>
> </tr>
>
>   </body>
> </html>
>
>
>
>
> Umberto Nicoletti wrote:
>   
>> Sorry, I meant the DATA statement.
>>
>> Umberto
>>
>> On 8/21/07, Umberto Nicoletti <umberto.nicoletti at gmail.com> wrote:
>>     
>>> You probably have to add the columns in the CONNECTION STATEMENT.
>>>
>>> Umberto
>>>
>>> On 8/21/07, HarryS <mapcollect at gmx.net> wrote:
>>>       
>>>> this is the layer configuration:
>>>>
>>>> LAYER
>>>>     NAME "City-Database"
>>>>     GROUP "Databases"
>>>>     STATUS default
>>>>     DATA "the_geom from name"
>>>>     CONNECTION "user=postgres dbname=geonamesallppl host=localhost
>>>> port=5432"
>>>>     CONNECTIONTYPE postgis
>>>>     TYPE QUERY
>>>>
>>>>
>>>>     CLASS
>>>>       TEMPLATE "template-city.html"
>>>>       NAME "City-Database"
>>>>     END
>>>>
>>>>   METADATA
>>>>    "queryable" "true"
>>>>    "searchfield" "name"
>>>>    "fields"
>>>> "name:Name,population:Pop,country:Country,province:Province,CODE:Code"
>>>>   END
>>>>
>>>>   END
>>>>
>>>>
>>>> Mapserver can render maps using this layer and also searching via
>>>> KaMap-Frontend works on this layer, only itemnquery via cgi gives me
>>>>         
>>> the
>>>       
>>>> above quoted error. I tried using different cases with column names
>>>>         
>>> (NAME,
>>>       
>>>> Name, name) but always got same error: ...ERROR: Column »london« does
>>>>         
>>> not
>>>       
>>>> exist...
>>>> This I don't understand because »london« is the querystring and not the
>>>> column-name.
>>>>
>>>> Thanks,
>>>> Harry
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Umberto Nicoletti wrote:
>>>>         
>>>>> Please post the layer configuration for postgis AND check the case of
>>>>> the column name.
>>>>> Postgres handles column names with case sensitivity if, at table
>>>>> creation, the column name was in quotes. In that case you have the
>>>>> specify the column name with exactly the same case and in quotes;
>>>>>           
>>> this
>>>       
>>>>> is most likely the reason.
>>>>>
>>>>> HTH,
>>>>> Umberto
>>>>>
>>>>> On 8/21/07, HarryS <mapcollect at gmx.net> wrote:
>>>>>           
>>>>>> doing this query:
>>>>>>
>>>>>>
>>>>>>             
>>> http://myServer.com/cgi-bin/mapserv?&map=/home/harry/Mapfiles/MyMap.map&mode=itemnquery&qlayer=City-Database&qitem=NAME&qstring='London'
>>>       
>>>>>> produces this Error:
>>>>>>
>>>>>> prepare_database(): Query error. Error executing POSTGIS DECLARE
>>>>>>             
>>> (the
>>>       
>>>>>> actual
>>>>>> query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
>>>>>>
>>>>>>             
>>> NAME::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>>>       
>>>>>> from name WHERE (London) and (the_geom && setSRID( 'BOX3D(-180
>>>>>>             
>>> -90,180
>>>       
>>>>>> 90)'::BOX3D,find_srid('','name','the_geom') ))'
>>>>>>
>>>>>> Postgresql reports the error as 'FEHLER: Spalte »london« existiert
>>>>>>             
>>> nicht
>>>       
>>>>>> '
>>>>>> (Translation: ERROR: Column »london« does not exist)
>>>>>>
>>>>>> More Help:
>>>>>>
>>>>>> Error with POSTGIS data variable. You specified '<check your .map
>>>>>>             
>>> file>'.
>>>       
>>>>>> Standard ways of specifiying are :
>>>>>> (1) 'geometry_column from geometry_table'
>>>>>> (2) 'geometry_column from (<sub query>) as foo using unique <column
>>>>>>             
>>> name>
>>>       
>>>>>> using SRID=<srid#>'
>>>>>>
>>>>>> Make sure you put in the 'using unique <column name>' and 'using
>>>>>>             
>>> SRID=#'
>>>       
>>>>>> clauses in.
>>>>>>
>>>>>> For more help, please see
>>>>>>             
>>> http://postgis.refractions.net/documentation/
>>>       
>>>>>> Mappostgis.c - version of Jan 23/2004.
>>>>>>
>>>>>>
>>>>>> If I do the same query on the same layer but using a shapefile
>>>>>>             
>>> instead of
>>>       
>>>>>> PostGIS for identical data the query works fine without any errors.
>>>>>>             
>>> Can
>>>       
>>>>>> someone point me to my mistake, please ?
>>>>>>
>>>>>> Harry Schmidt
>>>>>>
>>>>>>
>>>>>> --
>>>>>> View this message in context:
>>>>>>
>>>>>>             
>>> http://www.nabble.com/Error-on-Itemnquery-with-PostGIS-layer-tf4303941.html#a12250916
>>>       
>>>>>> Sent from the Mapserver - User mailing list archive at Nabble.com.
>>>>>>
>>>>>>             
>>>>>           
>>>> --
>>>> View this message in context:
>>>>         
>>> http://www.nabble.com/Error-on-Itemnquery-with-PostGIS-layer-tf4303941.html#a12252666
>>>       
>>>> Sent from the Mapserver - User mailing list archive at Nabble.com.
>>>>
>>>>         
>>     
>
>   