error accessing postgis

[Russ Bradford]

I have had this same problem. I could not do even the most simple SQL
query using MapServer and a postgis postgreSQL database. There was no
"oid" column - even though there was one in the database.

I have just overcome this problem (which was more related to my
inexperience than anything else) by using the following definition for
the DATA tag:

	DATA "geom from (select column_a, oid, geom from tablename where
column_name = 	'value') as foo using unique oid using srid =  4326)"

I use column_a as the LABELITEM. In short I have found that in the DATA
definition you need to specify every column that you will be using
(specifically "oid").

Hope this helps.

Cheers, Russ.

-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Stephen Woodbridge
Sent: Tuesday, 29 November 2005 3:02 AM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] error accessing postgis


By default posgres 8.1 does not build oids any more. You need to change 
you query to specify some other unique column for mapserver. You can add

a serial column and make it the primary key then reference that.

-Steve W.

Rick Schumeyer wrote:
> 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') )
> 
>  
> 
>  
>