topic Re: Create Database View 10.1 (Oracle, ST_Geometry, Feature Class) in Data Management Questions

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

SandraLuken — Mon, 28 Jul 2014 21:15:50 GMT

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

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

VinceAngelo — Mon, 28 Jul 2014 22:08:14 GMT

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

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

SandraLuken — Tue, 29 Jul 2014 12:34:10 GMT

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.

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

AsrujitSengupta — Tue, 29 Jul 2014 13:52:42 GMT

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

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

VinceAngelo — Tue, 29 Jul 2014 16:13:59 GMT

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

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

SandraLuken — Tue, 29 Jul 2014 18:05:34 GMT

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.

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

AsrujitSengupta — Tue, 29 Jul 2014 18:08:14 GMT

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.

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

VinceAngelo — Tue, 29 Jul 2014 18:34:43 GMT

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

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

SandraLuken — Tue, 29 Jul 2014 20:26:29 GMT

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.

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

VinceAngelo — Tue, 29 Jul 2014 21:04:43 GMT

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

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

AsrujitSengupta — Tue, 29 Jul 2014 21:47:04 GMT

Just to add to what Vince already mentioned, that View created using ArcGIS Desktop can be previewed in ArcMap w/o any issues. Basically thats going to act as a Query layer,as he pointed out, but will work fine.

Incase this helps or thats what you needed.

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

SandraLuken — Wed, 30 Jul 2014 20:23:04 GMT

Thank you to both of you for your time and effort to help clarify things. In the end this comes down to a difference in expected behaviour based on what is written in the document. Short answer, ArcGIS cannot do what I want/expect it to do when it comes to Spatial Views without the involvement of the SDE command line. Perhaps in a future release.

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

ForrestJones — Thu, 31 Jul 2014 17:19:45 GMT

Hi Sandra,

This should work. A few things to check:

-Are you using an application server connection? Try using a direct connection instead. This might be the problem.

-Are there any errors in the sde error log? For direct connections it will be on your arcgis client machine in the %temp% directory (app server in sdehome\etc)

-If it still fails and there is nothing helpful in the sde dc error log, I would suggest opening an incident so we can look into it further.

Thanks.