I am an idiot. Well I don't have a database named postgis on the first place (only template_postgis), but I tried to apply your first DATA line to my own database (test_mapserv).
Then I realized: your file don't have FILTER "" clause. At the same time, someone from postGIS mailing list told me that an empty FILTER clause will just confuse postGIS. So I remove it from my mapfile and suddenly everything works! Thanks, -Kresh --- On Wed, 8/6/08, Kusum <[EMAIL PROTECTED]> wrote: > From: Kusum <[EMAIL PROTECTED]> > Subject: Re: [mapserver-users] Still cannot display postGIS layers using > ms4w(MapServer 5.0.2 on WinXP). > To: [EMAIL PROTECTED] > Date: Wednesday, August 6, 2008, 3:18 AM > Try this format :- > LAYER > NAME "route" > STATUS ON > TYPE line > CONNECTIONTYPE postgis > CONNECTION "user=postgres dbname=postgis > host=localhost port=5432 > password=postgres" > DATA "the_geom from ind_road" > #DATA "the_geom from (SELECT the_geom, gid from > shortest_path_as_geometry('hr_road', 1, 50)) as > route using unique gid using > srid=-1" > #DATA "the_geom from (SELECT the_geom from > routing_results) as thing using > unique gid using srid=-1" > #DATA "the_geom from (SELECT gid, the_geom from > routing_results) as foo > using unique gid using srid=-1" > > TEMPLATE "t" > PROJECTION > "proj=latlong" > "ellps=WGS84" > "datum=WGS84" > END > > -------------------------------------------------- > From: <[EMAIL PROTECTED]> > Sent: Wednesday, August 06, 2008 12:40 PM > To: <mapserver-users@lists.osgeo.org> > Subject: [mapserver-users] Still cannot display postGIS > layers using > ms4w(MapServer 5.0.2 on WinXP). > > > Help. Somebody please help. Somebody. Anybody. :-( > > > > 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. However, I cannot find > the oid column when > > viewing the table's column definition. I attached > the screenshots > > accordingly. 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 > > > > > > > > > > > > > _______________________________________________ > > mapserver-users mailing list > > mapserver-users@lists.osgeo.org > > > http://lists.osgeo.org/mailman/listinfo/mapserver-users > > _______________________________________________ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users