Best way to join to multiple databases

2973
8
12-13-2016 02:10 PM
DavidBuehler
Occasional Contributor III

I am using 10.3.1 and will be moving to 10.4.1 shortly.  My goal is to create as many "live" layers as possible by integrating to other SQL databases, and then pushing out services for use in web maps.  Example: ParcelID on the feature in the enterprise geodatabase is joined to GISID for tax rolls in the tax database.  When GISID's information is updated it permeates through to the web map.

I would like to use spatial views, which I know can be written if the data resides in the same database.  Is registering the tables from Database 1, Database 2, etc. and then making the spatial views in the enterprise geodatabase the way to go?  Any limitations or impacts on the data in the original database?  Or is there a better way?  Is it not possible?  What is the best course of action?

0 Kudos
8 Replies
Asrujit_SenGupta
MVP Regular Contributor

If the Data that you need to join are in separate Databases, you can check Cross-Database Views. That should be dynamic.

However check links and test it for efficiency and performance before implementing.

arcsde - Create spatial view that references multiple databases - Geographic Information Systems Sta... 

DavidBuehler
Occasional Contributor III

Thank you for sharing that link.  This might be a basic question, but how does the "Register with Geodatabase" tool know to register the table from database 2 with geodatabase1 through connection xyz?

0 Kudos
DavidBuehler
Occasional Contributor III

Here is an example set up  Test1 has the tables that contain ownership and GIS1 is where the parcels are.  I do not think this is possible to have a live connection, less they were both in GIS1 or both in Test1.

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

You will need to create a new Cross Database View in 'Test1' which will display the required data from Tables in Test1 and also join it to the required data pulled from the other database GIS1. This view can be Spatial or Non-spatial, depends on what you need.

So now that you have created the new View, you can register this. The View will get registered in the Geodatabase where it was created, in this case Test1.

NOTE: Create the Cross Database View from SQl Server end and make sure first that the data that you need is correctly showing up.

BrianFausel
Occasional Contributor III

I'd be curious to hear feedback about this question as well, especially in light of the 10.5 "Register with Database" options that are now available.  I'm still working primarily with ArcMap 10.2.2/10.3 and SQL Server 2008 R2.

Generally speaking 1:1 relationships work pretty well in spatial views.  One source of problems that I have encountered when joining external databases is when you attempt 1:M scenarios.  The "Tax Database" (external business database) lacks an Esri-recognized ObjectID.  When you create a view between an Enterprise geodatabase feature class (which has ObjectID) with an external business database (no ObjectID) you will encounter odd results, at least in ArcMap.  The attribute table may display the correct number of total records, but when you attempt selections and click "show only selected results" you will only see 1 record returned when there is a 1:M happening.  The attribute table will even tell you you have multiple features selected but only 1 will display.

I worked with Esri tech support on this and they did end up creating a bug related to this problem that is still open:

#BUG-000086132 The Select By Location tool does not return expected results when a table contains a join. **

** Though the description is partially misleading - it should read "The Select By Location tool does not return expected results when querying a spatial view that contains Enterprise geodatabase feature class and external business database table."

To get around this, I have explored ETL methods to truncate/append the data of interest into the Enterprise geodatabase.  This creates an ObjectID, and then the spatial views work properly.  Unfortunately, this solution does not allow for live data, and also creates redundant copies of data.

Although a bit dated, this thread offers more discussion.

0 Kudos
GIS_Spellblade
Occasional Contributor

Hi Brian,

I was wondering if you had any further experience with this subject since you posted in 2016. I've been running an ETL that uses a cross-database query to create tables directly into the back-end of the enterprise geodatabase. Now that we are upgrading past 10.3, I was looking forward to registering views with the geodatabase to have more live data and to also get past back-end manipulation.

I've been playing around with the register views tool today and I don't seem to be able to do the same kind of cowboy queries that I've been getting away with via the back-end create table statements.

0 Kudos
CraigSwadner
Occasional Contributor

We have dozens of queries / views, 10.7.1

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

GIS_Spellblade
Occasional Contributor

I definitely need to pick your brain, I'm going to send an email.

Edit:

I sent a LinkedIn request, you can reach me at my work email [jcarmona@mckinneytexas.org]

0 Kudos