Error on Itemnquery with PostGIS-layer

Tue Aug 21 13:03:39 EDT 2007

I tried what you suggested in Layer definition:

...
DATA "the_geom from name.name"
...

without success (and also map-drawing and search-function with Kamap does
not workd anymore)

I also tried in URL:

http://myServer.com/cgi-bin/Hybrid?&map=/home/Harry/Mapfile/MyMap.map&mode=itemnquery&qlayer=City-Database&qitem='name.name'&qstring='London'

what causes this error on a Debian Linux machine with Mapserver 4.8.3:

prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
'name.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 '

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. 

Harry (still confused...)

Sebastian Walter-2 wrote:
> 
> 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.
>>>>>
>>>>>         
>>>     
>>
>>   
> 
> 

-- 
View this message in context: http://www.nabble.com/Error-on-Itemnquery-with-PostGIS-layer-tf4303941.html#a12258926
Sent from the Mapserver - User mailing list archive at Nabble.com.