Oracle Spatial User's Guide and Reference
Release 8.1.6






Prev Next

Generic Geocoding Interface

This appendix describes a generic interface to third-party geocoding software that lets users geocode their address information stored in database tables and obtain standardized addresses and corresponding locational information as instances of predefined object types. This interface is part of the geocoding framework in Oracle Spatial and interMedia Locator products.

A geocoding service is used for converting tables of address data into standardized address, location, and possibly other data. Given a geocoded address, one can then perform proximity or location queries using a spatial engine, such as Oracle Spatial, or demographic analysis using tools and data from Oracle's business partners.

Once data has been geocoded, users can perform location queries on this data. In addition, geocoded data can be used with other spatial data such as block group, postal code, and county code for association with demographic information. It is now possible for decision support, customer relationship management, supply chain analysis, and other applications to use spatial analyses as part of their information processing and gathering functions. Results of analyses or queries can be presented as maps, in addition to tabular formats, using third-party software integrated with Oracle Spatial.

This appendix describes a set of interfaces and metadata schema that enables geocoding of an entire address table, or a single row. It also describes the procedures for inserting or updating standardized address and spatial data into another table (or the same table). The third-party geocoding service is assumed to have been installed on a local network and to be accessible through standard communication protocols such as sockets or HTTP.

C.1 interMedia Locator Implementation: Benefits and Limitations

Oracle interMedia Locator contains a set of interface (API) functions that allows the integration of Oracle Spatial to third-party geocoding products and Web-based geocoding service. A database user can issue a standard SQL call or construct PL/SQL routines to geocode an address, and retrieve the spatial and standardized address objects, both of which are defined as Oracle database object types. Users have the option of storing these in the database or using the spatial objects in Locator functions for Euclidean within-distance queries.

The APIs offer great flexibility in extracting information from existing relational databases. Data conversion procedures are minimal. A geocode result also returns an additional set of information; there is no requirement to use all the information, and the application can decide what fields to extract and where to store them. However, to use the full range of features of Oracle Spatial or interMedia Locator, it is recommended that the Spatial object be stored as returned.

The existing locator service is Web-based and requests are formatted in HTTP. Thus, each request in SQL must contain the URL of the Web site, proxy for the firewall (if any), and user account information on the service provider's Web site. An HTTP approach potentially limits the utility or practicality of the service when dealing with large tables or undertaking frequent updates to the base address information. In such situations, it is preferable to use a batch geocoding service made available within an intranet or local area network. The following sections describe the interface for a facility that can include the existing HTTP-based solution.

C.2 Generic Geocoding Client

A fast, scalable, highly available, and secure Java Virtual Machine (Java VM, or JVM) is integrated in the Oracle8i database server. The Java VM provides an ideal platform on which to deploy enterprise applications written in Java as Java Stored Procedures (JSPs), Enterprise Java Beans (EJBs), or Java Methods of Oracle8i object types.

Therefore, any client geocoder component written in Java can be embedded in the Oracle8i database as a JSP. This JSP interface can perform either one-record-a-time or batch geocoding. Java stored procedures are published using PL/SQL interfaces; thus, the generic geocoding interface can be compatible with existing Locator APIs.

The stored procedures have an interface, oracle.spatial.geocoder, that must be implemented by each vendor whose geocoder is integrated with Oracle Spatial and interMedia Locator. The procedures also require certain object types to be defined and metadata tables to be populated. The object types, metadata schema, and the geocoder interface are described in further detail in the following sections.

Although the database user MDSYS oversees all data types, operators, and schema objects for Oracle Spatial, the geocoding metadata must exist in each user's schema. Each user of the geocoder service must have tables that implement the metadata schema.

Figure C-1 shows the Oracle geocoding framework.

Figure C-1 Oracle Geocoding Framework

C.3 Geocoder Metadata

The metadata describes the properties of the geocoding server, the location and structure of the address data to be geocoded, and the nature and storage location of the geocoding results. Other relevant information may include the name of the server machine, the port to connect, and so on. Together, these constitute the initialization parameters and are stored in metadata tables under the user's own schema. At client initialization, a data dictionary lookup is performed to locate the necessary metadata.

Batch geocoding allows the user to simultaneously geocode many records from one table. Batch geocoding requires the following:

Thus, the metadata table consists of a task ID, geocoder information, and schema information. The task ID is a primary key that identifies the initialization parameters for a particular geocoding task. For example, geocoding a table of customers is one task, while geocoding a table of customer inquiries is a separate task.

The metadata is stored in a table named GEOCODE_TASK_METADATA, which is defined as follows:

Create table geocode_task_metadata (
  task_id  NUMBER,  -- primary key

Note the following about the GEOCODE_TASK_METADATA table:

C.3.1 Server Properties

The geocoder_info property column of the GEOCODE_TASK_METADATA table contains information describing the characteristics of the server, including machines, ports, and vendor-specific information. The GEOCODE_SERVER_PROPERTY_TYPE type is defined as follows:

create type geocode_value_array as
  varray(1024) of varchar2(64)
create type geocode_server_property_type as object
  servers  geocode_value_array,
  protocol  varchar2(32),
  property_name  geocode_value_array,
  property_value  geocode_value_array,
  reject_level  integer,
  batch_size  integer

Note the following about the GEOCODE_SERVER_PROPERTY_TYPE definition:

C.3.2 Geocoding Input and Output Specification

The schema_info property column of the GEOCODE_TASK_METADATA table specifies the set of columns that makes up an address in the table to be geocoded, the table and columns into which the geocoding results are stored, and where rejected record data and multiple matches are stored.

The GEOCODE_SCHEMA_PROPERTY_TYPE type uses columns of type GEOCODE_TABLE_COLUMN_TYPE to describe the address fields in the input (table to be geocoded) and output (table containing geocoding results). The two types are defined as follows:

create type geocode_table_column_type as object 
  firm  varchar2(32),
  street  varchar2(32),
  street2  varchar2(32),
  cty_subdivision  varchar2(32),
  city  varchar2(2332),
  country_subdivision  varchar2(32),  --state
  country  varchar2(32),
  postal_code  varchar2(32),
  postal_addon_code  varchar2(32),
  lastline  varchar2(32),
  col_name  geocode_value_array,
  col_value  geocode_value_array

create type geocode_schema_property_type as object
  language  varchar2(32),
  character_set  varchar2(32),
  in_table  varchar2(32),
  in_table_cols  geocode_table_column_type,
  out_table  varchar2(32),
  out_table_cols  geocode_table_column_type,
  out_sdo_geom  varchar2(32),
  out_geo_result  varchar2(32),
  in_primary_key  varchar2(32),
  out_foreign_key  varchar2(32),
  DML_option  varchar2(16),
  multi_match_table  varchar2(32),
  reject_table  varchar2(32),
  batch_commit  varchar2(5)

Note the following about the GEOCODE_TABLE_COLUMN_TYPE and GEOCODE_SCHEMA_PROPERTY_TYPE definitions:

C.3.2.1 Multiple Matches and Rejected Records

Tables can be specified to store multiple matches (multi_match_table) and rejected records (reject_table) during a batch geocoding. The primary key will be a user-specified field from the original table. Hence, any single column can be used. Currently, no composite primary keys are supported.

If a single address results in multiple matches, after the batch processing, you can examine multi_match_table and select the correct entries for the original data rows. For example, you can create a table in the following format:

create table <user-defined multimatch table> (
  pk  <same data type as in input table>,
  location  mdsys.sdo_geometry,
  std_addr  mdsys.geocode_result

The match code in the geocode result object indicates the failure during geocoding. The rejection level is used in determining if a record has failed the geocoding. If a record has failed and reject_table is defined, the primary key (specified by the user) is inserted into a rejection table. The interpretation of rejection level is left to the programmer. reject_table can be defined in the following format:

create table <user-defined reject table> (
  pk  <same data type as in input table>,
  matchcode  varchar2(64),
  errcode  varchar2(128)

C.4 Metadata Helper Class

The geocoder metadata is comprehensive. To accelerate development and deployment, Oracle offers a sample class, oracle.spatial.geocoder.Metadata, to allow easy access (read and write) to these objects. Also, SELECT and INSERT SQL statements are constructed automatically for the caller. See the class implementation code for details.

C.5 Single-Record and Interactive Geocoding

Geocoding a row in a table is required when updating or inserting data in the address table. One way to maintain consistency between the base address table and the table of geocoded results is to use a trigger to call the geocoding function. The Java interface method geocode1() will take the primary key to perform the geocoding task and insert or update the geocoded information into the specified table.

The GEOCODER_HTTP package functions are still supported for single-record geocoding. In addition, you are able to pass an address in as parameter, and get back an array of matches. The Java interface takes a metadata structure (see the GEOCODE_SERVER_PROPERTY_TYPE definition in Section C.3.2) and an address structure, and returns an array of this same address structure:

create type geocode_record_type as object
  firm  varchar2(40),
  street  varchar2(40),
  street2  varchar2(40),
  city_subdivision  varchar2(40),
  city  varchar2(40),
  country_subdivision  varchar2(40),
  country  varchar2(40),
  postal_code  varchar2(40),
  postal_addon_code  varchar2(40),
  lastline  varchar2(80),
  latitude  number,
  longitude  number

After performing geocoding, it will return an array (SQL collection type) of such structures as possible matches. In this method, no database table or schema is accessed. This method can enable interactive applications such as store locators.

C.6 Java Geocoder Service Interface

Each geocoder independent software vendor (ISV) must implement the following geocoder interface in order to integrate their products with Oracle Spatial and interMedia Locator.

The interface is defined as follows:

// Geocoder Interface
package oracle.spatial.geocoder;

public interface GeocoderInterface {
  public void geocode(int taskId)
    throws oracle.spatial.geocoder.GeocoderException, java.sql.SQLException;
  public void geocode1(int taskId, BigDecimal pkVal)
   throws oracle.spatial.geocoder.GeocoderException, java.sql.SQLException;

// ... other geocode1 functions with different pkVal types

  public ARRAY interactive_geocode(STRUCT meta, STRUCT inAddr)
    throws oracle.spatial.geocoder.GeocoderException, java.sql.SQLException;

// Geocoder Exception Class
package oracle.spatial.geocoder;

public class GeocoderException extends java.lang.Exception {
  public GeocoderException() {}
  public GeocoderException(String mesg)

Further details, including some of the actual implementation, will be provided to developers by Oracle.

C.7 Enabling Third-Party Geocoders

For customers to implement an Oracle solution with any vendor's Java client, they will have to download a copy of the Java client from the geocoding vendor's Web site, link the geocoder interface package with the vendor's code, and then upload the resulting JSP into the Oracle JVM. Once enabled, the Java client resides on their server and can provide the required services.

To load a client into the database, invoke the Oracle8i loadjava utility, and the Java geocoding method will be exposed as a SQL function call.

The vendor-specific geocoder interface implementation can be owned by any schema, such as MDSYS, a DBA account, or an account determined by the customer or vendor. Appropriate EXECUTE privileges should then be granted to PUBLIC or some set of users of the service.

Prev Next
Copyright © 1999 Oracle Corporation.

All Rights Reserved.