Using spatial views on tables with an ST_Geometry column |
|
Release 9.3 |
CREATE VIEW quake_v AS SELECT objectid,shape FROM quakes4;
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;
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);
select count(*) from san_berdoo_quakes_v;
create materialized view quake_mv as select objectid,shape from qua
kes4;
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
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.