C
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 location information as instances of predefined object types. This interface is part of the geocoding framework in the Oracle Spatial and Oracle 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 Oracle interMedia Locator, 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 gathering and processing functions. Results of analyses or queries can be presented as maps, in addition to tabular formats, using third-party software integrated with Oracle interMedia Locator.

This chapter 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 point 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 Locator Implementation: Benefits and Limitations

Oracle interMedia Locator contains a set of application programming interface (API) functions that allows the integration of Oracle Spatial with third-party geocoding products and Web-based geocoding services. 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 which fields to extract and where to store them. However, to use the full range of features of Oracle Spatial or Oracle 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, 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-at-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 Oracle 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 and Oracle interMedia Locator, the geocoding metadata must exist in each user's schema. Each user of the geocoder service must have tables that implement the metadata schema.

The third-party geocoding service must be installed on a local network and be accessible through standard communication protocols such as sockets, HTTP, or CORBA.

Figure C-1 shows the Oracle geocoding framework.

Figure C-1 Oracle Geocoding Framework

Illustration of the Oracle geocoding framework, as described in this section.

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 which 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 lets the user simultaneously geocode many records from one table. Batch geocoding requires the following:

Geocoding server setup, instructing the client where and how to connect to the geocoding service.
Associating input fields and output fields with columns in the database tables. This is called the schema setup.
Specifying how to handle geocoding situations such as rejects, multiple matches, or exceptions.

Thus, the metadata table consists of a task ID, geocoding 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
  geocoder_info  MYSYS.GEOCODE_SERVER_PROPERTY_TYPE,
  schema_info  MDSYS.GEOCODE_SCHEMA_PROPERTY_TYPE
);

Note the following about the GEOCODE_TASK_METADATA table:

The metadata is divided into a server object (described in Section C.3.1) and a schema object (described in Section C.3.2).
Each object is identified by a unique task_id value.

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:

SERVERS is an array of character strings each in the form Machine:Port that uniquely identifies the geocoding service on the network. This also supports multiple services on the same network by providing an array of servers. Some geocoders, for example, can switch to secondary servers in the case of failures.
PROTOCOL allows different transport mechanisms, such as HTTP or socket.
Additional PROPERTY_NAME and PROPERTY_VALUE arrays allow customization for unique geocoder processing options. They are not intended to be used for name or password information, because a local geocoding service usually does not require this information.
REJECT_LEVEL is a vendor-specific value that defines the criteria for rejecting a record. It is up to the implementation of the Java interface to interpret the value.
BATCH_SIZE indicates how many records to send to the geocoder at one time.

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 geocoded 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 geocoded 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:

LANGUAGE and CHARACTER_SET are for internationalization.
IN_TABLE identifies the name of the input address table (for example, CUSTOMERS).
IN_TABLE_COLS identifies the standard set of fields for geocoding. The fields in the object are standard, and LASTLINE is redundant with the combination of CITY, STATE, POSTAL_CODE, and POSTAL_ADDON_CODE. Only one (LASTLINE, or the combination of CITY, STATE, POSTAL_CODE, and POSTAL_ADDON_CODE) should be specified.
OUT_TABLE and OUT_TABLE_COLS have the same meaning as IN_TABLE and IN_TABLE_COLS, except that these are the column names where the results are stored. Either a subset or all the OUT_TABLE_COLS fields can be null. OUT_TABLE_COLS and GEOCODE_RESULT contain similar information, that is, the standardized (corrected) address in case of successful geocoding. Users can choose to store the standardized address in two forms, expanded into a set of columns or as a single object.
If the actual address definition differs from the fields in the GEOCODE_TABLE_COLUMN_TYPE definition, adjust the field mappings and insert null values as needed. For example, assume an input table CUSTOMERS defined as follows:
```
 (custname  varchar2(32), 
  company  varchar2(32), 
  street  varchar2(64), 
  city  varchar2(32), 
  state  varchar2(32), 
  country  varchar2(32), 
  zip  varchar2(9)) 
```
In the GEOCODE_SCHEMA_PROPERTY_TYPE column definition, the IN_TABLE_COLS attribute value would be specified as: geocode_table_column_type(`company', `street', null, null, `city', `state', `country', `zip', null, null, null, null).

The COL_NAME and COL_VALUE information will be used for feature enhancement for individual geocoding services.

OUT_SDO_GEOM and OUT_GEO_RESULT: SDO_GEOMETRY and GEOCODE_RESULT are the two database objects for storing a standard set of geocoded results, including standardized address and latitude/longitude information. If you are using Oracle Spatial, it is required that SDO_GEOMETRY objects be stored in the database. MDSYS.GEOCODE_RESULT exists in the current Locator implementation and is defined as follows:

Create type geocode_result as object  (
  matchcode  varchar2(16),
  firmname  varchar2(512),
  addrline  varchar2(512),
  addrline2  varchar2(512),
  city  varchar2(512),
  state  varchar2(512),
  zip  varchar2(5),
  zip4  varchar2(4),
  lastline  varchar2(512),
  county  varchar2(32),
  block  varchar2(32),
  loccode  varchar2(16),
  cart  varchar2(16),
  dpbc  varchar2(16),
  lotcode  vrchar2(16),
  lotnum  varchar2(16)
);

IN_PRIMARY_KEY and OUT_FOREIGN_KEY designate a primary key and foreign key, respectively. Using a primary key and foreign key pair is a way to associate the input records to the output records, and is essential when the database stores the output results. Even if the input table and output table are the same, a primary key and foreign key pair (essentially the same column: for example, ID or ROWID) must be specified. There is no restriction on the data type, because no manipulation of the data is needed.
DML_OPTION specifies whether to insert geocoded data into a new row in the result table (INSERT) or update existing rows in the table (UPDATE). If IN_TABLE is the same as OUT_TABLE, then DML_OPTION must be UPDATE, because adding new rows in an existing table is unnecessary. If IN_TABLE is different from OUT_TABLE and if UPDATE is specified, OUT_TABLE must have partial records available for primary and foreign key lookup. This permits the service to locate the exact row to update with the new objects.
MULTI_MATCH_TABLE and REJECT_TABLE are table names where the primary key of the multiple matches and rejected records are stored. If these tables do not exist, they will be created automatically. Automatic creation is the preferred approach due to the fixed structure. The REJECT_TABLE table will be created with a primary key column type in the input table, a match code column, and an optional error message column. The MULTI_MATCH_TABLE table will contain a primary key, SDO_GEOMETRY, and GEO_RESULT. If these fields are null, no table will be created and no multiple matches will be returned.
BATCH_COMMIT is a string containing TRUE or FALSE, indicating if a commit operation should be performed after each batch. If FALSE is specified, a large rollback segment will be needed for large address table geocoding.

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 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 a parameter, and get back an array of matches. The Java interface takes a metadata structure (see the GEOCODE_SCHEMA_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. With 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 to integrate their products with Oracle Spatial and Oracle 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)
  {
    super(mesg);
  }
}

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

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 geocoder 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 the vendor's 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. The owner must grant the appropriate EXECUTE privileges to PUBLIC or some set of users of the service.

C Generic Geocoding Interface