Querying multiple SDE Databases

1652
7
01-24-2014 12:05 PM
Highlighted
New Contributor II
Our office has multiple SDE databases on an SQL server.  Each SDE database represents a County within our state and each County SDE Database has the same feature classes and attributes table parameters.

Is there an existing tool or a script that you use which will allow us to querry all of our county databases at one time for a specific attribute total or value?

Example:  We want to determine the total acrage of State Forest.  Each county database has a feature class called 'state_forest_area' and in that feature class is the attribute column for 'total_acres'.  What we would like the tool/script to do is access each County Database and pull the total acres from each 'state_forest_area' feature class and total them.  OR at the least, have an output of all the 'state_forest_area' records, then we could total them ourselves.
0 Kudos
7 Replies
Highlighted
MVP Regular Contributor
If all the databases exist within the same SQL Server instance, it is possible to execute cross-database queries if you fully qualify all of your tables within the SQL.
0 Kudos
Highlighted
Occasional Contributor III
also, you can check this: "Administer Multiple Servers Using Central Management Servers":http://technet.microsoft.com/en-us/library/bb895144.aspx
0 Kudos
Highlighted
New Contributor II
OK... i guess i should have mentioned we are VERY new to SDE, SQL Server, etc.  I've attached a screen shot of the top of our catalog tree.  This is only the first 5 databases, but you get the idea; they are all listed like this.

Would we be able to write a python script, or something similar, to check each database for a specific feature class or attrbute value?

I would think that this is a common query other users would use; am i wrong?
0 Kudos
Highlighted
Occasional Contributor III

I wonder if you could create a view on one database that references all those databases that you would like to query against?

Or perhaps the view contains the query?

0 Kudos
Highlighted
Esri Esteemed Contributor

You could try something like this:

import arcpy

import os

# specify your folder with the .sde connection files

sde_folder = r"C:\Users\xbakker\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog"

arcpy.env.workspace = sde_folder

# list the sde files

sdes = arcpy.ListWorkspaces("*", "SDE")

# define featureclass name and field name

fcname = "state_forest_area"

fldname = "total_acres"

flds = (fldname)

total_acres = 0

# loop through the sde files

for sde in sdes:

    try:

        sde_path, sde_name = os.path.split(sde)

        fc = os.path.join(sde, fcname) # assuming data is in sde root and not in a feature dataset

        lst_acres = [r[0] for r in arcpy.da.SearchCursor(fc, flds)]

        county_acres = sum(lst_acres)

        total_acres += county_acres

        print "County '{0}' has {1} acres '{2}'".format(sde_name[:-4], county_acres, fcname)

    except Exception as e:

        print "Error: {0}".format(e)

        print "check existence of the featureclass and field in the current sde connection"

print "Total acres: {0}".format(total_acres)

Kind regards, Xander

0 Kudos
Highlighted
Esri Esteemed Contributor

Since it's still early in deployment, you should reconsider the multi-database model, and evaluate putting all the data in a single database, either by using multiple county users, or by making one comprehensive database and using COUNTY_CODE to restrict mapping by county.  Cross-database operation will make access to the data for statewide mapping as difficult and inefficient as possible.

- V

Highlighted
Esri Esteemed Contributor

Vince Angelo‌ is absolutely right! (+1 for that) It is better to get things right, create a structure that is durable and enables analysis and statistics rather than start with all types of customization to get results you could obtain without customization if your schema was set up correctly. Invest now in setting up your data correctly, the ROI will be high.

0 Kudos