[ms4w-users] Why can't MS4W work with postGIS? I always fail (numerous attempts).

Wed Aug 6 03:24:47 EDT 2008

Somebody please help. :-(

I am using postgreSQL 8.2.5 for Windows with postGIS. The database was installed using Windows .msi installation package. I am also using ms4w 2.2.7 that comes with MapServer 5.0.2. All installed on Windows XP Service Pack 2.

The MapServer does support postGIS. When I executed mapserv -v, I got the following:
MapServer version 5.0.2 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=PDF OUTPUT=SWF OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

In the postgreSQL database, I have created a database named test_mapserv. I also created a schema named rencana, and a table named kawasanbencana. The name of the geometry column is the_geom.
 I have created a spatial index (kawasanbencanaspix) on the geometry column. I also use lowercase to write the 'from' statement.

The problem is: MapServer always gives the "..Query error. Error executing POSTGIS DECLARE.." error message, no matter what I do. And I have tried everything ad nauseam.

I have tried specifying the DATA line without USING UNIQUE ... USING SRID line. I have tried specifying the DATA line with USING UNIQUE ...USING SRID. I have tried using double apostrophe (") and single apostrophe ('). It doesn't matter. Everything I have tried has failed, and I still get the error message no matter what I did. 

My mapfiles are attached in this mail. Each reflect my futile attempt, and all of them generated the error message.

My first attempt:
=================

This is my first attempt. I have both the mapfile and the error message attached on this mail.
    CONNECTIONTYPE postgis
    CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'
    DATA 'the_geom from rencana.kawasanbencana'

And I got the following error message:
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from rencana.kawasanbencana WHERE () and (the_geom && setSRID( 'BOX3D(528635.525378392 9074209,609092.162121608 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom') ))' Postgresql reports the error as 'ERROR: syntax error at or near ")" LINE 1: ...DR'),gid::text from rencana.kawasanbencana WHERE () and (the... ^ ' 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/ Mappostgis.c - version of Jan 23/2004. 

My second attempt:
==================

I tried adding USING UNIQUE to my DATA line. There is something odd with my spatial table though: whenever I view the data contents with pgAdmin, I can always see the oid column (first column to the left, before the PK column). However, I cannot find the oid column when viewing the table's column definition. The question is: does my table have oid column or not? And does it have something to do with the error?

Nonethless, here's my lines:
    CONNECTIONTYPE postgis
    CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'
    DATA 'the_geom from rencana.kawasanbencana using unique oid' 

And I still get the same error message as above. The difference is that the error message now says 'oid' instead of 'gid'. For instance, "(the_geom)),'NDR'),gid" becomes "(the_geom)),'NDR'),oid", but everything else remains the same.

My third attempt:
=================

Like I said in my second attempt, I'm not sure whether my table has oid column or not. Thus, in my third attempt I used the gid column instead, especially since the gid column is my primary key column anyway. This is my lines:
    CONNECTIONTYPE postgis
    CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'
    DATA 'the_geom from rencana.kawasanbencana using unique gid'

And I got exactly the same error message as my first attempt.

My fourth attempt:
==================

In my fourth attempt, I used the gid column for my unique id, and I am using SRID=1. Here is the lines:
    CONNECTIONTYPE postgis
    CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'
    DATA 'the_geom from rencana.kawasanbencana using unique gid using SRID=1'

Well I still got the error message. This time, the error message is slightly different. Instead of getting "::BOX3D,find_srid", I got "::BOX3D,1".

Here is the complete error message:
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from rencana.kawasanbencana WHERE () and (the_geom && setSRID( 'BOX3D(528635.525378392 9074209,609092.162121608 9134520)'::BOX3D,1) )' Postgresql reports the error as 'ERROR: syntax error at or near ")" LINE 1: ...DR'),gid::text from rencana.kawasanbencana WHERE () and (the... ^ ' 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/
 Mappostgis.c - version of Jan 23/2004. 

My fifth attempt:
=================

Now I had been pretty much desperate, I just wrote using SRID=#, closed my eyes, and hoped for the best. Here's the lines:
    CONNECTIONTYPE postgis
    CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'
    DATA 'the_geom from rencana.kawasanbencana using unique gid using SRID=#'

I still get an error message. This time, the error message is different altogether. Here's the message:
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data variable: You specified 'using SRID=#' but didnt have any numbers! More Help: Error with POSTGIS data variable. You specified 'the_geom from rencana.kawasanbencana using unique gid using SRID=#'. 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/ Mappostgis.c - version of Jan 23/2004. 

I'm pretty much desperate. Could somebody please tell what's wrong on my mapfile? Or maybe ms4w just cannot display postGIS data? Do I need to install postGIS and MapServer on Linux, in order to get them work together? Please help. :-(

Thanks,
-Kresh

-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_mapserv_01.map
Type: application/octet-stream
Size: 1005 bytes
Desc: not available
Url : http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_01.obj
-------------- next part --------------
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from rencana.kawasanbencana WHERE () and (the_geom && setSRID( 'BOX3D(528635.525378392 9074209,609092.162121608 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom') ))' Postgresql reports the error as 'ERROR: syntax error at or near ")" LINE 1: ...DR'),gid::text from rencana.kawasanbencana WHERE () and (the... ^ ' 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/ Mappostgis.c - version of Jan 23/2004. 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_mapserv_02.map
Type: application/octet-stream
Size: 1022 bytes
Desc: not available
Url : http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_02.obj
-------------- next part --------------
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text from rencana.kawasanbencana WHERE () and (the_geom && setSRID( 'BOX3D(528635.525378392 9074209,609092.162121608 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom') ))' Postgresql reports the error as 'ERROR: syntax error at or near ")" LINE 1: ...DR'),oid::text from rencana.kawasanbencana WHERE () and (the... ^ ' 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/ Mappostgis.c - version of Jan 23/2004.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_mapserv_03.map
Type: application/octet-stream
Size: 1022 bytes
Desc: not available
Url : http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_03.obj
-------------- next part --------------
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from rencana.kawasanbencana WHERE () and (the_geom && setSRID( 'BOX3D(528635.525378392 9074209,609092.162121608 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom') ))' Postgresql reports the error as 'ERROR: syntax error at or near ")" LINE 1: ...DR'),gid::text from rencana.kawasanbencana WHERE () and (the... ^ ' 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/ Mappostgis.c - version of Jan 23/2004. 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_mapserv_04.map
Type: application/octet-stream
Size: 1035 bytes
Desc: not available
Url : http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_04.obj
-------------- next part --------------
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from rencana.kawasanbencana WHERE () and (the_geom && setSRID( 'BOX3D(528635.525378392 9074209,609092.162121608 9134520)'::BOX3D,1) )' Postgresql reports the error as 'ERROR: syntax error at or near ")" LINE 1: ...DR'),gid::text from rencana.kawasanbencana WHERE () and (the... ^ ' 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/ Mappostgis.c - version of Jan 23/2004. 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_mapserv_05.map
Type: application/octet-stream
Size: 1035 bytes
Desc: not available
Url : http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_05.obj
-------------- next part --------------
msDrawMap(): Image handling error. Failed to draw layer named 'kawasanbencana'. msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data variable: You specified 'using SRID=#' but didnt have any numbers! More Help: Error with POSTGIS data variable. You specified 'the_geom from rencana.kawasanbencana using unique gid using SRID=#'. 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/ Mappostgis.c - version of Jan 23/2004. 