[postgis-users] postgis through views

[Brent Wood]

> We're trying to use PostGIS through a VIEW with Mapserver.  We have two 
> identical tables, one being accessed natively and one through a VIEW with
>
> CREATE VIEW r200408_wet_fill as select * from r2004_wet_fill.
> 
> Things seem to not be working.  Are there any known problems for using 
> VIEWs with PostGIS?

Postgres (generally) uses an internal column (oid) in each table to uniquely
number each row. Unless explicitly stated, your view will not have the oid
column. From the mapserver error message, it suggests that mapserver uses this
column, hence the error with your view.

So create your view as: 
CREATE VIEW r200408_wet_fill as select oid, * from r2004_wet_fill

to ensure your view contains the oid column. 

I don't know this for sure, but if mapserver uses the oid, there may be some
performance benefits to indexing the oid column in the underlying table. Does
anyone know this for sure? (Postgres does NOT index the oid by default).


One other aspect of views on spatial tables relates to the geometry_columns
table. When you add a spatial (geometry) column to a table, an entry in the
geometry_columns table is created, which describes the new spatial column. Your
view will not have an entry in this table (but the underlying table will).

I don't know about mapserver, but some applications do refer to this table to
identify tables with geometry columns. So creating an entry in this table for
your spatial views may also resolve some problems.


Cheers,

  Brent Wood