Optimizing Spatial Data Workflows With The Addfielddelimiters Tool

The Problem of Unoptimized Attribute Data

As spatial data volumes continue to grow, organizations increasingly face performance and storage challenges with their ArcGIS geodatabases. A major pain point is unoptimized attribute data, which can strain database capacity and slow down essential workflows.

When adding features to a geodatabase, attribute fields are configured to reserve adequate storage space for text values. However, in practice, fields often contain shorter text strings than the defined length. This results in unused storage capacity, or “white space,” within the database tables. As more features and attributes accumulate, this wasted white space expands exponentially, bloating the geodatabase.

Bloated geodatabases lead to longer query times, reduced edit speeds, and sluggish application performance. Large attribute tables take longer for ArcGIS to scan, join, and filter during spatial analysis and data management workflows. Users experience the impact through choppy map navigation, spinning cursors, and delays loading attribute tables.

Understanding the AddFieldDelimiters Tool

To reclaim wasted attribute storage and improve performance, ArcGIS provides the AddFieldDelimiters geoprocessing tool. This tool analyzes the actual text string lengths in a geodatabase table and adds delimiters to shorten the reserved field lengths to closely match the real data.

For example, a parcel fabric may define a 100 character field for street addresses. But the actual addresses only use 50 characters. The AddFieldDelimiters tool would analyze the real string lengths and add a delimiter after 50 characters in the field definition, reclaiming unused space while preserving complete address values.

The tool adds field delimiters directly within the geodatabase schema. Delimiters truncate excess space without modifying actual text values. So no data is lost or altered. The delimiters simply let ArcGIS reserve less total capacity for those fields moving forward.

How Field Delimiters Improve Performance

Reducing Storage Needs

By tightening field definitions closer to utilized string lengths, delimiters can significantly reduce a geodatabase’s storage footprint. Less reserved whitespace means smaller database tables on disk and smaller index sizes for queries to traverse.

In some cases, organizations have reclaimed over 50% of their geodatabase capacity using the AddFieldDelimiters tool. This frees up room for additional data while avoiding expensive hardware upgrades. It also cuts underlying storage costs for organizations leveraging cloud data warehouses.

Optimizing Query Speed

The other major benefit of field delimiters is faster attribute queries. Narrower field definitions enable more targeted table scans and more efficient index lookups by ArcGIS. Index sizes shrink substantially when bloated whitespace is removed.

In lab tests, applying field delimiters improved query performance by 60% or more on bloated tables. Users will experience snappier attribute operations, with pop-up windows and metadata tables loading instantly even for complex features.

With the exponential growth of real-time field applications, improving query responsiveness is crucial. Delimiters allow organizations to support more concurrent users and decision-makers that require access to attribute data in the field.

Using the AddFieldDelimiters Tool

Specifying Input and Output Geodatabases

Running the AddFieldDelimiters tool involves just a few simple parameters. First, specify the input geodatabase containing the bloated tables you wish to optimize. This is usually an enterprise geodatabase stored in SQL Server, Oracle, PostgreSQL, etc.

Next, define the output geodatabase where the delimited tables will be written. This can overwrite the existing input database or export to a new delimited copy for validation.

Choosing Fields for Delimiter Optimization

The tool also allows selecting specific database tables and fields to assess and delimit. In most cases, it’s best to start by analyzing all enterprise geodatabase tables using the default settings.

The tool scans each text field and compares defined lengths versus actual string usage. For any substantial whitespace, it automatically adds truncating delimiters to narrow that field’s capacity to closely match real data.

Delimited tables are then written to the output database while preserving all original attribute values. If needed, restrictions can refine the tool to focus only on the most bloated tables or string fields.

Running the Tool and Viewing Results

With parameters defined, simply execute the geoprocessing tool. Processing times vary based on database size and number of delimited fields.

Output includes a delimited copy of the enterprise geodatabase. More importantly, the tool generates a summary report quantifying the storage savings achieved with delimiters for each field and table.

Review the output report to validate significant whitespace reduction across database tables. Check delimited datasets for any issues before overwriting source production data.

Real-World Examples

Optimizing a Parcel Fabric

Bloated parcel fabrics are common targets for geodatabase optimization. Location fields, legal descriptions, and other attributes tend to reserve lengthy text strings. Yet values often fit within smaller limits.

In one county case study, their parcel fabric’s attribute tables consumed over 100 GB in SQL Server. After delimiting, storage usage dropped to only 60 GB – freeing 40% capacity. This let them incorporate archived records while also improving update and query workflows.

Managing Utility Network Attributes

Utility network attribute bloat is another prime use case. Descriptive fields for equipment, components, and facilities typically specify very wide columns. Exporting and delimiting these datasets helps right-size the storage footprint.

One electric utility delimited their Smart Grid asset tables in ArcGIS Utility Network. This compacted indexes while maintaining complete transformer metadata. Editing performance tripled for field crews validating new GIS data. Queries to populate web dashboards also responded 90% quicker after delimiting.

Best Practices for Ongoing Data Management

While a one-time use of the AddFieldDelimiters tool can achieve major compression, organizations should also incorporate delimiting into ongoing data lifecycle practices.

As new features and attributes continue getting added, whitespace will gradually re-emerge. Periodically assessing and re-delimiting tables ensures databases stay optimized over time.

Scheduling delimiting with every major geodatabase upgrade or rebuild is recommended. Validate capacity savings and performance gains against metrics captured from the initial tool run.

Delimiting combats bloat at the source and keeps storage utilization better aligned with actual data volumes. Combined with sound database archiving, it enables ArcGIS geodatabases to support more complex analytics while avoiding platform bottlenecks.