Opened 20 years ago

Closed 18 years ago

#623 closed defect (fixed)

Case significant in PostGIS queries?

Reported by: gino-mapserverbugs@… Owned by: refractions
Priority: normal Milestone:
Component: PostGIS Interface Version: 4.0
Severity: normal Keywords:
Cc: tylermitchell@…

Description

I am using MapServer version 4.0.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP 
SUPPORTS=FREETYPE INPUT=TIFF INPUT=EPPL7 INPUT=JPEG INPUT=POSTGIS INPUT=GDAL 
INPUT=SHAPEFILE

I have geographical data stored in a PostgreSQL database (PostgreSQL 7.3.2 + 
PostGIS 0.8.1)

That data came from a shapefile which MapServer could display correctly, so the 
data itself is OK.

When I try to include that data in a mapfile, however, it doesn't work.

Here is the mapfile I produced, following the PostGIS docs:

Here is a sample layer definition:
  LAYER
    NAME "TestPostgis"
    STATUS OFF
    DATA "the_geom FROM capoluoghi"
    TYPE POINT
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=postgres dbname=geodb"
  END

Here is the error I got:
----------------------8<--cut-here--8<----------------

Warning: [MapServer Error]: msDrawMap(): Failed to draw layer 
named 'TestPostgis'. in /var/www/html/maplab/htdocs/mapedit/preview.php on line 
465

Warning: [MapServer Error]: prep_DB(): Error executing POSTGIS DECLARE (the 
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary
(force_collection(force_2d()),'NDR'),OID::text from h#Bh#B WHERE && setSRID
('BOX3D(2342011 4600000,2518000 4760000)'::BOX3D, find_srid
('','h#Bh#B','') )' 

Postgresql reports the error ''

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.php 

Mappostgis.c - version of June 12/2003. 
in /var/www/html/maplab/htdocs/mapedit/preview.php on line 465

Warning: [MapServer Error]: msPOSTGISLayerParseData(): Error parsing POSTGIS 
data variable. Must contain 'geometry_column from table_name' or 'geom from 
(subselect) as foo' (couldnt find ' from '). More help: 

Error with POSTGIS data variable. You specified 'the_geom FROM capoluoghi'.
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.php 

Mappostgis.c - version of June 12/2003. 
in /var/www/html/maplab/htdocs/mapedit/preview.php on line 465

----------------------8<--cut-here--8<----------------

Please note that there are many "strange" (non-ASCII) characters in the 
PostgreSQL query.


If I change in the definition the line
    DATA "the_geom FROM capoluoghi"
to 
    DATA "the_geom from capoluoghi"
it works correctly.

If I query PostgreSQL/PostGIS directly it doesn't matter whether I use "FROM 
or "from", so I'd exclude its fault.
Also, it is MapServer which generates a strange query, so I suppose it's 
MapServer's fault.

It is easy to circumvent this inconvenient (using lower case), but it could be 
a symptom of a more serious bug.

Change History (6)

comment:1 by tylermitchell@…, 19 years ago

Cc: tylermitchell@… added
Any movement on this bug?  I _really_ don't want to have to explain this 
intricacy to readers :) 

comment:2 by refractions, 19 years ago

Can you confirm this still exists? We were not good at using bugzilla (still
aren't) and a number of case / data string fixes have been added since this report.

comment:3 by tylermitchell@…, 19 years ago

It is a problem for me right now, running MS 4.4.0-beta3 and Postgis 0.8.   

comment:4 by tylermitchell@…, 19 years ago

It is still a probably for me, running 4.4.1. :) 

comment:5 by tomkralidis, 19 years ago

I have the same problem, using MapServer fresh CVS and PostGIS 1.0.0RC2

comment:6 by mark@…, 18 years ago

Resolution: fixed
Status: newclosed
This was fixed in rev 1.47.
Note: See TracTickets for help on using tickets.