Using spatial views on tables with an ST_Geometry column

Spatial views are database views that include a geometry column.

One use for spatial views is to eliminate extra spatial columns so you can register the view with ArcSDE. Since you cannot register spatial tables that consist of more than one spatial column, using a spatial view of the table is your only option if you want to register with ArcSDE.

When you define the view, you select only one of the spatial columns to include in the view. Then you can register the view with ArcSDE.

The following is an example of creating a spatial view, then running a query on the view.

Creating a view with one spatial column

CREATE VIEW quake_v 
AS SELECT objectid,shape 
FROM quakes4;

In this example, a spatial view is created, and a spatial join is created on that view.

CREATE VIEW san_berdoo_quakes_v 
AS SELECT a.objectid, a.location, b.name
FROM quakes4 a, st_counties b
WHERE b.name = 'San Bernardino'
AND st_intersects(a.location,b.boundary)=1;

The following is the same example for a geodatabase in Informix:

CREATE VIEW san_berdoo_quakes_v 
AS SELECT a.objectid, a.location, b.name
FROM quakes4 a, st_counties b
WHERE b.name = 'San Bernardino'
AND st_intersects(a.location,b.boundary);

Selecting from the view

SELECT COUNT(*) 
FROM san_berdoo_quakes_v;

Creating spatial materialized views

A materialized view is a database object that contains the results of a query. These objects are typically used in Oracle Materialized View (or Snapshot) advanced replication. In DB2, materialized views are known as materialized query tables.

CREATE MATERIALIZED VIEW quake_mv 
AS SELECT objectid,shape 
FROM quakes4;

DB2

CREATE TABLE quake_mv as (SELECT objectid,shape FROM quakes4) 
DATA INITIALLY DEFERRED REFRESH DEFERRED 
MAINTAINED BY USER 
SET INTEGRITY FOR quake_mv MATERIALIZED QUERY IMMEDIATE UNCHECKED

Selection on the materialized view

SELECT COUNT(*) 
FROM quake_mv a, st_counties b 
WHERE b.name = 'San Bernardino' 
AND st_intersects(a.shape,b.shape)=1;

Note:

You cannot create a materialized view like this in Informix. It is just a view that requires temporary tables to be created when it is viewed. For instance, if you use a union of many tables to create a view, temporary tables must be created by Informix when Querying this view. There is no equivalent in Informix.

As with ArcSDE feature classes stored in the LOB storage format, spatial type layers are also supported using Oracle Materialized View replication. The DB2 replication suite allows spatial data replication but does not have an equivalent to Oracle Materialized View replication.

For more information about using multiversioned views and other database views with ArcGIS, see the following topics:

Database viewsWhat are multiversioned views?

.