Sunday, November 25, 2012

Tools for Comparing and Analyzing Geodatabase Schemas

Here is a suite of tools written in Python for analyzing components of a geodatabase and for finding the differences between geodatabase schemas. The tools are accessible through an ArcGIS Toolbox called SchemaInspector.tbx. They are grouped into three categories:
1. Analyzer Tools
2. Diff & Compare Tools
3. Diff Tools

There are five tools under the Analyzer Tools toolset. These tools are for printing properties of a single geodatabase component. Each tool has parameters for the input workspace and the output text file (the output text file does not have to pre-exist).
1. Analyze Domains
- Prints properties to a text file for each domain in a workspace including Domain Type, Range, Field Type, Merge Policy, and Split Policy.

2. Analyze Feature Classes
 - Prints properties to a text file for each feature class in a workspace (geodatabase or feature dataset) including Shape Type, Shape Field Name, Feature Type, Spatial Index, Has M, and Has Z.

3. Analyze Fields
- Prints properties to a text file for each selected field in a feature class including Type, Alias, Base Name, Domain, Editable, Nullable, Length, Precision, Required, and Scale.

4. Analyze Relationship Classes
- Prints properties to a text file for each relationship class in a workspace including Backward Path Label, Cardinality, Class Key, Destination Class Names, Forward Path Label, Attachment Relationship, Attributed, Composite, Reflexive, Key Type, Notification, and Origin Class Names.

5. Analyze Tables
- Prints properties to a text file for each table in a workspace including Has OID and OID Field Name.


There are six tools under the Diff & Compare Tools toolset. These tools are for comparing particular geodatabase components between two workspaces. Each tool has parameters for the two schemas to compare and the output text file.
 1. Domain Diff & Compare
- Prints the names of domains that are not common between workspaces to a text file. Searches common domains and prints domain names and properties (same properties as the Analyze Domains tool) for domains with the same name but with different properties.

2. Feature Class Diff & Compare
- Prints the names of feature classes that are not common between workspaces to a text file. Searches both standalone feature classes and feature classes in feature datasets. Searches common feature classes and prints feature class names and properties (same properties as the Analyze Feature Classes tool) for feature classes with the same name but with different properties.

3. Field Diff & Compare (by Feature Class)
- Prints the names of fields that are not common between feature classes to a text file. Searches common fields and prints field names and properties (same properties as the Analyze Fields tool) for fields with the same name but with different properties.

4. Field Diff & Compare (by Workspace)
- Prints the names of fields that are not common between all feature classes in the workspaces to a text file. Searches both standalone feature classes and feature classes in feature datasets. Searches common fields and prints field anmes and properties (same properties as the Analyze Fields tool) for fields with the same name but with different properties.

5. Relationship Class Diff & Compare
- Prints the names of relationship classes that are not common between workspaces to a text file. Searches common relationship classes and prints relationship class names and properties (same properties as the Analyze Relationship Classes tool) for relationship classes with the same name but with different properties.

6. Table Diff & Compare
- Prints the names of tables that are not common between workspaces to a text file. Searches common tables and prints table names and properties (same properties as the Analyze Tables tool) for tables with the same name but with different properties.


There is one tool under the Diff Tools toolset. This tool is called Workspace Diff and combines all of the tools under Diff & Compare toolset into a single tool. There are parameters for the two schemas to compare and the output text file.


All of the tools can be used with Basic, Standard, and Advanced license levels. The tools can be used with ArcGIS 10.0 or higher, with the exception of the Analyze Domains tool, the Domain Diff & Compare tool, and the Workspace Diff tool which require ArcGIS 10.1 or higher (to use the Workspace Diff tool on 10.0, simply comment out the lines that compare domains [361 - 387] in the ws_diff.py file).

The SchemaInspector.tbx and all of the tools described above can be downloaded from the Geospatial Database for free. Right click on the link below and select Save As... to download the zip containing the .tbx and .py files. Unzip all of the files to the same folder location (toolbox and scripts use relatives paths). The tools can be accessed and executed from both ArcMap and ArcCatalog.

Schema Inspector Toolbox

Below is an image of the Workspace Diff tool and the output text file that is created when the tool is executed.

27 comments:

  1. great contribution.
    Can you make the toolbox working?

    ReplyDelete
  2. Hello and thanks. I'm not sure if I'm understanding your question. To use the toolbox, simply download the zip file provided via the link in the blog post, then navigate to the folder that you unzipped the contents to in ArcCatalog (or the ArcCatalog window in ArcMap).

    ReplyDelete
  3. the toolbox has the toolset created. However, I cannot find the scripts inside the toolset. If I run python script directly, it works.

    THX, Sean

    ReplyDelete
  4. Okay, I see what you are talking about Sean. You must be loading these into 10.0. I created them in 10.1 and it looks like there are some compatibility issues. I'm working on creating a set specifically for 10.0 right now. I'll update the post with the link when I'm done.

    ReplyDelete
  5. Sean - I recreated the toolbox and tools in ArcGIS 10.0 and swapped out the zip file in the blog post. You should be able to access the tools from both 10.0 and 10.1 now. Thanks for letting me know about this. Let me know if you see any issues.

    Thanks again!
    Brooke

    ReplyDelete
  6. Brooke - Nice set of tools. The only issue I ran into is that the db connections folder is not populated when I choose the browse option for gdb parameter. The solution I found was to bring up the tool properties and toggle on/off the workspace parameter for gdb1 and gdb2. After that, the db connections that I had previously established show up and I can select my sde dbs for comparison. Thanks for sharing this, Mara

    ReplyDelete
  7. Hi Mara, thanks for your comment. When I wrote these tools I only tested them with file geodatabases, so I filtered the geodatabase input parameters so that they only accepted file geodatabases (I should have mentioned that in the original blog post). Anyway, I'm glad to hear they also work with SDE database connections. Thank you for testing that out for me! :-)

    ReplyDelete
  8. hi i have a problem, in my process the system say:
    : 'module' object has no attribute 'da'
    Failed to execute (WorkspaceDiff).

    ReplyDelete
  9. Are you using the tool on 10.0? The Data Access (da) module is new at 10.1, so if you're running the tool on 10.0 you'll need to comment out the lines that compare domains [361 - 387] in the ws_diff.py file.

    Hope that helps.

    ReplyDelete
  10. Is there a tool for arcgis 10.1 to synchronize the domains of 2 FGDB's

    ReplyDelete
    Replies
    1. Sorry for the late reply. I'm not aware of a tool for this, though I'm sure one could be written using Python by listing the domains for the source FGDB, then adding or removing domains where necessary from the second FGDB.

      I know I'm a month late. Hopefully you got what you were looking for.

      Delete
  11. I need a script to a fgdb or mdb to count the objects.
    The value must be in a text file
    've Had one for ArcGIS 9.3.1 but does not work in ArcGIS 10.1
    Who can help me

    ReplyDelete
    Replies
    1. This could be done very easily by looping through all of the feature classes in the workspace and running the Get Count GP tool (from the Data Management Tools toolbox) on each feature class, and printing the results to a text file. This would be a nice addition to the Schema Analyzer tool. I will try to make that enhancement soon. Do you still need this tool?

      Delete
  12. First of all, thanks to Mara for her post because I needed that change to run this on SDE. Next comment is for Brooke - does your domain analyzer take into account subtypes and domains associated with them? I am using these tools for our db cleanup and migration and that is the last portion that I have to verify. Thanks! Michelle

    ReplyDelete
    Replies
    1. Hi Michelle,

      The Domain Analyzer only prints out properties of domains in the workspace. Unfortunately the domain properties do not include subtypes associated with them. I'm not sure if there is a way to access this information via Python - it may require ArcObjects code.

      Brooke

      Delete
  13. Hi Broke,
    this tools is very useful.
    Now, I have the following error when i execture FieldDiff & Compare (I'm using AGD 10.0 with SP5):
    : 'NoneType' object is not iterable
    Failed to execute (FieldDiffCompareWS).

    Any idea?
    Regards
    Giuseppe

    ReplyDelete
    Replies
    1. I'm sorry. The complete message error is:
      Executing: FieldDiffCompareWS "\\giove\pkt305_DB\pkt305_config\Personal geodatabase\MPM_GeoDB_3.8.gdb" "\\giove\pkt305_DB\pkt305_config\Personal geodatabase\MPM_GeoDB_3.9.gdb" T:\a.txt
      Start Time: Thu Sep 05 14:46:49 2013
      Running script FieldDiffCompareWS...
      ERROR 000601: Cannot delete T:\a.txt. May be locked by another application.
      Failed to execute (FieldDiffCompareWS).
      Failed at Thu Sep 05 14:46:50 2013 (Elapsed Time: 1,00 seconds)

      Thank in advanced.
      Giuseppe P.

      Delete
    2. I'm sorry again. The correct message error is:
      Executing: FieldDiffCompareWS "\\giove\pkt305_DB\pkt305_config\Personal geodatabase\MPM_GeoDB_3.8.gdb" "\\giove\pkt305_DB\pkt305_config\Personal geodatabase\MPM_GeoDB_3.9.gdb" T:\b.txt
      Start Time: Thu Sep 05 14:48:51 2013
      Running script FieldDiffCompareWS...
      : 'NoneType' object is not iterable
      Failed to execute (FieldDiffCompareWS).
      Failed at Thu Sep 05 14:49:09 2013 (Elapsed Time: 18,00 seconds)

      Thank in advanced.
      Giuseppe P.

      Delete
    3. Hi Giuseppe,

      It's hard to tell what's causing the problem from the error message. Could you send me the two GDBs that you are trying to compare? If not, I can send you a script that will print some statements out to the GP window to try to narrow down the issue. Let's take this offline. Can you email me at brooke.reams@gmail.com?

      Thanks,
      Brooke

      Delete
    4. Hi Broke,
      I have sent you the email now from my private @gmail.com account, please have a look when you have time.

      Thanks
      Giuseppe P.

      Delete
  14. Hi Broke,
    have you examined error on my geodb?

    Thanks
    Giuseppe P-

    ReplyDelete
  15. Hi Brooke

    It doesn't work for me. The feature class compare tool, give me a list of feature classes whose don't exist in my sde data base. Any idea..?

    Thanks
    Andrés.

    ReplyDelete
  16. Hi Brooke,
    I got this error. Any idea how to fix it?
    : 'module' object has no attribute 'da'
    Failed to execute (WorkspaceDiff).

    ReplyDelete
  17. Hi Brooke

    Many thanks for the tools
    Can I use the schema tools with SDE databases?
    I tried connecting to ArcSDE 9.2 it didnt work
    Any Hints

    Thanks

    Abdullah

    ReplyDelete
  18. nice tool.
    Minor issue - tool treats field names in different cases as different. Should work like arcgis and treat them the same Invert <> INVERT

    ReplyDelete
  19. This comment has been removed by a blog administrator.

    ReplyDelete
  20. This comment has been removed by a blog administrator.

    ReplyDelete