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

[Adam Eskreis]

You could try regex

\"Area\"

-Adam

On Wed, May 27, 2009 at 6:48 PM, Peter Willis  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"
>
>
> Mapserver relays a PostGIS error from PostgreSQL:
> 'ERROR:  column "area" does not exist...
>
> 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@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

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

[Peter Willis]

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 > 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"


Mapserver relays a PostGIS error from PostgreSQL:
'ERROR:  column "area" does not exist...

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@lists.osgeo.org 
http://lists.osgeo.org/mailman/listinfo/mapserver-users





___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [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 
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 > > 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"
>> 
>> 
>> Mapserver relays a PostGIS error from PostgreSQL:
>> 'ERROR:  column "area" does not exist...
>> 
>> 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@lists.osgeo.org
>> 
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>> 
>> 
> 
> 
> ___
> mapserver-users mailing list
> mapserver-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

-- 
Cheers,
SIMON
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

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

[Peter Willis]

Hello,

Darn!, I just sent a [RESOLVED] with the same solution.
(just as I was receiving your email...)
Yes, this is how I managed to to make it work.

Everything works now.
I guess you get the badge for that one.

Thanks for your help.

Peter



Simon Haddon wrote:

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


___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

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

[Simon Haddon]

Thanks, Now I just need that chest.  lol

On Wed, 27 May 2009 16:29:54 -0700, Peter Willis 
wrote:
> Hello,
> 
> Darn!, I just sent a [RESOLVED] with the same solution.
> (just as I was receiving your email...)
> Yes, this is how I managed to to make it work.
> 
> Everything works now.
> I guess you get the badge for that one.
> 
> Thanks for your help.
> 
> Peter
> 
> 
> 
> Simon Haddon wrote:
>> 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
>>

-- 
Cheers,
SIMON
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users