[postgis-users] Using Views as a source of geometric data?

[Dave Blasby]

C F wrote:
> Can PostGIS views (PostgreSQL views which include the PostGIS geometry
> column) be used as layers in Mapserver?  If so, how?  From what I gather
> from previous posts, it's not as simple as it sounds.
> 
> Perhaps this would be better asked on the Mapserver list.  If so, I'll try
> there next.

I wasnt sure what Vinko was asking - but the answer to your question is
Yes - you can use views (and arbitrary SQL) for your layers.

First, I suggest you use the CVS 3.7 mappostgis.c file as it has spiffy
new error reporting, a few minor problems fixed, and lot of suggestions
if you make a mistake.  You can compile the 3.7 mappostgis.c with 3.6.

To use a view, do something like:
'<geometry_column> from (SELECT * FROM <view>) as foo using unique
<column name> using SRID=<srid#>'
Ie. 'the_geom from (SELECT * FROM myview) as foo using unique gid using
SRID=-1'

I've include the error message below:

<postgresql error message>

More Help:

Error parsing POSTGIS data variable. You specified ''.
Standard ways of specifiying are :
(1) 'geometry_column from geometry_table'
(2) 'geometry_column from (<sub query>) as foo using unique <column
name> using SRID=<srid#>'

NOTE: for (2) 'using unique' and 'SRID=' are optional, but its highly
recommended that you use them!!!

The most common problem with (1) is incorrectly uploading your data.
There must be an entry in the geometry_columns table. This will be
automatically done if you used the shp2pgsql program or created your
geometry column with the AddGeometryColumn() postgis function.

If you are using the (2) method, you've probably made a typo.
Example: 'the_geom from (select the_geom,oid from mytable) as foo using
unique oid using SRID=76'
This is very much like the (1) example. The subquery ('select
the_geom,oid from mytable') will be executed, and mapserver will use
'oid' (a postgresql system column) for uniquely specifying a geometry
(for mapserver queries). The geometry (the_geom) must have a SRID of 76.

Example: 'roads from (select
table1.roads,table1.rd_segment_id,table2.rd_name,table2.rd_type from
table1,table2 where table1.rd_segment_id=table2.rd_segment_id) as foo
using unique rd_segment_id using SRID=89'

This is a more complex sub-query involving joining two tables. The
resulting geometry (column 'roads') has SRID=89, and mapserver will use
rd_segment_id to uniquely identify a geometry. The attributes rd_type
and rd_name are useable by other parts of mapserver.

NOTE: for the (2) case, the ' as foo ' is requred. The 'using unique
<column>' and 'using SRID=' are case sensitive.
NOTE: 'using unique <column>' would normally be the system column 'oid',
but for views and joins you'll almost certainly want to use a real
column in one of your tables.
NOTE: you'll want to build a spatial index on your geometric data:
CREATE INDEX <indexname> ON <table> USING GIST (<geometrycolumn>
GIST_GEOMETRY_OPS )
You'll also want to put an index on either oid or whatever you used for
your unique column:
CREATE INDEX <indexname> ON <table> (<uniquecolumn>)