Querying multiple SDE Databases

[MatthewDuke]

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.

[WilliamCraft]

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.

[EmadAl-Mousa]

also, you can check this: "Administer Multiple Servers Using Central Management Servers":http://technet.microsoft.com/en-us/library/bb895144.aspx

[MatthewDuke]

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?

[PaulLohr]

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?

[XanderBakker]

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

[VinceAngelo]

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

[XanderBakker]

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.