Cost
Effective Solutions to Utilizing GIS with Existing Databases:
Andrew N. Sauer, Camp
Dresser & McKee Inc, Kansas City, MO
Geographical Information Systems (GIS) can be expensive, intimidating endeavors for smaller cities (Population < 25,000). With proper planning and technical resources, however, a smaller municipality can effectively use GIS to display existing data, generate new maps, and increase data retrieval capabilities without spending large amounts of money. Prairie Village, Kansas, a Kansas City suburb, was faced with this very issue, and contracted with Camp Dresser & McKee (CDM) to provide professional GIS services for their Public Works Department. A community of just over 23,000 people, Prairie Village desired the ability to link existing public works data to available County GIS coverages. Working with a small budget, CDM was able to successfully link the existing non-spatial public works data with the County spatial coverages using ArcView. CDM used addresses stored in the existing public works database as the conduit to link spatial and non-spatial data. Using geocoding, the location of each database feature was located on the County GIS coverage. The first step in the project was to create a relationship between the street segments in the City’s database with the County street centerline coverage. Once the initial street database was connected to the centerline coverage, additional data stored in the non-spatial database could be displayed spatially – including street signs, trees, and sidewalks. The resulting connection established between the existing public works database and the County’s spatial database provided the City with a method for displaying existing data, generating new maps, and efficiently querying and retrieving data. The Public Works department can now effectively utilize, update, and maintain data in ArcView.
INTRODUCTION
Geographical Information Systems (GIS) can be expensive, intimidating endeavors for smaller cities (Population < 25,000). However, with proper planning and technical resources a smaller municipality can effectively use GIS to display existing data, generate new maps, and increase data retrieval capabilities without spending large amounts of money. Prairie Village, Kansas, a Kansas City suburb, was faced with this very issue, and contracted with Camp Dresser & McKee (CDM) to provide professional GIS services for their Public Works Department. The main goal of the project was to link existing public works databases to available spatial data using ArcView 3.2a to effectively manage public works assets and efficiently display and map public works data in order to communicate with city council members, developers, and citizens.
Prairie Village, Kansas is located within Johnson County, Kansas approximately 7.5 miles southwest of downtown Kansas City, Missouri. It has a current population of just over 23,000 people. Prairie Village is one (1) of seventeen (17) Johnson County suburb cities that comprise the southwestern metropolitan area of Kansas City (Figure 1). As shown in Figure 1, the City is blocked from expanding in all directions by other municipalities. Because the City’s spatial extents are fixed, the public works department is focused on maintaining and managing their existing infrastructure.
The City’s Public Work Department is currently using ArcView 3.2 for their GIS applications software but has no GIS department or GIS coordinator. Currently, GIS applications include displaying parcel mapping, manually querying ArcView tables for addresses and street names, and building new ArcView shapefiles of tree maintenance data. Technical ArcView and GIS experience is limited, ranging from no experience to using ArcView weekly to build and maintain shapefiles. ArcView shapefile storage is decentralized with data stored on various local and networked hard drives and in numerous folders.
In addition to GIS data and software, the Public Works Department uses Vanasse Hangen Brustlin, Inc. (VHB) asset managment software, RoadManager 2000, to store infrastructure information, schedule maintenance, issue work orders, and meet the accounting requirements for Governmental Accounting Standards Board (GASB). The Public Works Department created the VHB database from existing paper records and field verification of street high and low address. Prairie Village has confidence in the VHB database because it was created in house from existing records and is updated and maintained on a daily basis. However, the VHB software had no ability to display or query the data spatially; a function that the Public Works Department wanted to link the available County spatial data with their in-house database.
Data
GIS Data
The primary source of GIS, or spatial, data being used by the Public Works Department was obtained from the Johnson County Automated Information Mapping System, or AIMS. Available spatial data included: buildings, street centerlines, driveways, edge of pavement, contours, parcels, spot elevations, utility features, vegetation, water features, water structures, and orthophotographs. Associated attribute tables included address, land class, legal descriptions, permits, owner, and sales and value. This data is developed, maintained, and updated by AIMS and is available to the cities of Johnson County at a nominal fee. The future goal of this data sharing agreement is for the cities to use the data, update and develop new data, and then provide data back to the County in a GIS format.
Non-Spatial Databases
The Public Works Department VHB database software, RoadManager 2000, is the primary inventory and assessment tool that is being used by Prairie Village. Data currently being stored in this database structure are pavement, sidewalks, traffic signs, trees, and drainage structures. Attributes associated with these databases are stored in separate modules and are linked together by a unique identifier. Table 1 is a sample of the pavement data stored for each street segment. This unique identifier, Parent ID, is generated from concatenating the associated street name with a sequential index number. For example, the second segment of 63rd Street would be 63ST02 and the first segment of Ash Street would be ASST01. As shown in Table 1, each record in the pavement database has a street name and high and low address associated with it. Not quite latitude and longitude, but it is a spatial reference that, as described below can be used to link spatial and non-spatial data.
Table
1. Sample of VHB Pavement database attributes
|
OB_NAME |
PARENT |
FROM |
TO |
LOW_ADDR |
HIGH_ADDR |
|
63RD STREET |
63ST01 |
MISSION ROAD |
ROE AVENUE |
3900 |
4630 |
|
63RD STREET |
63ST02 |
ROE AVENUE |
NALL AVENUE |
4700 |
6299 |
|
63RD TERRACE |
63TE01 |
DELMAR DRIVE |
GRANADA DRIVE |
4302 |
4510 |
|
63RD TERRACE |
63TE02 |
ROE AVENUE |
HODGES DRIVE |
4701 |
5007 |
|
63RD TERRACE |
63TE03 |
HODGES DRIVE |
ASH STREET |
5114 |
5227 |
|
ASH STREET |
ASST01 |
63RD STREET |
64TH STREET |
6300 |
6345 |
|
ASH STREET |
ASST02 |
73RD STREET |
TOMAHAWK ROAD |
7300 |
7409 |
IMPLEMENTATION
Data Review and Organization
The first step in providing GIS professional services and developing GIS applications for Prairie Village was to first review and organize the available spatial and non-spatial data. CDM conducted a detailed review and analysis of Prairie Village’s existing data and electronic data organization structure. In general, the review of the Prairie Village data indicated that various public works data, drawings, documentation, and ArcView projects and applications were being stored on different local and network drives and in different folders and sub-folders.
Prairie Village found managing data within their current GIS framework to be confusing and difficult to track. The data folder structure, while most likely familiar to Prairie Village, lacked a rigid and logical folder structure that would make updates and management easier. CDM worked with Public Works staff and management to develop an effective and logical data structure to organize both their spatial and non-spatial data.
To streamline data management, provide a tracking mechanism, and eliminate confusion, CDM proposed and implemented an electronic data storage structure that helps eliminate confusion on data source and provides a tracking mechanism for data updates and archiving data. This formed a solid base to build sound GIS applications. The first application was to develop a process to link existing non-spatial infrastructure databases to the County’s spatial data.
Linking Spatial
and Non-Spatial Data (1-to-1 Relationship)
A relationship was developed linking the City of Prairie Village’s (City’s) Public Works asset management database, VHB, to the Johnson County AIMS street centerline. The relationship was necessary for the City to spatially display data stored in the non-spatial VHB database. The resulting relationship allows the City to display data from the VHB database in GIS.
Each line (arc) segment in the County street centerline coverage has a unique identification number (uniq). The uniq field in the centerline coverage was used as the primary key to link the centerline coverage to the VHB database. For the VHB database, the parent field was used as the primary key. The parent field was unique for each record in the database. The relationship between the uniq and parent fields was the basic framework for achieving a 1-to-1 relationship. Following are the procedures and results of the 1-to-1 relationship.
The 1-to-1 relationships used two data sources: 1) Johnson County AIMS street centerline coverage (co_cent) and 2) Prairie Village Public Works VHB Pavement 2000 database. An initial review of the data found that the pavement database had 506 records while the centerline ArcInfo coverage of the City had over 1,100 arc segments. Therefore, it was determined that a record in the pavement database was often comprised of more than one arc in the street centerline coverage. Street names were not always consistent between the City and County data. Furthermore, discrepancies were also found between the parcel addresses and the street name. The following sections detail the discrepancies in the data.
Street name discrepancies were a common problem in developing a 1-to-1 relationship. Numerous street segments within the Prairie Village boundary had different names associated with the street and/or property address. Table 2 below provides a sample of some of the street naming discrepancies found during this project. As shown in Table 2 the main discrepancy was in the street type (e.g. Avenue, Drive, Road, or Street). For the City, twenty-five (25) street names were found have different spellings and/or street type compared to the County’s GIS street centerline coverage. This may seem trivial but exact street names were found to be a key component of this process.
Table 2. Sample of
Street Name Discrepancies
|
VHB Database |
Parent ID |
Co_Cent |
Prairie Village Map |
Parcel Address |
|
Belinder Avenue |
BEAV01-07 |
Belinder Road |
Belinder Road |
Belinder Street |
|
Booth Avenue |
BOAV01-02 |
Booth Street |
Booth Street |
Booth Street |
|
Booth Drive |
BODR01 |
Booth Drive |
Booth Drive |
Booth Drive and Booth
Street |
|
Buena Vista |
BUVI01-03 |
Buena Vista Street |
Buena Vista |
Buena Vista Street |
|
Canterbury Drive |
CADR01 |
Canterbury Street |
Canterbury |
Canterbury Street and Canterbury Road |
|
Canterbury Drive |
CADR02 |
Canterbury Street |
Canterbury Drive |
Canterbury Drive |
|
Catalina Drive |
CATD01-04 |
Catalina Street |
Catalina |
Catalina Street |
|
Elmonte |
ELST01-05 |
El Monte |
El Monte |
El Monte |
The majority of the VHB street segments were linked to the County centerline coverage using geocoding. Geocoding is the process of assigning a spatial location defined by a street address. As described above, a high and low address was recorded for each VHB Pavement record. The high and low address assigned to the database was field verified by Prairie Village Public Works field crews. Because the high and low address was field verified it was used as a secondary key to link the VHB records to the County centerline coverage.
A table of high and low addresses was constructed from the VHB pavement database. Each point was given a unique name that was the combination of the parent field followed by an L for low address or H for high address. The spatial location of each address was converted into an ArcView point theme using geocoding. The County centerline coverage was used as the reference theme for determining the spatial location.
The geocoding procedure was able to successfully match 75 percent of the VHB addresses. Of the remaining 25 percent, 13 percent were designated as cul-de-sac street segments and 12 percent were determined to have other errors. These remaining errors were resolved manually using electronic parcel data and paper maps and records.
The resulting 1-to-1 relationship between the County street centerline and Prairie Village VHB pavement database was completed using both automated and manual procedures. Table 3 provides a sample of the resulting 1-to-1 relationships that link the centerline unique field to the parent field in the VHB database. While the linking of these two independent databases was successful it is possible to eliminate the manual process of joining the databases by achieving address consistency between the County street centerline, Prairie Village Public Works databases, and County parcel data. Ideally, these three databases should use the same naming convention and addressing for each and every street throughout Prairie Village. If this could be achieved then the automated process of geocoding the VHB addresses to the County street centerline could be the relationship rather than Table 3, which could become invalid in the future when one or both of the databases are updated.
Table 3. 1-to-1 Relationships
|
Co_Cent Uniq |
Road_name |
VHB Parent |
|
1762 |
W 63RD ST |
63ST01 |
|
2044 |
W 63RD ST |
63ST02 |
|
2045 |
W 63RD ST |
63ST02 |
|
2046 |
W 63RD ST |
63ST02 |
|
4977 |
DELMAR ST |
DELD02 |
|
23608 |
ROE AVE |
ROAV02 |
|
4978 |
GRANADA ST |
GRDR02 |
Locating Street Signs
Once the street centerline coverage was successfully linked to the non-spatial VHB database, additional data stored in the VHB database could be mapped using attribute stored in the database. The first additional data mapped was street signs. The VHB database for street signs included the Parent ID, side of the street the sign was located on and distance along the street segment. Using this information, CDM developed a customized ArcView script that estimated the location of each street sign based on the attribute data stored in the VHB database. Figure 2 is a sample map of the results of street sign mapping. As with the geocoding process, there were about XX percent of the signs that could not be located using this automated process. Review of the unmapped street signs found missing or errant data in the database. Once the data was cleaned up in the VHB database all of the street signs should be able to be located along the appropriate streets.
RESULTS AND CONCLUSIONS
Camp Dresser & McKee Inc. (CDM) was contracted by the City of Prairie Village, Kansas to provide cost effective solutions to integrating existing infrastructure data with Johnson County, Kansas GIS coverages. CDM used automated ArcView processes, geocoding and customized scripts, with manual database review and corrections to successful link spatial and non-spatial data. During the process CDM reviewed and organized the Public Works existing electronic data structure, updated their County GIS coverages, and provided recommendations on using GIS to map existing infrastructure. The results were improved electronic data management, higher quality databases, and increased usability and mapping of their existing data.
While this project may not have pushed the outer limits of GIS technology it did meet the needs of the City of Prairie Village. The processes presented in this paper demonstrate some practical solutions to achieving mapping objectives of smaller municipalities. This project was a good example of applying GIS with limited resources and within budget. Future plans are to continue to develop customized applications for Prairie Village using the street centerline as the primary link to map additional existing data such as sidewalks and trees. While this type of mapping is not as accurate as GPS it does provide the Public Works Department the ability to display existing data and perform spatial queries that they previous were unable to do. Therefore, the overall goal of meeting the needs of the City is achieved, resulting in a successful GIS project.
ACKNOWLEDGMENTS
The author would like to acknowledge the City of Prairie Village, Kansas and Johnson County, Kansas for their contribution and support of this project. I would like to especially acknowledge Bob Pryzby, Director of Public Works for Prairie Village, and Suzanne Lownes, Prairie Village Public Works Department Office Manager, for their contributions to this successful project.