[mapserver-users] Mapserver/PostGIS map file problem (double quotes in layer 'DATA' element)

[Simon Haddon]

Have you tried

DATA 'the_geom from (select oid,gid, the_geom,
     "Area","Perimeter","PixelValue" FROM "global_Land_poly" WHERE
"PixelValue"=1 )
     AS FOO using SRID=4326'

The other options is to change the table name and column names to be lower
case or case insensitive. Mixed case table and column names are always a
pain in any database system.

If you can't modify the table then try creating a view and using the view
in your query instead. Make sure your view is created all lower case
without quotes.  This will probably mean you will need to alias your select
to lower case the column names and you will probably have to add it to the
postgis geometry tables manually but other than that it will work.

Cheers,
Simon

On Wed, 27 May 2009 16:00:24 -0700, Peter Willis <peterw at borstad.com>
wrote:
> I tried that. I get the following error:
> 
> loadLayer(): Unknown identifier. Parsing error near (Area):(line 30)
> 
> Mapserver doesn't appear to like the additional formatting.
> Do I need to recompile with system regex I wonder....?
> 
> Peter
> 
> 
> Adam Eskreis wrote:
>> You could try regex
>> 
>> \"Area\"
>> 
>> -Adam
>> 
>> On Wed, May 27, 2009 at 6:48 PM, Peter Willis <peterw at borstad.com 
>> <mailto:peterw at borstad.com>> wrote:
>> 
>> 
>>     Hello,
>> 
>>     I am having a problem serving a PostGIS layer via mapserver
>>     as WFS.
>> 
>>     The problem arises from the generation/use of column names
>>     in PostgreSQL that require double quotes.
>> 
>>     ie:
>> 
>>      SELECT oid,gid, the_geom, "Area","Perimeter","PixelValue" FROM
>>     global_Land_poly WHERE "PixelValue"=1;
>> 
>> 
>> 
>>     In the map file the 'DATA' member of the PostGIS layer is defined
as:
>> 
>>     DATA "the_geom from (select oid,gid, the_geom,
>>     Area,Perimeter,PixelValue FROM global_Land_poly WHERE PixelValue=1 )
>>     AS FOO using SRID=4326"
>> 
>> 
>>     <ERR>Mapserver relays a PostGIS error from PostgreSQL:
>>     'ERROR:  column "area" does not exist...</ERR>
>> 
>>     This is because the column name is actually "Area"
>>     and requires quotes.
>> 
>>     How do I define double quotes in my PostGIS query
>>     within the 'DATA' element of my mapfile layer?
>> 
>>     Thanks for any enlightenment,
>> 
>>     Peter
>>     _______________________________________________
>>     mapserver-users mailing list
>>     mapserver-users at lists.osgeo.org
>>     <mailto:mapserver-users at lists.osgeo.org>
>>     http://lists.osgeo.org/mailman/listinfo/mapserver-users
>> 
>> 
> 
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

-- 
Cheers,
SIMON