[mapserver-users] Mapserver/PostGIS map file problem (double quotes in layer 'DATA' element)
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 ERRMapserver 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@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)
You could try regex \Area\ -Adam On Wed, May 27, 2009 at 6:48 PM, Peter Willis pet...@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 ERRMapserver 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@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)
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 pet...@borstad.com mailto:pet...@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 ERRMapserver 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@lists.osgeo.org mailto: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)
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 pet...@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 pet...@borstad.com mailto:pet...@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 ERRMapserver 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@lists.osgeo.org mailto: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)
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)
Thanks, Now I just need that chest. lol On Wed, 27 May 2009 16:29:54 -0700, Peter Willis pet...@borstad.com 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
