Oracle Views Containing Spatial Data

Single Table Views

SIS Desktop 9 can display data held in an Oracle view where the data is based on a single table.

Multiple Table Views

An overlay can be created in SIS Desktop 9 from an Oracle view based on multiple tables that have been joined. The view must be one that conforms to Oracle's ‘key preserved table’ requirements. This requires the base tables to have primary keys assigned to them. See "The Oracle Database Administration Guide" for further details.

A dynamic overlay can be created in SIS Desktop 9 from the view so long as the ‘rowid’ can be returned from the view in Oracle. Users can test this in SQL Plus using the following script:

SELECT rowid FROM viewname;

If Oracle does not return the rowid information the view cannot be used by SIS Desktop 9 to create a dynamic overlay. Users will be able to open the data as ‘Cached’ overlay. Users also have the option of creating the view as a table with its own spatial index, in this case a dynamic overlay can be created.

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 view: These types of views are not editable in SIS Desktop 9.

Registering Views

The following steps should be followed to ensure that the view is created in Oracle as a valid spatial object:

• Create view in Oracle.
• Register the view in USER_SDO_GEOM_METADATA table – this is a Database Administrator task.
• Assign correct privileges to the user
• Create overlay in SIS Desktop 9 using the view.

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 * FROM STEVENAGE220610  a WHERE sdo_relate(A.geometry, mdsys.sdo_geometry(2003,81989,NULL,mdsys.sdo_elem_info_array
(1,1003,3),mdsys.sdo_ordinate_array(523840,223241,525288,224639)),'mask=anyinteract querytype=window') = 'TRUE'

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

CREATE OR REPLACE VIEW  v_single_table AS
    SELECT a.*
    FROM stevenage220610 a
    WHERE a.sis_fc = 10021;

View based on two tables:

CREATE OR REPLACE VIEW v_stev_join AS SELECT a.*,b.toid b_toid, b.descriptive_groups b_descriptive_groups
    FROM stevenage220610 a , stevenage_data b
    WHERE a.toid = b.toid

---