Multiversioned views are designed primarily to access attribute columns of a table rather than spatial columns, though it is possible to access the spatial column. Accessing the spatial column with a multiversioned view is more straightforward if you are using a spatial type, such as ST_Geometry or SDO_Geometry, to store your geometries; it is more involved if you are using a binary geometry storage type, such as the ArcSDE compressed binary or the Open Geospatial Consortium, Inc. (OGC), well-known binary types.
Solved! Go to Solution.
If that's the case, why does the documentation hint that it is possible (albeit more involved)?
SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(SHAPE),1)) FROM districts ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(SHAPE),0)) --------------------------------------- POLYGON ((10.0 10.0, 50.0 10.0, 50.0 50.0, 10.0 50.0, 10.0 10.0))
CREATE OR REPLACE FORCE VIEW VW_DEVSTAT_TEST ( SHAPE, OBJECTID, STAND_KEY, DEV_ID, DEV_DATE, DEV_STAT_DESCR ) AS SELECT FIMXXPY3.SHAPE, FIMXXPY3.OBJECTID, FIMXXPY3.STAND_KEY, DSTS.DEV_ID AS DEV_ID, DSTS.DEV_DATE AS DEV_DATE, DS.DEV_STAT_DESCR AS DEV_STAT_DESCR FROM FIM.FIMXXPY3, FIM.STAND STAND, (SELECT stat.* FROM FIM.DEV_STATUS stat WHERE (stat.stand_key, stat.dev_date) IN ( SELECT stand_key, MAX (dev_date) max_dev_date FROM FIM.DEV_STATUS GROUP BY stand_key)) DSTS, FIM.DEV_STAT DS WHERE FIMXXPY3.STAND_KEY = STAND.STAND_KEY AND STAND.STAND_KEY = DSTS.STAND_KEY(+) AND DS.DEV_STAT_CD(+) = DSTS.DEV_STAT_CD;
I followed Deepti's advice from the following post:
http://forums.arcgis.com/threads/23833-how-to-create-a-multi-versioned-spatial-view
That worked for me. It loads into Arcmap and contains the current data. However, opening the resulting spatial view in arcmap takes about 2 minutes. Once its loaded, a query will take a couple seconds so that's good.
This would be a good way to build thematic spatial layers but the slow performance in loading the table once in arcmap, is a major deficit.
Anyone have a suggestion to make it faster?
Sonia
CREATE OR REPLACE FORCE VIEW VW_SDE_DEVSTAT_TEST ( OBJECTID, STAND_KEY, DEV_ID, DEV_DATE, DEV_STAT_DESCR, SHAPE ) AS SELECT dt.objectid, dt.stand_key, dt.dev_id, dt.dev_date, dt.dev_stat_descr, b.SHAPE FROM FIM.VW_CURRENT_DEVSTAT DT, FIM.FIMXXPY3 b, (SELECT SDE_DELETES_ROW_ID, SDE_STATE_ID FROM FIM.D82 WHERE SDE_STATE_ID = 0 AND FIM.version_util.in_current_lineage (DELETED_AT) > 0) d WHERE b.OBJECTID = d.SDE_DELETES_ROW_ID(+) AND d.SDE_STATE_ID IS NULL AND FIM.version_util.get_lineage_list > 0 AND b.STAND_KEY = DT.STAND_KEY UNION ALL SELECT dt.objectid, dt.stand_key, dt.dev_id, dt.dev_date, dt.dev_stat_descr, a.SHAPE FROM FIM.VW_CURRENT_DEVSTAT DT, FIM.A82 a, (SELECT SDE_DELETES_ROW_ID, SDE_STATE_ID FROM FIM.D82 WHERE FIM.version_util.in_current_lineage (DELETED_AT) > 0) d WHERE a.OBJECTID = d.SDE_DELETES_ROW_ID(+) AND a.SDE_STATE_ID = d.SDE_STATE_ID(+) AND FIM.version_util.in_current_lineage (a.SDE_STATE_ID) > 0 AND d.SDE_STATE_ID IS NULL AND a.stand_key = dt.stand_key;