[postgis-users] MapServer dies when trying to view a layer generated with PostGIS

[Carlo Trönnberg]

Dear list,

I have troubles to get PostGIS to work with MapServer.
I went through the PostGIS tutorial, using the sampe data. PostGIS works great on the command line.
But then I tried to call PostGIS with the MapServer as described in the tutorial adding the following layer to the map file of some working sample maps of nboth GMap and Chameleon:

LAYER
  NAME "elections"
  CONNECTIONTYPE postgis
  CONNECTION "host=localhost port=5432 dbname=bc password=[ I won't tell you! :-) ] user=postgres"
  DATA "the_geom from bc_voting_areas"
    TYPE POLYGON
    STATUS OFF
    PROJECTION
    "proj=aea"
    "ellps=GRS80"
    "lon_0=-126"
    "lat_0=45"
    "lat_1=50"
    "lat_2=58.5"
    "x_0=1000000"
  END
  CLASS
    NAME "Voting Areas"
    OUTLINECOLOR 0 0 0
    COLOR 255 255 200
  END
END


If the layer is disabled (STATUS OFF) then everything is OK. BUT if i enable it, MapServer cannot render the map (image missing symbol in the browser). I switched on the Apache error logging to see what happens. Here is an extract of the error log. A column oid seems to be missing... But that does not make me smarter. I got similar error logs with both GMap and Chameleon.

[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] PHP Warning:  [MapServer Error]: msDrawMap(): Failed to draw layer named 'elections'., referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1]  in C:\\ms4w\\apps\\chameleon\\htdocs\\common\\wrapper\\drawmap.php on line 533, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] PHP Warning:  [MapServer Error]: prep_DB(): 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 bc_voting_areas WHERE the_geom && setSRID('BOX3D(-1077436.43349587 -810519.362607715,6569519.15906088 5920600.07354032)'::BOX3D, find_srid('','bc_voting_areas','the_geom') )' <br><br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] Postgresql reports the error as 'ERROR:  column "oid" does not exist, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] '<br><br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:11 2005] [error] [client 127.0.0.1] More Help:<br><br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] Error with POSTGIS data variable. You specified '<check your .map file>'.<br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] Standard ways of specifiying are : <br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] (1) 'geometry_column from geometry_table' <br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] (2) 'geometry_column from (<sub query>) as foo using unique <column name> using SRID=<srid#>' <br><br>, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] Make sure you put in the 'using unique  <column name>' and 'using SRID=#' clauses in., referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] <br><br>For more help, please see http://postgis.refractions.net/documentation.php , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] <br><br>Mappostgis.c - version of Jan 23/2004., referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] , referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1]  in C:\\ms4w\\apps\\chameleon\\htdocs\\common\\wrapper\\drawmap.php on line 533, referer: http://localhost/chameleon/samples/sample_enhanced.phtml
[Thu Nov 24 17:32:12 2005] [error] [client 127.0.0.1] PHP Warning:  Cannot modify header information - headers already sent by (output started at C:\\ms4w\\apps\\chameleon\\htdocs\\common\\wrapper\\drawmap.php:533) in C:\\ms4w\\apps\\chameleon\\htdocs\\common\\wrapper\\drawmap.php on line 1476, referer: http://localhost/chameleon/samples/sample_enhanced.phtml

I am quite deseperate: I tried different versions of MS4W (1.0.3 and 1.2.2) but that did not change the situation.
I am using PostgreSQL 8.1 with the PostGIS included with it under Windows XP Pro SP2.
Trying step by step different PostGIS tutorials I get the same error.
I have tried this on 2 different computers and get the same errors.

What can be the problem? As opposed to what I have read in the forum, in my situation ALL layers die when I try to get a layer via PostGIS.
I previously used MySQL via ODBC but that gave me trouble as well (I could not query the database, just dump a complete column). This is why I decided to convert to PostGIS (which is MUCH better anyway for my task).

Thanks for your help and your time.

Cheers,

Carlo Trönnberg
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20051124/252d326e/attachment.html>