Create Database View 10.1 (Oracle, ST_Geometry, Feature Class)

895
12
07-28-2014 02:15 PM
Highlighted
Occasional Contributor

According to the online help documentation, it seems possible that a spatial view against an Oracle/non-versioned/registered with the GDB/st_geometry/feature class is possible. The view creates without error, but cannot be viewed afterwards, giving a generic 'cannot display' error message. The user that is attempting to create the view has create view privileges.

Are we mis-reading the help documentation? Is this possible with our configuration? Sure we know that spatial views can be created at the command line, but are more interested in the desktop tool solution rather then the soon to be deprecated command line tools.

ArcGIS Desktop 10.1 SP1 (no QIP)

ArcSDE 10.0 SP5

Reply
0 Kudos
12 Replies
Highlighted
Esri Esteemed Contributor

It's certainly possible, but you need to create the view with all required components, and register it properly.

There are no supported tools to do this at 10.1sp1 or 10.2.x, so you *must* use command-line, and given

the elderly ArcSDE release, you'd be best doing this with the tools that correspond to that release.

In order to explain what is happening, and where things have gone wrong, you'd need to provide information

on what you are attempting.  That would include SQL*Plus DESCRIBE output of the table(s) involved in the

view, 'sdetable -o describe' and 'sdelayer -o describe_long' output of the table with the geometry, the exact

SQL you've used to create the view, the exact command you used to register the view, and the output of

'sdetable -o describe' and 'sdelayer -o describe_long' on the view.

- V

Reply
0 Kudos
Highlighted
Occasional Contributor

Apologies that I wasn't more clear in the original posting, we are attempting to create a spatial view through either the GP tool (Create Database View.) or geodatabase context menu (New | View).  

We're following these directions, which indicate that a spatial view can be created provided the data is not using binary geometry (we are using ST_Geometry).

ArcGIS Help 10.1‌ - Creating a database view in ArcGIS Desktop

ArcGIS Help 10.1‌ - A quick tour of views in the geodatabase

From what you have outlined, that spatial views can only be created from the SDE command line, so we have misunderstood the help documentation.

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

Sort of.  Those tools create views in the database.  They do NOT register those views with ArcSDE.

If you want the views registered with ArcSDE (as geodatabase objects, not as simple Query Layers),

you must use ArcSDE command-line to register the view(s).

The rules for view creation remain the same:

  • Must have a rowid column, which is 32-bit INTEGER,NOT NULL, with distinct and repeatable values
    (no ROWNUMs allowed),
  • Many-to-one and one-to-many allowed ONLY if the resulting rowids are unique (choose rowids
    from the many side).
  • Can only contain one geometry class (point/line/poly)
  • The source and result projections must be the same

You'd still need to provide a complete description of what you have attempted, and what error(s) occurred

before we could help you.

- V

Reply
0 Kudos
Highlighted
Regular Contributor III

Have you tried creating views on any other Feature Class? Are you observing the same issue for all the feature classes?

In case you have SDE command line installed, and used it to create the View...does that work ( I know you are trying to use the GP tool, but just to confirm).

Reply
0 Kudos
Highlighted
Occasional Contributor

Here is the complete workflow:

- use ArcCatalog (Standard, 10.1, SP1)

- navigate to SDE Oracle 10.0 SP5 geodatabase- right click | New | View

- enter in view name (i.e., SL_TEST_SV)

- enter in view criteria such as 'select * from FS_TreeInventory' or 'select oid, shape, treeid from FS_TreeInventory'

     (this is a point feature class, registered with the geodatabase, not versioned, stored using ST_Geometry)

- we have also tried with line and polygon feature classes in the same and other geodatabaseswith the same configuration mentioned above

- the view creates without error and appears in the geodatabase listing

- attempts to preview the view return the following error message 'Operation Failed, Error Opening Feature Class'

If the same process is repeated on a table, and not a feature class, or the query does not involve the shape column, the view is created and can be previewed.

If we right click on the resulting spatial view, the option to register with the geodatabase is greyed out/unavailable. Another source of confusion as either this implies that this spatial view cannot be registered with the geodatabase, or it already is.

Reply
0 Kudos
Highlighted
Regular Contributor III

a quick note: "Registered with geodatabase" and "Registered with SDE" are two different things. What Vince had mentioned previously, involved "Registered with SDE", which doesn't happen if you us the GP tool.

Highlighted
Esri Esteemed Contributor

Are you attempting to access the view as a Query Layer or within the Geodatabase connection? If it's the latter, the table must be registered with ArcSDE.

Views can't be registered with the geodatabase at all (they cannot participate in feature datasets, etc. because they cannot carry an SDE-set rowid), so the registration option being greyed out should not alarm.

- V

Highlighted
Occasional Contributor

As feared, this comes down to a misunderstanding of the Help documentation. From what everyone has indicated in this thread, a spatial view created from ArcGIS Desktop will not work, unless the sde command line operation to register it with SDE is executed. Sigh. Appreciate the assistance, and Help clarification.

Reply
0 Kudos
Highlighted
Esri Esteemed Contributor

The view should work as a Query Layer from a 10.1 client, just not through a geodatabase connection

as a simple feature class.

- V