GISdevelopment.net ---> GITA 2000 ---> Uniting The Enterprise

Seamless GIS integration with other software applications

Jane E. Hayes and Ann Ridnour
Logan County Assessor
315 Main Street
Sterling, CO 80751


General information
Logan County is a rural county of 1,839 square miles located on the high plains of northeastern Colorado. According to the 1990 census, its population is approximately 18,800 persons with a median household income of $22,065. Sterling is the county seat and is a major regional commercial center for northeastern Colorado.

Logan County does not have a data processing department or computer programmers on staff. The Assessor's Office is comprised of 10 full time employees and maintains approximately 19,500 parcels, which includes approximately 11,500 parcels of real property. The 1999 total assessed value for Logan County is $144,773,120.

Duties of the Assessor
The County Assessor is required to discover, list and value all taxable properties within the county. Colorado operates on a two-year assessment cycle, resulting in complete property revaluations every other year. Each year the Assessor notifies property owners of the current year property values, conducts appeals, certifies valuations to the taxing entities, and delivers the tax warrant to the County Treasurer after tax levies have been certified by the County Commissioners. Assessors are also required to maintain county maps.

Hardware & Software Configuration
The Assessor's Office system server operates on a 200 MHz processor with 256 MB Ram memory and a 9.1 GB hard drive. The individual workstations also have 200 MHz processors, 32 MB Ram Memory and 3.2 GB hard drives. The workstations that perform the GIS functions have 64 MB Ram memory.

All of the software used in the Assessor's Office is "off the shelf." The network runs on Windows NT. The assessment administration and valuation functions are performed utilizing Cole-Layer-Trumble, Inc. (CLT) Integrated Assessment System (IAS). The GIS functions are performed by ESRI ArcCAD, which operates on an AutoCAD engine. The Assessor's Office also utilizes ArcVIEW for end-user GIS applications. In addition, the Assessor's Office utilizes the Microsoft Office 97 suite of products.

History of GIS and Assessment Software Within the County
The Assessor's Office purchased EarthOne GIS software in late 1988. EarthOne was a CAD-based GIS and was one of the first desktop GIS applications available. GIS training and implementation began in earnest in early 1989. The County converted to ESRI's ArcCAD, also CAD-based, GIS software in 1993.

Once the GIS program was in place, difficulties were encountered integrating the spatial GIS data with the tabular data in the assessment system. The integration could be accomplished, but it required numerous cumbersome tasks involving the saving, retrieving and joining of files. Not only was this time consuming, but it meant that the tabular data was outdated the moment it was joined to the spatial data, thus requiring the entire download process to be run and re-run in futile efforts to keep all of the data current.

The County converted to CLT's IAS assessment software early in 1998. This conversion to CLT dramatically expanded the integration capabilities within the Assessor's Office, since it marked the first time that the assessment and administrative systems were based on an "open" Relational Database Management System (RDBMS).

The integration
The County contracted with Farragut Systems, Inc. in mid-1998 to perform the seamless integration of its assessment and GIS systems. The County's cost for the integration was less than the annual salary (excluding benefits) of an entry-level clerk in the Assessor's Office and was significantly less than the cost of a full-time data processing/programming position. The integration project was completed in a matter of weeks. The Assessor's Office has been so pleased with the results of the integration that the original integration capabilities are currently being expanded to include more assessment data fields.

Integration goals

Single Update Process
Prior to the integration, the Assessor's Office maintained the assessment and GIS databases separately, resulting in duplication of efforts. One of the primary goals of the integration was to eliminate the duplication processes and instead create the ability to maintain and use a single process to update both assessment and GIS data.

Use GIS to Calculate and Update Assessment fields
GIS systems have the ability to calculate a variety of data, such as land size (acreage or square feet). Prior to the integration, fields such as those relating to land size were hand-entered into the assessment system. The Assessor's Office wanted to create the ability to use the GIS to calculate and also update the assessment fields.

Ensure Assessment Data Used for GIS Analysis is not Out-dated
Prior to the integration, the Assessor's Office had to download extract files from the assessment database, and then tie the assessment download to the GIS system. Because the download process was so cumbersome, the download process was not run for each GIS application. Since assessment data such as ownership and mailing addresses changes on a daily basis, the infrequency of downloads resulted in GIS analysis being run on data that was potentially out-dated. Our goal was to have GIS analysis always based on current assessment data.

Automatic, live link from GIS to Assessment data
The entire download process mentioned above was not efficient. Our goal was to eliminate the download process completely. We wanted to establish an automatic and live-link from the GIS to the assessment database.

Better Utilization of GIS as Integrated Assessment Tool
GIS is a powerful analysis tool, but the Assessor's Office staff was not taking full advantage of its capabilities. The only GIS users were those directly involved in the mapping of parcels. We wanted the entire staff, including the appraisal and clerical staffers, to better use the GIS as an integrated assessment tool.

Requirements of an integrated system

Requirements Specification
Within this project, a requirements specification was created that detailed both the functional and user interface requirements of the integrated system. In addition to provide a working "blueprint" of the system, the requirements specification was used to identify specific tasks that were to be accomplished by the contractor. The following tables depict an example of the requirements specification that was developed for this integration effort.


Figure 1: Portion of the Requirements Specification used within the Project.
Please note that this is a single section of the document.


Implementation steps
The implementation of the Logan County GIS/assessment integration project included the followed sub-tasks. Please note that, although the sub-tasks are labeled sequentially, many of the sub-tasks were completed in parallel. In addition, although development of the User Interface is presented as the last item, it is recommended that the User Interface (with the functions or buttons stubbed, e.g., the button exists, but no function is behind the button) be one of the first items completed during the development phase.
  • Define and/or revise GIS database dictionary
    The importance of a well-developed and maintained database dictionary cannot be overstated. Any integration that is performed relies heavily on the information contained within the database dictionary in terms of which fields to display, field definitions, etc. Typically, assessment systems have well-developed database dictionaries. Logan County also has a very well-defined and maintained database dictionary for the GIS spatial data, which greatly eased the integration project.
  • Define assessment fields to update, display, and/or query from GIS
    Typically, the data that the end-user requires out of the assessment system is a small subset of the assessment database. A good approach in defining the appropriate fields and their usage is to develop a data matrix. A data matrix is a matrix that defines fields and usage (update, display, query, insert).
  • Define and implement ODBC connectivity
    Once the assessment fields to be displayed and/or queried are defined, the GIS is configured to connect to the assessment database via ODBC. This allows the GIS software to connect to the assessment database and automatically retrieve specific fields specific to a GIS feature. For example, the sales information of a specific lot can be retrieved from the assessment database and displayed based on a lot selected from the GIS user interface.
  • Define and implement SQL using native GIS tools
    Specific software packages have their own methodology to connect to SQL databases. We used tools native to our GIS software to define and implement SQL.
  • Develop code to develop SQL scripts "on-the-fly"
    For this project, we implemented the ability to submit SQL scripts from the GIS to the assessment database by automatically generating the SQL scripts from within the GIS's native language. Our goal to was to create a GIS-centric application, which allowed the GIS to update the assessment database.
  • Develop code that retrieves information from GIS
    This code was also written in the GIS's native language. This passed information, such as active theme or coverage, selected records, and required assessment fields.
  • Develop code to start Oracle and pass SQL scripts
    This code is typically an ASCII script file of SQL*PLUS commands. At the command line, SQL*PLUS can be initiated with this file passed as an argument.
  • Develop code that links or joins IAS tables to coverage datasets
    This code handles the linking or joining of GIS coverage features to the assessment database. This is typically done "under the covers" in order to reduce the requirement of the users in establishing connections to the assessment system. The automatic linking simplifies the application and, consequently, makes it more accessible to the end-user.
  • Organize code, scripts, tables, etc. into GUI
    A well-designed user interface, customized for the specific application, will lead to wider use of the technology within a department or municipality. For example, we focused on creating push-button approaches to common tasks (e.g., we may not have a "make-map" button, but we're close). This includes the automatic loading and symbolization of the GIS data, automatically establishing connections to the assessment system, and wide use of pull-down menus for common tasks.

Figure 2: This screen shows the pull-down menus created by the interface that
identify the tables (PARDAT, OWNDAT, LEGDAT,LAND, etc.) and the fields
(Alternate ID, total acres, front footage, etc) in the assessment database
that are automatically updated when changes are made to the GIS.


Benefits
All of the integration goals set before beginning the integration process have been met. Both tangible and intangible benefits were derived from the integration effort. Tangible benefits included the following:
  • Single user interface and process to access, modify, and update both assessment and GIS data, thus leading to a more streamlined process and increased efficiency.
  • GIS and assessment data that is always current and up to date. With the new integration, the user is always ensured of accessing the most current assessment data, since the connection is made to the actual assessment database, rather than a copy.
Intangible benefits include the following
  • Better information and better decision making. The better information is not only a function of the GIS/assessment integration, but also because of the processes involved with any implementation of GIS. For example, when a link is created between the parcel GIS layer and the assessment datafiles, errors are frequently discovered in the Parcel ID in either the GIS or assesment databases, e.g., range and township might be switched, etc.
  • Elimination of duplication of efforts. Two databases are now maintained with one procedure.
  • Reduction of errors. The integration interface automated many of the functions used to maintain complex office procedures. The integration interface prompts the user through a series of steps, thus lessening the chance that an important part of the process is inadvertently omitted. This has been especially helpful in those procedures used to split a single parcel into several smaller parcels or to combine several properties into a single parcel.
  • Technical expertise of GIS user lessened. Since many of the GIS administrative functions are now "built-in" to the integration interface, a user with limited technical GIS expertise can still successfully perform many of the GIS administrative functions. This is of great benefit to Logan County, where the pool of potential employees with extensive technical GIS expertise is very limited.
Lessons learned
Four items were deemed of major importance for the successful implementation of the integrated system, including:
a comprehensive and up-to-date database dictionary, development of a requirements specification, open standards, and a high level of support from the ASSESSMENT vendor.
  • Database Dictionary
    A solid database dictionary is important for both the GIS and assessment software systems. Typically database dictionaries derived from the assessment vendors are very solid. What frequently provides more problems is the GIS database dictionary, since these are sometimes non-existent or out-of-date. It is very important to identify data fields, to retrieve, link, etc.; the database dictionaries provides critical information since it details both the GIS and assessment data structures.
  • Requirements Specification
    As in any software development effort, development of a requirements specification is an important first step. A requirements specification document typically includes a characterization of the end-users, and a detailed statement of required functionality, including functions, database structures, and a user interface definition. Additionally, a prototype interface can be presented, with screen shots depicting forms, pull-down menus, etc. Typically, the forms contain stubbed functions, (e.g., the button exists, but no functions underlie them).
  • Open Standards
    A primary technical driver for GIS/assessment integration efforts has been the movement from proprietary systems to systems based on open standards. Simply put, the new open standards, such as ODBC and SQL, made integration efforts like the project described in this paper, easier to develop and maintain. Integration efforts based on proprietary systems are oftentimes more expensive, higher risk, and harder to maintain than those based on open standards.
  • Support from the assessment vendor
    As part of this integration effort, we inserted and/or updated assessment records directly from the GIS. To perform this function, we physically got "under-the-covers" of the database, and inserted and/or updated records directly into Oracle (CLT's IAS 4 is based on the Oracle RDBMS).

    This technique required close interaction with CLT's technical personnel, to ensure that these inserts and updates were correctly performed, and that all internal database actions were being performed (e.g., triggers, etc.). For this effort, CLT provided a high level of support, without which, this effort would not have been possible.
© GISdevelopment.net. All rights reserved.