<div dir="ltr">Hi,<br> I've used MS4W and POSTGis to display maps in the Web. It's really works. The data loader was done using the SPIT extension of QuantumGIS. I think you did the same. <br> I've looked your .map, and i compared with the .map that i've used in my aplication, and the difference it's the way how you tell mapserver the datasource. The mine look like this:<br>
<br>CONNECTION "user=xxx password=xxxx dbname=xxx host=xxx"<br>data "the_geom from table_name USING UNIQUE gid USING SRID=-1"<br><br> Try to use that sintax, it could help.<br> <br> <br><br><div class="gmail_quote">
On Wed, Aug 6, 2008 at 6:00 PM, <span dir="ltr"><<a href="mailto:ms4w-users-request@lists.maptools.org">ms4w-users-request@lists.maptools.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Send ms4w-users mailing list submissions to<br>
<a href="mailto:ms4w-users@lists.maptools.org">ms4w-users@lists.maptools.org</a><br>
<br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a href="http://lists.maptools.org/mailman/listinfo/ms4w-users" target="_blank">http://lists.maptools.org/mailman/listinfo/ms4w-users</a><br>
or, via email, send a message with subject or body 'help' to<br>
<a href="mailto:ms4w-users-request@lists.maptools.org">ms4w-users-request@lists.maptools.org</a><br>
<br>
You can reach the person managing the list at<br>
<a href="mailto:ms4w-users-owner@lists.maptools.org">ms4w-users-owner@lists.maptools.org</a><br>
<br>
When replying, please edit your Subject line so it is more specific<br>
than "Re: Contents of ms4w-users digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. Why can't MS4W work with postGIS? I always fail (numerous<br>
attempts). (<a href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</a>)<br>
<br>
<br>
----------------------------------------------------------------------<br>
<br>
Message: 1<br>
Date: Wed, 6 Aug 2008 00:24:47 -0700 (PDT)<br>
From: "<a href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</a>" <<a href="mailto:kreshna_iceheart@yahoo.com">kreshna_iceheart@yahoo.com</a>><br>
Subject: [ms4w-users] Why can't MS4W work with postGIS? I always fail<br>
(numerous attempts).<br>
To: <a href="mailto:ms4w-users@lists.maptools.org">ms4w-users@lists.maptools.org</a><br>
Message-ID: <<a href="mailto:257691.12166.qm@web56107.mail.re3.yahoo.com">257691.12166.qm@web56107.mail.re3.yahoo.com</a>><br>
Content-Type: text/plain; charset="us-ascii"<br>
<br>
Somebody please help. :-(<br>
<br>
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 <a href="http://5.0.2." target="_blank">5.0.2.</a> All installed on Windows XP Service Pack 2.<br>
<br>
The MapServer does support postGIS. When I executed mapserv -v, I got the following:<br>
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<br>
<br>
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.<br>
I have created a spatial index (kawasanbencanaspix) on the geometry column. I also use lowercase to write the 'from' statement.<br>
<br>
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.<br>
<br>
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.<br>
<br>
My mapfiles are attached in this mail. Each reflect my futile attempt, and all of them generated the error message.<br>
<br>
<br>
My first attempt:<br>
=================<br>
<br>
This is my first attempt. I have both the mapfile and the error message attached on this mail.<br>
CONNECTIONTYPE postgis<br>
CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'<br>
DATA 'the_geom from rencana.kawasanbencana'<br>
<br>
And I got the following error message:<br>
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<br>
<a href="http://postgis.refractions.net/documentation/" target="_blank">http://postgis.refractions.net/documentation/</a> Mappostgis.c - version of Jan 23/2004.<br>
<br>
<br>
My second attempt:<br>
==================<br>
<br>
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?<br>
<br>
Nonethless, here's my lines:<br>
CONNECTIONTYPE postgis<br>
CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'<br>
DATA 'the_geom from rencana.kawasanbencana using unique oid'<br>
<br>
<br>
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.<br>
<br>
<br>
My third attempt:<br>
=================<br>
<br>
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:<br>
CONNECTIONTYPE postgis<br>
CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'<br>
DATA 'the_geom from rencana.kawasanbencana using unique gid'<br>
<br>
And I got exactly the same error message as my first attempt.<br>
<br>
<br>
My fourth attempt:<br>
==================<br>
<br>
In my fourth attempt, I used the gid column for my unique id, and I am using SRID=1. Here is the lines:<br>
CONNECTIONTYPE postgis<br>
CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'<br>
DATA 'the_geom from rencana.kawasanbencana using unique gid using SRID=1'<br>
<br>
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".<br>
<br>
Here is the complete error message:<br>
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 <a href="http://postgis.refractions.net/documentation/" target="_blank">http://postgis.refractions.net/documentation/</a><br>
Mappostgis.c - version of Jan 23/2004.<br>
<br>
<br>
My fifth attempt:<br>
=================<br>
<br>
Now I had been pretty much desperate, I just wrote using SRID=#, closed my eyes, and hoped for the best. Here's the lines:<br>
CONNECTIONTYPE postgis<br>
CONNECTION 'host=localhost user=rtrw password=123456 port=5432 dbname=test_mapserver'<br>
DATA 'the_geom from rencana.kawasanbencana using unique gid using SRID=#'<br>
<br>
I still get an error message. This time, the error message is different altogether. Here's the message:<br>
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 <a href="http://postgis.refractions.net/documentation/" target="_blank">http://postgis.refractions.net/documentation/</a> Mappostgis.c - version of Jan 23/2004.<br>
<br>
<br>
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. :-(<br>
<br>
<br>
Thanks,<br>
-Kresh<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: test_mapserv_01.map<br>
Type: application/octet-stream<br>
Size: 1005 bytes<br>
Desc: not available<br>
Url : <a href="http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_01-0001.obj" target="_blank">http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_01-0001.obj</a><br>
-------------- next part --------------<br>
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 <a href="http://postgis.refrac" target="_blank">http://postgis.refrac</a>!<br>
<a href="http://tions.net/documentation/" target="_blank">tions.net/documentation/</a> Mappostgis.c - version of Jan 23/2004.<br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: test_mapserv_02.map<br>
Type: application/octet-stream<br>
Size: 1022 bytes<br>
Desc: not available<br>
Url : <a href="http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_02-0001.obj" target="_blank">http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_02-0001.obj</a><br>
-------------- next part --------------<br>
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 <a href="http://postgis.refrac" target="_blank">http://postgis.refrac</a>!<br>
<a href="http://tions.net/documentation/" target="_blank">tions.net/documentation/</a> Mappostgis.c - version of Jan 23/2004.<br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: test_mapserv_03.map<br>
Type: application/octet-stream<br>
Size: 1022 bytes<br>
Desc: not available<br>
Url : <a href="http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_03-0001.obj" target="_blank">http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_03-0001.obj</a><br>
-------------- next part --------------<br>
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 <a href="http://postgis.refrac" target="_blank">http://postgis.refrac</a>!<br>
<a href="http://tions.net/documentation/" target="_blank">tions.net/documentation/</a> Mappostgis.c - version of Jan 23/2004.<br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: test_mapserv_04.map<br>
Type: application/octet-stream<br>
Size: 1035 bytes<br>
Desc: not available<br>
Url : <a href="http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_04-0001.obj" target="_blank">http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_04-0001.obj</a><br>
-------------- next part --------------<br>
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 <a href="http://postgis.refractions.net/documentation/" target="_blank">http://postgis.refractions.net/documentation/</a> Mappostgis.c - version !<br>
of Jan 23/2004.<br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: test_mapserv_05.map<br>
Type: application/octet-stream<br>
Size: 1035 bytes<br>
Desc: not available<br>
Url : <a href="http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_05-0001.obj" target="_blank">http://lists.maptools.org/pipermail/ms4w-users/attachments/20080806/f7299972/test_mapserv_05-0001.obj</a><br>
-------------- next part --------------<br>
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 <a href="http://postgis.refractions.net/documentation/" target="_blank">http://postgis.refractions.net/documentation/</a> Mappostgis.c - version of Jan 23/2004.<br>
<br>
------------------------------<br>
<br>
_______________________________________________<br>
ms4w-users mailing list<br>
<a href="mailto:ms4w-users@lists.maptools.org">ms4w-users@lists.maptools.org</a><br>
<a href="http://lists.maptools.org/mailman/listinfo/ms4w-users" target="_blank">http://lists.maptools.org/mailman/listinfo/ms4w-users</a><br>
<br>
<br>
End of ms4w-users Digest, Vol 52, Issue 1<br>
*****************************************<br>
</blockquote></div><br><br clear="all"><br>-- <br> Evelyn Briones Ortega<br> Ingeniero Civil en Informática<br> Universidad del Bío Bío<br> Concepción - Chile<br>
</div>