PostgreSQL/PostGIS Views Containing Spatial Data

Single Table Views

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

Multiple Table Views

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

Editing Views

Overlays created in SIS Desktop 9 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:

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:

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:

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

---