In very general terms, how do I give an exisitng Oracle DB a spatial component

[DaveAugspurger]

Our organization recently upgraded to ArcGIS for Server.  In the past, I maintained 3 important feature classes (2 point features and 1 polyine feature) in a File Geodatabase.  Now that we have Server, I have moved 2 of the feature classes from the file gdb to a SQL Enterprise database (sde) so that I can edit and maintain those 2 features.  The remaining feature is our Customer list.  The mast customer list has been and still is maintained in an Oracle database.  That data was periodically exported and then imported into the File Geodatabase and geocoded in order to show the customer locations on a map.

I don't maintain the customer database.  However, I do still need to show the locations on maps.  With Server, I am thining I should be able to connect to the Oracle Database and then add that data to the map that way.  What I don't know is just how to go about this...

Does the Oracle db get a spatial component added to it?  Or, is there a feature added to the SQL Enterprise db and then connected to a common field in the Oracle db?

i am very new to Server, Oracle, and SQL so any advice would be greatly appreciated.  I'm just trying to first get a feel for the general way this should work....then figure out the how-to's of it.

Thanks!

[MarcoBoeringa]

Sounds to me you actually don't want a static spatial layer added or imported to the ArcSDE managed SQL Server DB, based on your customer list in the original Oracle DB, but just a dynamically geocoded one in ArcMap based on those addresses (maintained by your colleagues), so that it is current at any point in time.

For that, you don't need ArcSDE or ArcGIS for Server, you only need access to the original table with customer addresses in ArcGIS. If on ArcGIS 10.0 or below, that can be accomplished by creating an OLE DB connection to your database. First create an ODBC data source using the Windows tools available for that and than use ArcCatalog's "Add OLE DB Connection" option to add a connection to this ODBC data source (expand the "Database Connections" option). If you personally don't know how to setup an ODBC data source in Windows, ask your local network administrators / IT staff, they will definitely be able to help you out. Please note that in ArcGIS 10.1, the "Add OLE DB Connection" option is still available, but no longer by default visible in ArcCatalog. This Help page states how to add it in 10.1: http://resources.arcgis.com/en/help/main/10.1/index.html#//006600000452000000

In 10.1 you may also try to simply connect to the original Oracle DB by using the generic "Database Connection" dialog that replaces the previous options under "Database Connections", and access the table from there. This option doesn't require setting up an ODBC data source, but it may require a database client installed. See this Help page for more information about that dialog: Database connections in ArcGIS for Desktop

After you created either an OLE DB or normal Database Connection, you still need to add the data itself. This can be accomplished by either simply opening the connection in ArcCatalog and dragging a visible table "as is" to ArcMap, or using the Query Layer tool in ArcToolbox for more sophisticated SQL based selection query layers. See What is a query layer? in the Help.

Using this method, you should be able to setup a fully dynamic geocoded layer, which will be current at any point in time (e.g. each time you open ArcMap, the layer will reflect the edits of your colleagues).

By the way, be aware there is a Windows 7 issue with setting up ODBC data sources that at least some people including myself have run into. See the details here in a thread I started.

[DaveAugspurger]

Thank you, that is very helpful.  I'll talk to our IT folks and see what we can accomplish. 

One more question (and maybe it will become obvious) but how do I get the customers in the Oracle db to show up as points in a map?  I'm used to just geocoding addresses from a table, would this be the same or will I have to incorporate some sort of X/Y coordinate field to the Oracle db?

Thanks again, I really appreciate the response.

[MarcoBoeringa]

It ought to be exactly the same, as you are just working with a table. Only difference is that you access it directly from the Oracle DB, instead of with some export.

*** EDIT ***:

There may be some obstacle with having a fully dynamic solution. At least it seems you need a relationship class for full automatic re-matching, see the text below from one of the Help pages:

"Alternatively, a relationship class between the input address table and output feature class can be created so that edits to the addresses in the input address table are automatically updated in the output feature class. This option is supported only if the input address table and output feature class are in the same geodatabase workspace."

This requires the original table also to be part of an ESRI Geodatabase. That may require Enable Enterprise Geodatabase on the existing database that contains the address information. I don't know if that is acceptable in your shop.

To make such a relationship class automatically, if your addresses are indeed part of an ESRI Geodatabase or can be made so by Enabling, you can simply choose the "Create dynamic feature class related to table" option, see the text below from this Help page.

"6. Select the option Create static snapshot of table inside new feature class or Create dynamic feature class related to table.

The Create dynamic feature class related to table option is only available if you have an ArcGIS for Desktop Standard or Advanced license and the input address table and output feature class are in the same geodatabase workspace."

[DaveAugspurger]

I think IT 'mirrored' the customer database (Oracle) in a SQL table within my Enterprise Geodatabase (sde).  I can see the data as a table within my main Enterprise Geodatabase now and they have populated it with 60 sample records.  The intent is to fully populate it once we have it working, then just update nightly any changes.

That's the theory.  The reality is I'm stuck at getting it geocoded.  I tried to dynamically geocode and was informed I had to register the table.  So, I go to the table, right-click and select Register and get this error:

[ATTACH=CONFIG]22293[/ATTACH]

I'm not sure if this is something that can be corrected or maybe the manner in which we are trying to do this (mirroring the Oracle in SQL) is not going to work.

Any ideas?

[MarcoBoeringa]

I'm not sure if this is something that can be corrected or maybe the manner in which we are trying to do this (mirroring the Oracle in SQL) is not going to work.

Any ideas?

Dave, this is probably as far as I can get you, I think it is time Vince Angelo of ESRI or one of the other ESRI folks joins the thread to shed light.

Yes, there may be an issue with the current manner you are trying this, as I think that, to register your table with the geodatabase, most likely requires some database rights to the SDE admin user that your "mirrored" table may not provide, like being able to add a geodatabase ObjectID column.

See this Help topic also:
Registering a table with the geodatabase

But again, I hope some others will join the thread that may be able to help you out here. There should be some possibility, especially if you are already able to see and access the table in ArcGIS.

[MarcoBoeringa]

I also see the error message talks about "table attachments". Maybe excluding some offending columns containing ESRI unsupported attachments / column types from the "mirror" your IT folks created, will solve the issue of not being able "register with geodatabase".

By the way, as it not entirely clear from your posts, is your own "enterprise geodatabase" also in Oracle like the customer database, or in Microsoft SQL Server?

[DaveAugspurger]

I think we might be close.  The Enterprise gdb is a SQL db.  The IT person that is helping created a new SQL table in that gdb and connected it to the Oracle db.  I can see the SQL table in ArcCatalog and am able to Geocode it selecting the Dynamic option.  For the test, we populated the table with 60 records then geocoded it.  We then added 14 additional records.  Now trying to figure out how to get those to automatically geocode.  I think what you said before regarding creating a relationship class is what I need to do to accomplish this.  I'll see what I can accomplish on that end but I'm still a little concerned that this isn't the 'preferred' method to do this.  If this fails I will try to convince IT to 'enable geocoding' on the Oracle db.

Thanks for all the help, I really do appreciate it.

[MarcoBoeringa]

I think we might be close.  The Enterprise gdb is a SQL db.  The IT person that is helping created a new SQL table in that gdb and connected it to the Oracle db.  I can see the SQL table in ArcCatalog and am able to Geocode it selecting the Dynamic option.  For the test, we populated the table with 60 records then geocoded it.  We then added 14 additional records.  Now trying to figure out how to get those to automatically geocode.

The "Create dynamic feature class related to table" option should create a relationship class automatically, if I understood the Help page I referred well. So you theoretically should not have to manually create one any more if the geocoding operation successfully finished.

Success with further tries. Hope you get it right.