File Geodatabase Admin


My data is slow. In particular the draw times are terrible and the processing times are poor. I have so much data that I wonder if there are things I can do at the database end to make it run faster. I don’t have an enterprise solution, I need a desktop solution.

Never fear, there’s a few best practices to optimise your data so it’s smarter and therefore faster:

  1. Do not use Shapefiles or Personal Access Geodatabase
  2. Use the File Geodatabase
  3. Upgrade Your Old File Geodatabase
  4. Add and Calculate the Spatial Index Grid
  5. Iterate Compacts to free orphaned lock files & defragment files
  6. If you don’t edit it, compress it
  7. Iterate the repair tool on all feature classes
  8. Use an attribute index where appropriate
  9. Understand resolution and tolerance
  10. Standardise your projections and datum’s
  11. Manage your path names and conventions

1) Do not use Shapefiles or Personal Access Geodatabase
Shapefiles and Personal Access Geodatabase are outdated. They have many restrictions due their antiquated nature. They need to be respected for their vintage when used. Shapefiles should conform to the short naming convention and the ascii naming schema. Secondly the shapefile was never designed for much more than display, it does not handle geoprocessing operations well as it lacks resolution and tolerance.

2) Use the File Geodatabase
For non enterprise projects use the File Geodatabase:

  • It holds more data per: point, line and polygon (between 1 & 500+ terabytes)
  • It has no over all database size limit
  • It stores the data in less space (sometimes up to 70% less space[see below])
  • The database has behaviors such as: Domains, Subtypes and Topologies (see below)
  • The lines and polygons have computer defined fields that dynamically display area, perimeter and length
  • You can drag and drop between all GDB
  • The projection is more secure than a shapefiles  prj file
  • It supports high precision (sub millimeter)
  • The resolution and tolerance can be controlled (see previous blog)
  • The File GDB is faster (it the faster than all the enterprise GDB’s)
  • Best of all the File Geodatabase has administration tools

 3) Upgrade Your Old File Geodatabase
Every version of ArcGIS comes with a re engineered File Geodatabase. Each release makes it better, stronger and faster. For example the ArcGIS 10 File GDB can store true 3D geometry. Note however that older versions of ArcGIS typically cannot read newer File Geodatabase.

To upgrade. Right click on the File GDB > Properties > General Tab > Upgrade Geodatabase button.

You will find geoprocessing tools for upgrading File Geodatabase and for exporting File Geodatabase to older versions. This comes in handy if you regularly receive older data formats, you can run an upgrade as a part of a model builder model that quality assures your data.

4) Add and Calculate the Spatial Index Grid
There is an efficiency concept called the “spatial index grid”. The spatial index should be calculated for every featureclass in the geodatabase.  Essentially it applies a grid over the top of the features and creates a relationship between the grid and the underlying features. Queries then pass through the relationship before interrogating the features making the process much more efficient thus faster. Historically Esri courses would take the better part of a day teaching the math of a Spatial Index to enterprise Geodatabase students. Now anyone can recalculate the index by clicking  on the “Recalculate” button. This is reasonably effective at calculating a good spatial index if the feature class has one defined.

The single method:
Right click the: feature class > Properties> Indexes Tab > then click the ‘Recalculate’ button.

The batch method:
To batch recalculate the spatial index: ArcToolBox> Data Management Tools> Featureclass> Right Click ‘Calculate Default Spatial Grid Index’ tool.  Drag and drop the feature classes into the dialog.

Batch process with model builder:
The best practice is to make a model to add the spatial index and then calculate the default index. In addition models can be triggered by windows scheduler to run each night. For data that is edited frequently it is wise to re calculate the Spatial Index regularly.

I have created a model and uploaded it to the ArcGIS Resource Center. You can down load and use it as you please:

http://bit.ly/Geodatabase_Admin_Models

5 Iterate The Compact Geodatabase tool:

Compacting the geodatabase allows you to clean up empty rows in the file database tables and to nuke orphaned lock files among other things. The compact is similar to a hard drive de fragmenter, multoiple passes are very beneficial. There are several methods to run this tool:

  • You can: right click the File Geodatabase > Click Compact Database
  • You can: use the Geoprocessing Tool : Data management Tools > Database > Compact (run as batch by right click the Compact tool)
  • The best method is to use a model builder model

The intelligent method, batch process with model builder:
The best practice is to make a model to iterate a compact on the Geodatabase. In addition models can be triggered by windows scheduler to run each night or on demand. For data that is edited frequently it is wise to iterate a compact regularly too.

I have created a model and uploaded it to the ArcGIS Resource Center. You can down load and use it as you please:
http://bit.ly/Geodatabase_Admin_Models

6) If you don’t edit it, use the compress tool
The Geodatabase has a compress function. The compress works like a zip file. It optimises the storage. The draw and query times are faster when the geodatabase is compressed. None of the data can be edited while the geodatabase is compressed.  There are geo processing tools available to compress and uncompress the geodatabase if desired.

Right click the File Geodatabase> Compress File Geodatabase > Click OK

7) Iterate the repair tool on all feature classes

Read the “Repair is critical” Blog post. It is critical that geometry for the points, lines and polygons are stored correctly. The repair tool ensures standards. If the geometry is bad then it will potential slow down processes.

https://gisintelligence.wordpress.com/2011/06/30/repair-geometry-is-critical/

 8 ) Use Attribute Indices where appropriate

For fields you regularly query add an attribute index, it will speed up the queries. Attribute basesed processes will be considerably faster. However  during editing operations on the field that is indexed, it will be a wee bit slower as it has to update the index. 

Right Click the Feature class > Properties > Indexes Tab> Add Button

 9) Understand resolution and tolerance

Read the “Limiting X Y Z Drift” Blog post. Moving data around can impact on its quality and speed. High resolution datasets in the sub millimeters are typically slower than course ones in the centimeters. The resolution and tolerance of your data should be appropriate to you capture technique (precision). If you put coarse data into a feature classes that’s intended for high resolution data it does not make it a high resolution dataset. The result is the combination of xyz increase and with it the processing power required to crunch it.

https://gisintelligence.wordpress.com/2011/04/19/limiting-xyz-geometry-drift/

10) Standardise your projections and datum’s

ArcGIS is blessed with on the fly projection. It can move all you data into the same space on the screen in near real time. To do this it needs to know: what the current system is, what system you need to go to is and what is the preferred transformation equation is.  It will potentially move millions of vertices from one theory into another. This is an intensive process. By setting a single projection and datum’s as a standard the computers processors don’t need to do some much work.

11) Manage your path names and conventions

Be aware of the MS Windows long naming convention if you are using windows. In particular the characters you use in your file names, path names and table names.  From this point on I will refer to: file paths,  file names and table headers as “names”. Be concerned with the total number of characters names. Keeping names as a simple alpha numeric is a smart idea. Never start a name with a numerical value. Never have a space in a name.  Avoid reserved words and characters in names. Lastly avoid using more than 255 characters.

Permitted Characters:

abcdefghijklmnopqrstuvwxyz

ABCDEFGHIJKLMNOPQRSTUVWXYZ

0123456789_

Illegal Characters:

The following characters have special meaning within the command interpreter or the operating system kernel:

\ / : * ? ” < > |.

Finaly the last best practice advice, hold your mouth on the correct angle

Advertisements

About gisintelligence

Principal Consultant at GIS Intelligence Pty Ltd. Certified ArcGIS Desktop Professional. Spatial Solutions Architect, Lecturer, Map Maker and Food-Monster.
This entry was posted in ArcGIS Admin, Geodatabase, Geometry, Shapefile, XY Resolution and tagged , , , , , , , , , , , , . Bookmark the permalink.

4 Responses to File Geodatabase Admin

  1. great stuff joe. Well put and very succinct

  2. doug scrivener says:

    I have been going between shape (.dbf) and Access for years. I like fgdb. Can I get the fgdb table to access by using “get external data” in Access? How do I get the Access table back into fgdb?
    Very very great information Joe. Thanks much -Doug

  3. Scott Langley says:

    Joseph,
    You have given us a wealth of useful knowledge within this thread. Thank you very much for taking the time to do so. I’m a bit of a dinosaur in that I do still use both shp files and personal gdb files but have been considering the switch to the file gdb which is the reason why I came across this thread. It’s nice to have some of the questions that have been in my head answered – and then some…..

    Scott

  4. GIS_Dhan says:

    Well done Joe!
    Keep up the good work of documenting more GIS Tips & Tricks to make life easier for GIS Users!
    Best wishes!
    Dhan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s