How To Create a Multi-versioned Spatial View

[DeeptiVB]

Environment: ArcGIS 10, SDE 10, SQL Server 2008 R2

The way we go about creating multi-versioned spatial view for feature class with Geometry storage for Shape is like this,
1.Create multi-versioned view from SDE command line.
2.Copy the code to notepad, the code is mainly to filter the data from different versions and get the delta table names right.
3.Delete the multi-versioned view.
4.Create a normal (non-multiversion) view from command line
5.In SQL Management Studio, open the normal view design, paste the sql code of multi-versioned view from notepad, plus any other columns we might want to include and there we have our multi-versioned spatial view.

It seems to be a roundabout process to me. If we just create a multi-versioned view, it doesn't show up "spatially" in ArcCatalog or ArcMap.

I found the quote below, 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.

The page doesn't say how you can access the column, except saying one way is more involved than the other. If there is a right way or better way to create a multi-versioned spatial view that can be viewed spatially in ArcMap or ArcCatalog, that's what I am looking for.

Thank you,

-Deepti

[ForrestJones]

Hi Deepti,

Multi-version views are meant to be accessed outside of ArcMap and ArcCatalog through SQL or applications that are using SQL to access the geodatabase. To access the versioned featureclass (including the shape column) in ArcMap or ArcCatalog, you just use the featureclass itself and connect to the version you'd like to access. Does that make sense?

Is there another specific reason why you are trying to access a multi-version view in ArcMap and ArcCatalog instead of using the featureclass?

Thanks,

[DeeptiVB]

Hi Deepti,

Multi-version views are meant to be accessed outside of ArcMap and ArcCatalog through SQL or applications that are using SQL to access the geodatabase. To access the versioned featureclass (including the shape column) in ArcMap or ArcCatalog, you just use the featureclass itself and connect to the version you'd like to access. Does that make sense?

Thanks,

Makes very good sense, what you said above is one good argument that I can use. I need to do some tests before I can answer your question as to why I need a mv_view. Thank you.

[DeeptiVB]

Hello:

We are linking to a remote server(non-spatial, sql server) using primary key from our feature class and creating views. I created a multi-versioned view and a normal view for the same feature class on the master version of the SDE database.

One interesting thing is that the same feature class when viewed in SQL Server as a table has lesser number of records than when its attribute table is viewed in ArcCatalog. Any explanations?

This difference is also reflected in the multi-versioned and normal view. Multi-versioned view brings back all records even though they are not visible in the table in sql server and the Normal view just joins whatever records sql server can see. Our cartographers want all the records, they are not sql back-end users, they want a view to bring back everything ArcCatalog sees which multi-versioned view achieves.

Can anyone provide an explanation for this missing record phenomenon in SQL Server.

TIA

-Deepti

[JonHall]

The "missing" records are versioned edits in the "A" tables - running COMPRESS on ArcSDE moves those to the "base" tables that you are viewing in SQL Server.

You should also see "extra" records in the base table, for any records that have been deleted, which are in the "D" table.

The mv view queries those "missing" records into the view - that's what is special about mv views.

Using SSMS, look-up the name of your table in the SDE_Table_Registry table, and get it's registration_id.
Then, for example, if the registration_id is 164, using SSMS, take a look at the A164 table for "missing" records, and D164 for "extra" records that haven't been deleted yet from the base table.

There is a wealth of info on these subjects in the Help content for ArcGIS - the forums are a quick-fix, but you ought to study the documentation more thoroughly, to get a better understanding of how versioned editing in ArcSDE works in the underlying DBMS.

[DeeptiVB]

The "missing" records are versioned edits in the "A" tables - running COMPRESS on ArcSDE moves those to the "base" tables that you are viewing in SQL Server.

You should also see "extra" records in the base table, for any records that have been deleted, which are in the "D" table.

The mv view queries those "missing" records into the view - that's what is special about mv views.

Appreciate it. What you explained above helps me describe why we need multi-versioned views to the gis user I am dealing with and also makes my understanding better. I inherited an SDE database last month, on which no administration has been done after it was created, which means no compression, no house-cleaning, which necessitated the use of multi-versioned views for GIS business users. I hope once I get done with the house-cleaning and regularly 'administer' the database we can go back to using normal views.

There is a wealth of info on these subjects in the Help content for ArcGIS - the forums are a quick-fix, but you ought to study the documentation more thoroughly, to get a better understanding of how versioned editing in ArcSDE works in the underlying DBMS.

I did read thru' most of the ArcGIS 10 documentation for Geodatabases in SQL Server, didn't find anything explaining clearly about delta tables. After your comment I did a google search for delta tables in ArcSDE and landed at the developer documentation for SDE 9.x  which has a ton of information I need. I was concentrating my search for SDE 10, that was a mistake.


Thanks Jon,

-Deepti

[ForrestJones]

I did read thru' most of the ArcGIS 10 documentation for Geodatabases in SQL Server, didn't find anything explaining clearly about delta tables.

Hi Deepti,

Does this help from the 10 doc?

Versioned tables in a geodatabase in SQL Server

[DeeptiVB]

Hi Deepti,
Versioned tables in a geodatabase in SQL Server

Thanks Fojo. I did read that link SDE 10 link during my initial searches. It helped me explain to our business GIS users that part of their data is stored in delta(d) and alpha(a) tables in addition to the base table, though the arc client sees a composite of all of these. This was before Jon had posted his reply. But I found that 9.2 developer help(which I came across yesterday) was much more descriptive and self-explanatory to me.

In addition to d and a tables there are f, i and s tables. Any url explaining that?

TIA

-Deepti