PostgreSQL/PostGIS Views Containing Spatial Data

Single Table Views

A dynamic Overlay can be created SIS Desktop 9.1 from a a PostgreSQL/PostGIS view based on a single table.

Multiple Table Views

An overlay can be created in SIS Desktop 9.1 from a a PostgreSQL/PostGIS view based on multiple tables that have been joined.

Editing Views

Overlays created in SIS Desktop 9.1 from PostGIS views are not editable.

Registering Views

Once a view has been created it needs to be registered in the ‘Geometry_columns’ table.

Requirements

Ensure that a primary key is a part of the view definition.

Examples of Creating Views

View based on a single table using a spatial ‘WHERE’ clause to restrict data:

Copy

CREATE OR REPLACE VIEW v_bedwell AS
SELECT stevenage220610.feature_id, stevenage220610.toid, 
stevenage220610.label_text, stevenage220610.label_height, 
stevenage220610.label_orientation, 
stevenage220610.label_alignment, 
stevenage220610.symbol_orientation, 
stevenage220610.sis_fc, 
stevenage220610.geometry    
FROM stevenage220610    
WHERE stevenage220610.geometry 
&& setsrid('BOX3D(523840 223241 0,525288 224639 0)'::box3d::geometry, 27700);

View based on a single table using an attribute ‘WHERE’ clause to restrict data:

Copy

CREATE view v_stev_houses as select            
a.GEOMETRY,a.FEATURE_ID,a.TOID,a.DESCRIPTIVE_GROUPS,                
a.lABEL_TEXT,a.LABEL_HEIGHT,a.LABEL_ORIENTATION,                
a.LABEL_ALIGNMENT,a.SYMBOL_ORIENTATION,a.SIS_FC from sTEVENAGE220610 a                
where sis_fc= 10021            
and a.geometry && setsrid('BOX3D(523840 223241 0,525288 224639 0)'::box3d::geometry,27700);

View based on two tables:

Copy

CREATE view v_stev_join as select
            a.GEOMETRY,a.FEATURE_ID,a.TOID,a.FEATURE_CODE,a.lABEL_TEXT,
            a.LABEL_HEIGHT,a.LABEL_ORIENTATION,a.LABEL_ALIGNMENT,
            a.SYMBOL_ORIENTATION, a.SIS_FC,
            b.toid as b_toid,
            b.descriptive_groups b_descriptive_groups
            from stevenage220610 a, stevenage_data b
            where a.TOID = b.toid