Using spatial views on tables with an ST_Geometry column
Using spatial views on tables with an ST_Geometry column
|
| Release 9.3 |
   |
Note: This topic was updated for 9.3.1.
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 qua
kes4;
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
2 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 for 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 Using database views as well as the Working with the Geodatabase Using SQL white paper located at http://downloads.esri.com/support/whitepapers/sde_/GeodatabaseUsingSQL_2_30mar06.pdf.
