topic Re: multiversioned views/ versioned views & Geomery in Data Management Questions

multiversioned views/ versioned views & Geomery

ChristopherGraff — Fri, 26 Apr 2013 20:48:45 GMT

At ArcGIS 10.0 and earlier we get vague documentation that we can include geometries in our multiversioned views.

here

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.

But it doesn't say how to do it. Does anyone know? I need a multiversioned spatial view using SDE binary geometry.

I notice in the 10.1 documentation the above paragraph has been removed but elsewhere it still eludes that its doable.

Any geniuses concur this?
-chris

Re: multiversioned views/ versioned views & Geomery

VinceAngelo — Fri, 26 Apr 2013 23:18:40 GMT

I can't think of any way to access geometry in a versioned view unless the
base table uses ST_GEOMETRY or native geometry storage (no SDEBINARY
or SDELOB).

- V

Re: multiversioned views/ versioned views & Geomery

ChristopherGraff — Mon, 29 Apr 2013 11:51:07 GMT

If that's the case, why does the documentation hint that it is possible (albeit more involved)?

Re: multiversioned views/ versioned views & Geomery

VinceAngelo — Mon, 29 Apr 2013 13:05:35 GMT

It doesn't hint that anymore. Trying to squeeze into an unsupported and unsupportable
configuration is not a precursor to long-term happiness (or geodatabase stability).

- V

Re: multiversioned views/ versioned views & Geomery

MarcoBoeringa — Sat, 11 Dec 2021 18:07:15 GMT

If that's the case, why does the documentation hint that it is possible (albeit more involved)?

I think the main reason the documentation hinted on this option, is that it is possible to extract a binary geometrie's coordinates using a SQL command line application, or in programming, using SQL accessor functions of the spatial type on the command line, or methods related to geometry objects in your programming language. This allows you to get to the geometrie's coordinates, and possibly loop through a set of features to process them. This isn't likely to help you out with SQL view generation though.

Just an example taken from the "Working with the geodatabase effectively using SQL" PDF from the Developer Summit 2009, showing the extraction of coordinate data as text from a Well Known Binary geometry using SQL accessor functions on the command line.

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))

Re: multiversioned views/ versioned views & Geomery

soniadickerson1 — Sat, 11 Dec 2021 18:07:18 GMT

So how do you access geometry in a versioned view when the base table uses ST_GEOMETRY?

Like many other forum postings, I created the multi-versioned view from SDE command line, but it doesn't showup spatially in Arcmap.

Why do I want to do this?
Our spatial layer is highly normalized so in order for my arcgis users to see the data in which they have interest, they have to do a join. Only problem is that what they need to join to is a query of the max(date) results - so it's best to work with that in a view. You can't join to a view and expect reasonable performance. That is why I want to create a spatial view of this.

Here's the view of it when it uses the direct featureclass (fimxxpy3) - then I register it as a spatial layer and it works well but it doesn't return the current spatial data - that's why I want to use the versioned view. If I use the versioned view in this code instead of the feature class it just gives me a table result in arcgis.

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;

If you could show me how to access geometry in a versioned view when the base table uses ST_GEOMETRY, I think I could get this to work.

Thank you so much for your insight.

Sonia

Re: multiversioned views/ versioned views & Geomery

soniadickerson1 — Tue, 04 Feb 2014 17:24:55 GMT

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

Re: multiversioned views/ versioned views & Geomery

MarcoBoeringa — Tue, 04 Feb 2014 18:16:56 GMT

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

What is the actual performance at the database side? I see a rather complex query with subqueries, is it actually performing when taking ArcGIS out of the equation?

Another question: how big is this table, how many features are there, and what step takes 2 minutes? The calculation of the spatial extent, or simply dragging it to the TOC of ArcMap and waiting for some response?

Re: multiversioned views/ versioned views & Geomery

soniadickerson1 — Sat, 11 Dec 2021 18:07:20 GMT

We are using Oracle; I didn't mention that.

Here's the script of the spatial view:

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;

vw_current_devstat is a view that contains 13941 records that results from a query from the versioned view of the main featureclass which in our case is called fimxxpy3. fimxxpy3 contains 203,099 records.

In Oracle using Toad, it takes less than a second to run a simple query on vw_current_devstat.

When querying the spatial view, VW_SDE_DEVSTAT_TEST, it takes 1 second to run a simple query. It contains 13943 records.

It seems to perform well in Oracle.

What steps takes 2 minutes? Opening the table- yesterday it took that long. Today it took about 20 seconds to open the first time and then it seems to open just as fast as any feature class after that. So I'm seeing inconsistencies with its performance.

Yesterday, dragging it to the arcmap view, took about 20 seconds to load and draw and today it seems to work well.

The performance issues could have been due to accessing the layer when another process was running on the database.

I appreciate any insight you might have on this.

Sonia

topic Re: multiversioned views/ versioned views & Geomery in Data Management Questions

multiversioned views/ versioned views &amp; Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

Re: multiversioned views/ versioned views & Geomery

multiversioned views/ versioned views & Geomery