Leveraging Geoprocessing Functionality to Manage Enterprise Data
Continued...

Geoprocessing/Scripting offers an efficient and encapsulated solution for accomplishing these goals. After integrating tabular attribution with spatial features, we need to utilize an inventive means of packaging data for distribution. Data translation tasks facilitated by geoprocessing include

• Aggregation of features and summarized values
• Reprojections
• Field mapping and renaming
• Data formatting
• Spatial to tabular conversions (such as parcel/district cross-references)

While the above processes are readily accomplished within Esri applications, we have found that automation utilizing geoprocessing is not necessarily limited to this environment alone. Geoprocessing scripts can be embedded in other software packages to exploit their functionality. Because much of Sacramento County's automation is in the form of SQL processing, we rely on Data Transformation Services (DTS) within the SQL Server framework. [DTS provides graphic tools and programmable objects that administrators and developers use for extraction, transformation, and similar data movement operations.] Compiled geoprocessing scripts are just one step in a series of data manipulation tasks.

Advantages of Geoprocessing

• Automates recurring and repetitive tasks
• Produces standardized and consistent output
• Standardizes field naming conventions and formatting
• Supplies quality control checks and balances
• Reduces staff time; frees staff to resolve other issues
• Can be packaged and shared
• Is self-documenting
• Is perfect for operations that are mostly spatial in nature

Advantages of Data Transformation Services

• Automates recurring and repetitive tasks
• Produces standardized and consistent output
• Standardizes field naming conventions and formatting
• Supplies quality control checks and balances
• Decreases staff time, which can better be devoted to other issues
• Can be packaged and shared
• Is self-documenting
• Is perfect for operations that are mostly tabular in nature

If the bulk of the processing is spatial, we embed the SQL operations within a model or script. Conversely, if the bulk of the processing is SQL, then we embed the geoprocessing in the DTS.

The accompanying illustration titled "Complex SQL with Some Geoprocessing" shows how geoprocessing functionality can be leveraged from within the SQL Server environment. This diagram shows a workflow for generating a multiple address table by consolidating street address information from numerous relational databases from the assessor, permitting, utility billing, and other departments using native SQL functionality. Once consolidated and scrubbed, an embedded, compiled Python script standardizes the tabular data by calling the StandardizeAddress method of the geoprocessor object.

While this process is easily accomplished in the GIS world, the same task would require a significant amount of coding using SQL Server alone to achieve the same results. In conclusion, many GIS tasks are often voluminous and laborious, while data accuracy, currency, standardization, and dissemination are the pivotal components that drive mission-critical business systems. Esri's geoprocessing toolsets offer both flexibility and wide-ranging capabilities for decreasing the resources needed to maintain this information while liberating data maintainers and GIS analysts to do what they do best. For more information, contact

Cynde Porter
Information Technology Analyst II
Tel.: 916-875-7032
E-mail: portec@saccounty.net

Bob Earle
Principal Technology Analyst
Tel.: 916-875-6800
E-mail: earleb@saccounty.net

Sacramento County GIS
9700 Goethe Road, Suite A
Sacramento, California 95827

About the Authors

Cynde Porter, a GIS/IT analyst, graduated with a bachelor's degree from the University of California, Davis, in 1995. She joined the Sacramento County GIS unit shortly thereafter and is primarily responsible for spatial data warehousing and developing standards and processes to coordinate interdepartmental data sharing, system integration, and workflow automation.

Bob Earle received his master's degree in geography from San Francisco State University in 1973. He is currently the GIS database administrator for Sacramento County and an instructor of GIS at American River College as well as the College of Extended Learning at San Francisco State University.