Oracle Spatial User's Guide and Reference Release 8.1.6 A77132-01 |
|
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.
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.
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.
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 geocoder_info MYSYS.GEOCODE_SERVER_PROPERTY_TYPE, schema_info MDSYS.GEOCODE_SCHEMA_PROPERTY_TYPE );
Note the following about the GEOCODE_TASK_METADATA table:
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:
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:
(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.
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) );
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) );
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.
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.
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) { super(mesg); } }
Further details, including some of the actual implementation, will be provided to developers by Oracle.
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.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|