[maplab-users] Problem with MapLab and PostGIS

[Gino Lucrezi]

Hi all!

I am using the following items:

MapLab (2.0.1-release) 

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

PostgreSQL 7.3.2

PostGIS 0.8.1

In PostGIS I created a table called Capoluoghi, which includes (among other fields) one called the_geom.
This table contains 4 rows, one for each chief provincial towns in the region of Abruzzo.

The data is the same I have in a shapefile, and it is displayed correctly when I use it from the shapefile.

When I try to insert that data in a mapfile using MapEdit, however, it fails. It appears that preview.php is producing a 

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


----------------------8<--cut-here--8<----------------
MAP
  NAME "Mappa di Prova"
  EXTENT 2510011 4600000 2350000 4760000 
  SIZE 550 500 
  SHAPEPATH "../shape"
  SYMBOLSET "./etc/symbols.sym"
  FONTSET "./etc/fonts.txt"
  IMAGECOLOR 0 0 0
  UNITS METERS
  IMAGETYPE JPEG
  WEB
    MINSCALE 2000
    MAXSCALE 300000
    IMAGEPATH "/var/www/html/tmp/"
    IMAGEURL "/tmp/"
  END
  REFERENCE
    STATUS ON
    IMAGE "images/keymap2.png"
    SIZE 100 92 
    EXTENT 2350007 4600009 2510011 4740013 
    COLOR -1 -1 -1
    OUTLINECOLOR 255 0 0
  END
  LEGEND
    KEYSIZE 18 12 
    LABEL
      TYPE BITMAP
      SIZE MEDIUM
      COLOR 0 0 89
    END
  END
  SCALEBAR
    STATUS ON
    COLOR 255 255 255
    OUTLINECOLOR 0 0 0
    BACKGROUNDCOLOR 0 0 0
    IMAGECOLOR 255 255 255
    UNITS KILOMETERS
    INTERVALS 5
    SIZE 150 5 
    LABEL
      SIZE SMALL
      COLOR 0 0 0
    END
  END
  LAYER
    NAME "TestPostgis"
    STATUS OFF
    DATA "the_geom FROM capoluoghi"
    TYPE POINT
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=postgres dbname=geodb"
  END
  LAYER
    NAME "25000"
    GROUP "25"
    STATUS ON
    DATA "../demo/mappe/359ovest.tif"
    TYPE RASTER
  END
  LAYER
    NAME "Limiti Provinciali"
    GROUP "Altro"
    STATUS ON
    DATA "./province"
    TYPE POLYGON
    TEMPLATE "ttt_query.html"
    CLASS
      NAME "province"
      OUTLINECOLOR 255 0 0
    END
  END
END
----------------------8<--cut-here--8<----------------


If I preview it, everything is OK because my PostGis layer is disabled by default. 
As soon as I enable it I get this error page:

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


It appears that the PostGIS query is filled with control characters.
That's why I think it's a MapLab issue.


If I look at the PostGis log, here is what I see.

----------------------8<--cut-here--8<----------------
Mar 22 13:54:35 localhost postgres[9960]: [1] ERROR:  parser: parse error at or near "#" at character 104
Mar 22 13:54:35 localhost postgres[9960]: [2-1] LOG:  statement: DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d()),'NDR'),OID::text from h#^SBh#^SB^S
Mar 22 13:54:35 localhost postgres[9960]: [2-2]  WHERE  && setSRID('BOX3D(2342011 4600000,2518000 4760000)'::BOX3D, find_srid('','h#^SBh#^SB^S','') )
----------------------8<--cut-here--8<----------------

Again, lots of control characters.

I would also mention that I correctly created an extra index on oid (even though it shouldn't be needed in this instance).

The table is working correctly, and SELECT the_geom FROM capoluoghi yelds the desired results.
For these reasons, I don't believe I'm doing anything wrong on the mapfile or on PostGis. Since the query contains random (garbage) characters, it isn't the fault of PostgreSQL or PostGIS.
So, it either is a problem with my MapLab installation or a bug...

Can anyone help me?

Gino