[postgis-users] Quoting column names in MapServer PostGIS Support

[Frank Warmerdam]

Guys,

On behalf of BASIC (Bay Area Shared Information Consortium - www.basic.org)
I am trying to load a bunch of DLG data into PostGIS, and then map it with
MapServer.

I have encountered a problem using the MINOR1 column as the CLASSITEM.  It
seems that the PostGIS code in MapServer doesn't quote the column names
when selecting them, but that this means any upper case (or otherwise weird)
column name doesn't work.

It seems that this minor change fixes the problem for me, but I wonder if
a more comprehensive pass needs to be made in the mappostgis.c code to
ensure that column names are always quoted?

201c201
<                       sprintf(temp,"%s::text,",layer->items[t]);
---
 >                       sprintf(temp,"\"%s\"::text,",layer->items[t]);

I also found it very difficult to debug the problem because I can't see
the actual SQL commands that mappostgis.c is using when something goes wrong.
The following patch made one particular error message also report the SQL
string.  Should we try to do this more comprehensively?  Would you like me
to take a crack at either issue?

305,306c305,307
<             msSetError(MS_QUERYERR, "prep_DB:Error executing POSTGIS  DECLARE statement 
(0.6 failed - retried 0.5 and it failed too).",
<                  "msPOSTGISLayerWhichShapes()");
---
 >             msSetError(MS_QUERYERR, "prep_DB:Error executing POSTGIS  DECLARE statement 
(0.6 failed - retried 0.5 and it failed too).\n%s",
 >                          "msPOSTGISLayerWhichShapes()",
 >                          query_string_0_5 );

As an aside, I have done substantial work on OGR to use it for loading data.
This included ensuring that OGR always quotes column names (thus resulting in
odd column names like MINOR1 and LNODE# in PostgreSQL.  I also add the ability
for ogr2ogr to append to an existing table so I could easily merge many
individual DLG datasets (actually in Arc/Info Binary Coverage format in this
case) into one layer.  I imagine these are things the shapefile loader can
already do, but I would it handy to use ogr2ogr.

I have also recently modified OGR so that the OGRLayer::ExecuteSQL() method
will pass the SQL through to PostGIS in the PostGIS case, and treat the
query result as a pseudo-layer.  That means that a command like

ogrinfo -sql 'select distinct major1 from dlg_ve2' PG:dbname=test

works, though of course, some of this would be better off done in the SQL
shell.  However, for other cases, the fact that a general query can be used as
a temporary layer (including for the ogr2ogr command) could be useful.  I
suspect this is the only mechanism to get at views from OGR right now.  How
do I create a view in PostgreSQL?  I would like to test OGR against a view.

Best regards,

-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent