Authors: Anita J. Temple, James Cory, & Puneet Kishor, of GeoAnalytics, Inc.
The State Historical Society is mandated to collect, maintain, manage and disseminate archaeological and historical data in Wisconsin. This data takes two forms, non-map data in DOS-based Paradox databases, and spatial data recorded on 1:24,000 scale USGS quadrangles. This legacy data is being migrated to a more powerful architecture involving Oracle and the Esri suite of products.
Additionally, the migration includes web-based data access and management applications using Allaire's Cold Fusion and Esri's ArcIMS to support the Society's on-going data activities as well as to provide regional staff, local and State agencies, and the public with selective access to the new geospatial databases.
The State Historical Society of Wisconsin (SHSW) is both a state agency and a private membership organization that was founded in 1846. By statute, SHSW is charged with collecting, advancing, and disseminating historical knowledge of Wisconsin and of the trans-Allegheny West. The Society contains several offices or divisions - an Executive Office, Administrative Services, Archives, Development & State Relations, Historic Preservation, Library, Museum, Public History, and Circus World Museum.
The Division of Historic Preservation, the SHSW Division of focus for this project, has several sections of which three were a part of this project; Office of the State Archaeologist, Compliance, and Survey and Registration. The Division is mandated by law to collect, maintain, manage and disseminate archaeological and architectural historic data for the State of Wisconsin. The Division disseminates this information to a variety of individuals and organizations. It supplies professional evaluations and access to its data to federal, state, and local government agencies for regulatory compliance purposes. The Division also provides access and technical assistance to researchers of all types, from PhD university affiliated scientists to amateur history buffs. Archaeological data is exempt from the Wisconsin open records law so access is restricted to only those individuals or agencies that have data sharing agreements with the Division.
The Society wanted to modernize its record keeping systems for archaeological and historical sites and to do so, secured a grant from the Wisconsin Department of Transportation. GeoAnalytics, in turn, was awarded the contract for the project. Additionally, the project was designed to improve internal accessibility to these data for compliance reviews as well as to provide secure access to these data to various external agencies primarily for regulatory compliancy checks. Compliance reviews are an increasingly heavy burden and access to data in a timely fashion is an impediment to both staff and state agencies.
Therefore, to provide access to all data, this project had three main tasks; to migrate the existing legacy data to a new RDBMS, create spatial data from either hardcopy maps or addresses, and to create new web-based applications for daily business activities and remote access such as data input, maintenance, and queries. This migration and modernization of the Society’s record-keeping technology, applications, and processes provides the technological infrastructure for the Society to operate efficiently and effectively in the decades to come.
All of the legacy data in the Division for historic places, archaeological and burial sites, and tracking compliance reviews were held in various versions of Paradox-based databases: Archaeological Site Inventory (ASI), Architectural Historic Inventory (AHI), Bibliography of Archaeological Reports (BAR) and a tracking database for Compliance reviews. The AHI data was the most current, being in a Windows 3.1 version of Paradox while the others were still operating in a DOS-based version with corresponding applications.
One of the primary tasks for this project was to create an Oracle database design that would convert and integrate these databases. Since these databases were separate entities, there was some redundancy, particularly in regard to locations (e.g. county, civil town, municipality, USGS quad, and/or TRS). All other data fields and values are uniquely specific to the business activities of the three departments and to their original applications. Additionally, the Paradox data did not have referential integrity so that significant data scrubbing was required. It would have been feasible to merge the BAR and the ASI but the BAR contained so many memo fields, it was decided to be more efficient to keep them separate. The resulting Oracle database has four schemas, one for each of the original databases, with shared location tables for each type of location (county, city town, etc.).
Another significant database effort was to create spatial representations of the archaeological and historic place sites. For the archaeological and burial sites and survey areas, a team of staff (permanent and limited term students), were trained to compile the areas using ArcView 3.2x heads-up digitizing against a backdrop of scanned topographic quadrangles (24K DRGs). Nearly 30,000 sites were digitized, tiled by quad sheet, quality checked, corrected for errors, and merged into one shapefile. That team completed their work in six months. For historical places, nearly 115,000 addresses were scrubbed and then geocoded through a cooperative agency using QMS with a success rate of nearly 80%. The remaining places were then evaluated for inclusion, heads-up digitized, and added to the layer.
In accordance with the Oracle database, and the fact that the data have little in common, four web-based applications are being developed. The technology architecture uses Oracle as the database, Allaire's Cold Fusion and Java as middleware, and Esri ArcIMS as the mapping engine. TOAD software, an interface for easy access to Oracle tables, making SQL statements, and so forth, is, in our opinion, an essential tool for development against the Oracle database.
Each application has the functionality to add, update, or delete data values as well as show graphic map display (with standard map navigation capabilities) and to generate reports. For each application, the first action the user makes is to login, an action that gives permissions to access levels (read, read/write, read/write/delete) to each database. The next decision is to choose the database of interest followed by whether to add a new record, delete a record, or to search for one or more records in various ways (simple or advanced searches).
In the ASI application, for instance, the simple search engine allows the user to search by one of the following: Smithsonian code, burial site information (burial site number or case number), county, civil town, municipality, TRS, and nearest water type. Advanced searches allow for a combination of queries on location (county and TRDS), site description (site type, culture, state regional program, national register status, modern land use, investigation type) or environmental information (natural division or drainage system).
Each application has various views of the data fields organized into 'tabs' that allow users to move easily between screens. The graphic below illustrates the ASI application and its tabs for views. Each screen view has organized the business information logically into groups such as location information, environmental, etc. If the login permits, the edit function button is enabled for each group of data for that particular site. Due to the volume of fields and data, the system handles subsets of data better than posting edits to the entire record. Once in edit mode, the user can choose to either update field values or delete field values or cancel out of the edit session. Again, those changes apply only to the subset of fields chosen for edits.
One of the programmatic issues is that many fields in this database are multiple value fields (e.g. ASI locations, site types, cultures, etc.) and similarly with the other three databases. This makes queries, edits, and deletions orders of magnitude more difficult to code properly. Another interesting issue was linking the ArcIMS map service to the application. Additional JAVA scripting allowed the map frames to be illustrated properly for the user.
Typically, application development is a series of iterations between programmer, database designer, and users. For this project, it was no different. It is anticipated that once Version 1 is implemented for the Society, there will be a long list of enhancements and wishes for Version 2. However, the modernization of the Society's databases and programs to a high level relational database and web-based applications is a huge leap into the future for the SHSW Division of Historic Preservation and it will serve their needs well for a long time.
At the onset of this project, the Division of Historic Preservation was working with four different applications in Paradox, programmed and cobbled together by different programmers over time. Considering this legacy system, and the onslaught of compliance reviews and demands for their data for mandated comprehensive planning, the Division sought to modernize their system.
The modernization of the Division's data and applications began in September of 2000 and should be completed in one year's time. During this year, legacy Paradox tables, with much data scrubbing, were migrated upward into an Oracle database, spatial data was captured into a digital format (points for historical places and areas for archaeological sites and surveys), and new, web-based applications are being created for use by internal staff as well as paving the way for external, password-protected, external use by various researchers, contributors, local and State agencies.
We wish to thank the staff at the State Historical Society of Wisconsin, Division of Historic Preservation, for their efforts and cooperation with this project. In particular, we wish to thank Robert Birmingham, State Archaeologist, for his project leadership, John Broihahn, Jim Draeger, Brad Schwarzhoff, and Rick Dexter as well as the group of hardcopy data converters, Diane Holliday, Wesley Bernardini, Colleen Hermans, Amy Rosebrough, Matthew Thomas, Luz M. Ramirez de Bryson, Deirdre March, and Christopher Swiggum. We also thank Jerry Sullivan, of the Office of Land Information Services, for their gratis geocoding services for historical place data. Lastly, but importantly, we wish to thank the Wisconsin Department of Transportation whose grant provided the funding for this project.