Geocoding Data Analysis and Processing in Relational Databases

INTRODUCTION Geocoding data are geospatial coordinates that are used to identify location points on the earth. Geocoding data, especially physical location latitude and longitude data, become part of business data when businesses collect information for their decision making purpose. Business applications rely on data that are commonly stored and processed by relational database management systems (RDBMS). As part of business data, geocode, namely latitudes and longitudes, are incorporated more and more into applications' data processing to represent business locations and to compute distances from each others. As part of the data structure, geocoding coordinates can be stored in relational databases same as other business data fields to participate in data processing, such as updating, sorting, computing, as well as complex querying. RDBMS is a database system that maintains and manipulates data repository based on relational model (Codd, 1970). Through database objects, such as tables and views, relational databases organize data records into rows and columns. Therefore latitude and longitude data can be easily fit into any data attribute structure. Relational database is designed to maintain data integrities by using a variety of database constraints, such as primary keys and foreign keys. Data integrities, such as entity integrity and referential integrity, are enforced to comply with any business rules in database (Date, 2004). Geocoding data, and the way to process them, have their unique characteristics. Latitude and longitude data deserve special care from inside relational databases to reflect their computation intensive nature and to keep up with the database performance. Business applications can be degraded if the response time takes too long under low performance. There exists a variety of challenges in dealing with spatial data in relational databases, ranging from data formatting, sorting, transforming, computing, as well as storing. This paper discusses some of the issues in the process of using geocode as part of business data. The discussion starts from data analysis based on the business application requirements. Challenges to fulfill design requirements in relational databases will be addressed, followed by computational algorithms and implementation designs. Performance issues will be discussed under special relational design to minimize the computation process in responding to application queries. REQUIREMENT ANALYSIS Geocoding information is part of business data. Locations, such as customer location, business location, shipping address, contact address, are collected and stored by almost all applications that deal with business data. Accurate location information, such as street address, city, ZIP code, and state, are crucial in locating and contacting the given party. Geocoding data, latitudes and longitudes, are frequently part of location data to represent geographical location of the concerned data entity. Nowadays, geocoding process can be easily used to obtain geocoding coordinates. Geocode-enabled data can be easily incorporated into geographic information system (GIS) applications. Maps representing specified locations are also available through geocoding data. Latitude and longitude data can be provided by client directly or be retrieved based on location addresses collected. For example, public available web services, such as Google and Yahoo, can be invoked by feeding raw physical address to obtain geocoding data. The geocoding data can then be used, for example, to retrieve location map and calculate distances under certain conditions. Geocoding data have to be collected and stored in the way that they can be easily queried to support data manipulations. Latitude and longitude coordinates can be represented in different formats, such as degree-minute-second format and decimal degree format. In order to be used in a relational database and minimize dynamic conversion, coordinates need to be stored in a single format. …