error accessing postgis

[Mark Cave-Ayland]

Hi Rick,

Under PostgreSQL 8.1 the in-built OID column on user table is deprecated,
and so you must specify a unique column name for Mapserver to use as a
primary key by adding a “using unique” clause to your data statement, e.g.

DATA “iarea from sdata using unique id”

Perhaps this is worth mentioning in the FAQ, or even modifying the version
detection code in mappostgis.c so that if PostgreSQL 8.1 is detected then a
more helpful error message can be emitted.


Kind regards,

Mark.

-----

WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk  
http://www.infomapper.com
http://www.swtc.co.uk 

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.



________________________________________
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Rick Schumeyer
Sent: 28 November 2005 15:46
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: [UMN_MAPSERVER-USERS] error accessing postgis

Mapserver [v4.6.1] returns an error when I try to access a postgis [1.0.4]
database.  (Postgis is installed with postgresql 8.1.0).  I assume my
mapfile is incorrect, but I’m not sure what the mistake is.
 
Here is the error:
 
msDrawMap(): Image handling error. Failed to draw layer named 'sdata'. 
prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(iarea)),'NDR'),OID::text from sdata WHERE
iarea && setSRID('BOX3D(-180 -135,180 135)'::BOX3D,
find_srid('','sdata','iarea') )' 

Postgresql reports the error as 'ERROR: column "oid" does not exist '
 
The relevant portion of my mapfile:
 
LAYER
     NAME "sdata"
     CONNECTIONTYPE POSTGIS
     CONNECTION "dbname=gtest user=postgres host=localhost"
     DATA "iarea from sdata"
     TYPE line
     STATUS default
     CLASS
          NAME "State Line"
          STYLE
               SYMBOL "BigLine"
               SIZE 2
               COLOR 255 0 0
          END
     END
END
 
The definition of the table “sdata”:
 
Table “public.sdata"
   Column   |           Type           |                     Modifiers
------------+--------------------------+------------------------------------
----------------
 id         | integer                  | not null default
nextval('sdata_id_seq'::regclass)
 instrument | character varying(20)    |
 begintime  | timestamp with time zone |
 endtime    | timestamp with time zone |
 iarea      | geometry                 |
 filename   | character varying(512)   |
Indexes:
    "idx_iarea" gist (iarea)
Check constraints:
    "enforce_dims_iarea" CHECK (ndims(iarea) = 2)
    "enforce_geotype_iarea" CHECK (geometrytype(iarea) = 'POLYGON'::text OR
iarea IS NULL)
    "enforce_srid_iarea" CHECK (srid(iarea) = 4326)
ECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(iarea)),'NDR'),OID::text from sdata WHERE
iarea && setSRID('BOX3D(-180 -135,180 135)'::BOX3D,
find_srid('','sdata','iarea') )