topic Re: Is it possible to symbolize and perform analysis on related records from an Oracle database? in Geodatabase Questions

Is it possible to symbolize and perform analysis on related records from an Oracle database?

ShannonGrumbly2 — Tue, 24 Oct 2017 14:03:29 GMT

Hello!

I have an enterprise GDB in an Oracle 11.2.0.4 instance with features classes that have the unique IDs from the native Oracle database. I created a relationship class to ensure that I can link the feature classes to the Oracle data which houses additional information such as meter status (active, inactive, abandoned), customer information, etc.

My question now is can I perform spatial analysis and symbolize on the feature classes using the fields from the Oracle views? If so, how? I think I may be missing a step here.

Thank you!

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

TinaMorgan1 — Tue, 24 Oct 2017 15:23:26 GMT

Would a join help you more so than a relationship class?

Add your data to ArcMap. Right click the data that has the spatial column (the feature class that can be symbolized). In the Joins and Relates section, add the join to your table with the extra information. Once the join is created, right click the feature class again in ArcMap table of contents. Go to properties, the symbology tab, then choose Categories. Do you see your Oracle table fields in the Value Field Drop down?

I just tested this in ArcMap 10.6 beta:

Create new feature class in Oracle 11g geodatabase.

Use SQL to create an Oracle table (not registered with geodatabase), and insert data.

Add the feature class and Oracle table to ArcMap 10.6.

Create a join on my id columns, keep all records.

Open layer properties for the feature class, Symbology tab, Categories.

I can use my Oracle table fields to symbolize.

Questions/Clarity needed:

If this workflow is different from your own, please clarify.

Please also clarify if you are working with any data that is not registered with the geodatabase (Can verify this in ArcCatalog, right click the table/feature class-> Properties-> General tab...if it is not registered then there will only be 3 tabs...there will be more tabs if it is registered).

What version of ArcMap are you using?

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

ShannonGrumbly2 — Tue, 24 Oct 2017 17:43:11 GMT

Hi Tina,

Thanks for the reply.

The reason that I went with a relationship class is because our Oracle data is dynamic. For example, the status of our meters can change throughout the day. Please correct me if I am wrong, but I believe a join would instantly make that data static. The end goal here is to be able to show that a meter is active or has been abandoned almost instantly after it is entered into the Oracle database as such.

I registered the Oracle view that I am using for testing purposes in ArcMap 10.5.1 in a dev environment but I mostly use ArcMap 10.2.1 for all other purposes because we are a utility and this is what ESRI recommends. So the data is loaded in ArcMap 10.2.1 TOC.

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

Asrujit_SenGupta — Tue, 24 Oct 2017 17:58:47 GMT

Create a Join between the Feature Class and the Oracle table using a Database View and then use it in ArcMap. This will be dynamic.

If the Feature class is Versioned....use the Versioned View for the Join

If the Feature Class is not Versioned....directly use the Feature Class name (Base table) for the Join

Will this work?

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

ShannonGrumbly2 — Tue, 24 Oct 2017 18:01:37 GMT

Hi Asrujit,

Thanks for the reply!

Could you please clarify what you mean by database view? I am working with an Oracle view that was created by our DBA. Are you saying to make a view off of the existing view?

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

Asrujit_SenGupta — Tue, 24 Oct 2017 18:04:05 GMT

Yes, a View which will Join the data from the Feature Class and the View provided by your DBA.

This way, this new View will be Dynamic, showing changes instantly and you can use it in ArcMap and symbolize based on desired Fields.

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

TinaMorgan1 — Tue, 24 Oct 2017 18:14:41 GMT

You may need to take advantage of Asruji's suggestion and get the dba to create you a new view where they join the fields needed from the feature class to the database table. You may then use that view alone in ArcMap.

You have:

1. A feature class (from the dba's perspective it is a table with a spatial column)

2. An Oracle table not registered with the geodatabase (I understand you are accessing this table as a view already)

What you need:

Request from the dba to create a new view directly on the feature class and the database table. We can do away with the view of that lone table. This new view will look something like this...

CREATE VIEW test_vw
AS SELECT f.OBJECTID, t.TableColumn1,t.TableColumn2, f.SHAPE
FROM MYFC f JOIN MYDBTABLE t
ON f.OBJECTID = t.MYID;

As asrujit_pb‌ said, if MyFC is registered as versioned your DBA will simply change the MyFC syntax to show the MyFC_evw (<-always double check that name in the feature class properties via ArcCatalog)

This will give you a new view that already does the joining for you. You only need to add this view as a query layer and can directly symbolize. If you do not want to work with the query layer you can choose to register the view with the geodatabase (using your ArcDesktop 10.5.1) for a more seamless geodatabase experience with the view.

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

ShannonGrumbly2 — Mon, 06 Nov 2017 18:58:58 GMT

Are you not able to publish this view as a feature service? The view actually references a few other databases and we are running into an error when trying to publish to our ArcGIS 10.4.1 Server/Portal and received the same error when trying to publish to our dev ArcGIS 10.5.1 Server/Portal:

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

TinaMorgan1 — Mon, 06 Nov 2017 19:07:34 GMT

Feature services published to ArcGIS Server require that all layers and stand-alone tables in the map reference a single enterprise geodatabase or enterprise database. WFS-T services require that all layers and stand-alone tables in the map reference a single enterprise geodatabase using the same database connection. The enterprise geodatabase or database must be registered with the ArcGIS server. Editable image services also require a registered enterprise geodatabase.

00134: Layer's data source is not supported—Documentation | ArcGIS Enterprise

Re: Is it possible to symbolize and perform analysis on related records from an Oracle database?

ShannonGrumbly2 — Mon, 06 Nov 2017 19:19:53 GMT

Yes, I also saw that in that documentation. I was hoping that there is a solution for a view that pulls from many databases as we inherently have many which support a variety of tasks.