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

[Nicolas Ribot]

Hello Carlo,

New version of Postgresql (8.1) does not create OID column by default
when creating a table (oid is globally unique identifiers used by
Postgresql and also used by Mapserver to uniquely identify a row of
result).

My solution was to enable oid-generation in postgresql, by editing the
postgresql.conf (in the postgresql/8.1/data folder)
Search for "default_with_oids", uncomment the line and set the
variable value to "on".
Then, regenerate your postgis tables.

I'm pretty sur you could also modify existing tables to add the oid
column, though I don't know if it will work.

With the method above, newly created tables will have the shadow OID
column added.

HTH.
Nicolas

On 11/24/05, Carlo Trönnberg <carlo.t at chello.hu> wrote:
>
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>