SQL Server Views Containing Spatial Data

Single Table Views

A dynamic Overlay can be created in SIS Desktop 9 from a SQL Server view based on a single table.

Multiple Table Views

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

Editing Views

There are rules as to when data in a view can be edited.

Single Table Views: If the user has editable access to the base table they can in theory edit the view so long as the correct privileges that have been assigned to the view and data constraints/integrity on the table are honoured.

Multi-Table Views: These types of views are not editable in SIS Desktop 9.

Registering Views

There is no requirement to register a view for SQL Server 2008.

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 VIEW v_bedwell AS
    SELECT  a.* FROM dbo.STEVENAGE220610 a
    WHERE (a.geometry.STIntersects
	(geometry::STGeomFromText
('POLYGON((523840 223241,523840 224639,525288 224639,525288 223241,523840 223241))',0))=1)

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

CREATE view v_single_table AS SELECT a.*
     FROM dbo.STEVENAGE220610 a
     WHERE a.sis_fc = 10021

View based on two tables:

CREATE view v_stev_join AS SELECT
     a.GEOMETRY,
     a.FEATURE_ID,
     a.TOID,
     a.VERSION_DATE,
     a.lABEL_TEXT,
     a.LABEL_HEIGHT,
     a.LABEL_ORIENTATION,
     a.LABEL_ALIGNMENT,
     a.SYMBOL_ORIENTATION,
     a.SIS_FC,
     b.toid as b_toid,
     b.id
     FROM dbo.STEVENAGE220610 a, dbo.stev_toid_only b
     WHERE a.toid = b.toid

---