2
interMedia Locator Functions

2.1 interMedia Locator Implementation

The implementation of Oracle8i interMedia Locator functions consists of a set of object types, an index method type, and an operator on these types. A geometry is stored in a single row in a column of type SDO_GEOMETRY. Spatial index creation and maintenance is done using data definition language (DDL) (CREATE, ALTER, DROP) and data manipulation language (DML) (INSERT, UPDATE, DELETE) statements.

2.1.1 interMedia Locator Structures

The geometric description of an interMedia Locator object is stored in a single row in a column of type SDO_GEOMETRY. This row is in a user-defined table that has one primary key column (or a set columns that constitute a primary key) and optionally one or more attribute columns.

The object type SDO_GEOMETRY is defined as:


Create Type SDO_GEOMETRY as object (

SDO_GTYPE NUMBER, 

SDO_SRID NUMBER, 

SDO_POINT SDO_POINT_TYPE,

SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,

SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);

The attributes have the following semantics:

SDO_GTYPE - Indicates the type of the geometry. The valid geometry type is:
```
1 = POINT 
```
The geometry type must always be 1.
SDO_SRID - Spatial reference identifier. This is always NULL.
SDO_POINT - Is an object type with attributes X, Y, and Z; all of type NUMBER represented as longitude, latitude, and NULL, respectively.
SDO_ELEM_INFO - Is always NULL.
SDO_ORDINATES - Is always NULL.

2.2 Results Definition and Geocode Functions

This section contains a description of the geocode result object type definition and the call interface described by two geocode functions as shown in Table 2-1.

***Table 2-1 interMedia Locator Functions and Procedures***

Type/Function	Description
GEOCODE_RESULT object	Geocode result object definition
GEOCODE1 function	Geocode function that contains a lastline field; but no city, state, or postal code (zip) fields
GEOCODE1 function	Geocode function that contains city, state, and postal code (zip) fields, but no lastline field

GEOCODE_RESULT Object

Purpose

This object describes the geocode result definition.

Syntax


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   varchar2(16), 

   lotnum    varchar2(16) 

); 

/

Parameters

matchcode

Match result, indicating the quality of a match

firmname

Firm name

addrline

Address line 1

addrline2

Address line 2

city

City

state

State

zip

Postal (zip) code

zip4

Plus 4 digit zip code

lastline

City, state, zip code

county

Federal information processing (FIPS) county code

block

Census block identifier

loccode

Location code

cart

Carrier route (postal service)

dpbc

Delivery point bar code

lotcode

Line of travel code

lotnum

Line of travel number

Usage Notes

In their implementation of interMedia Locator, geocode vendors may make use of all or most fields in the GEOCODE_RESULT table. See the vendor's documentation for a complete description of this object and the fields used.

Exceptions

Application-specific exceptions:

http_error, -20000

geocoder_error, -20001

unit_error, -20003

GEOCODE1 Function (with lastline field)

Purpose

This function is used for geocoding and includes a lastline field that contains city, state, and zip code information.

Syntax


function GEOCODE1(url       in varchar2, 

                  proxy     in varchar2, 

                  name      in varchar2, 

                  pwd       in varchar2, 

                  firmname  in varchar2, 

                  addrline  in varchar2, 

                  addrline2 in varchar2, 

                  lastline  in varchar2, 

                  mm        in varchar2, 

                  stdaddr   out MDSYS.GEOCODE_RESULT, 

                  location  out MDSYS.SDO_GEOMETRY) return varchar2; 

pragma restrict_references(GEOCODE1, WNDS, WNPS);

Parameters

url

Vendor Web site for geocoding: for example, www.centrus-software.com/oracle/geoservice.dll

proxy

Security protection mechanisms (firewall) address, NULL or '' if none

name

Customer name, (for accounting)

pwd

Password (for accounting)

firmname

Firm name

addrline

Address line 1

addrline2

Address line 2

lastline

Contains city, state, postal (zip) code, and zip4 information

mm

Matchmode; a string telling the vendor which match mode to use, such as STANDARD, NORMAL, and so forth.
See vendor sites for more information.

stdaddr

Standard address object or output geocode result object (defined previously)

location

Locator geometry object, SDO_GEOMETRY, containing latitude and longitude information

Return Value

This return value is the error code returned as a string by the geocode vendor; typically, the string contains an error code and a message, such as 0:SUCCESS. See the specific vendor documentation for more information.

Usage Notes

The lastline field contains the city, state, and postal (zip) code information.

Exceptions

None.

Examples

Example 1: Geocode a single record interactively.


-- Geocode a single record interactively.

set serveroutput on

set timing on

set pagesize 50000



declare

  geo_result MDSYS.GEOCODE_RESULT;

  geom MDSYS.SDO_GEOMETRY;

  result varchar2(255);

begin

  result := geocoder_http.GEOCODE1(

              'http://www.centrus-software.com/oracle/geoservice.dll',

              'www-proxy.us.acme.com',

              'user', 'password',

              'oracle','1 oracle dr','', 'nashua NH 03062',

              'tight',

              geo_result, geom);

  dbms_output.put_line(result);

exception

when geocoder_http.http_error then

   dbms_output.put_line('Internet problem - cannot connect');

when geocoder_http.geocoder_error then

   dbms_output.put_line('Geocoder problem - contact vendor');

when others then

   dbms_output.put_line('Oracle Error - check your PL/SQL');

end;

/

Example 2: Geocode a table in batch mode using the entire object.


-- See how to create this sample table using the file nh_cs.sql 

-- Geocode a table in batch mode using the entire object.



-- HOW TO CUSTOMIZE IT FOR YOUR USE:

-- 1. Change the select statement in declaration section to match 

--    your input table; 

--    If you are placing the geocode result into the same table, make sure 

--     rowid is selected; if you are geocoding into a different table, make sure 

--    the primary keys are selected.

--

-- 2. In the update call at the end, if you are placing all your results

--    back to the same table, use update ... where rowid = r.rowid;

--    otherwise, use insert into ... where pk = r.pk;

--

-- 3. Exception handling:

--    The routine generates http_error and geocoder_error.

--    HTTP_ERROR corresponds to transmission problem. 

--    GEOCODER_ERROR is when an address record cannot be matched by the 

--     geocoder from the vendor Web site, and the result you get back is likely 

--    to be null.

--    You should decide how to handle these errors according to your 

--    own needs.

--    The GEOCODER_ERROR exception can be examined in the result variable.

--

declare

  CURSOR crs is

     select company, address, city, state, zipcode, rowid from 

nh_computer_stores;

  standard_address MDSYS.GEOCODE_RESULT;

  geom_location MDSYS.SDO_GEOMETRY;

  result varchar2(255);

begin

  for r in crs loop

   begin

    result := geocoder_http.GEOCODE1(

      'http://www.centrus-software.com/oracle/geoservice.dll',

      'www-proxy.us.acme.com',

      'user','password',

      r.company,

      r.address, '',

      r.city, r.state, r.zipcode,

      'normal',

      standard_address,

      geom_location);

   exception

   when geocoder_http.geocoder_error then

      dbms_output.put_line('Geocoder error, continuing');

   when others then

      dbms_output.put_line('HTTP or server error, quit');

      exit;

   end;

   update nh_computer_stores

      set std_addr = standard_address, location = geom_location

      where rowid = r.rowid;

<<end_loop>>

    null;

  end loop;

end;

/

Example 3: Geocode a table in batch mode using fields in the object.


-- Geocode a table in batch mode using fields in the object.



-- HOW TO CUSTOMIZE IT FOR YOUR USE:

-- 1. Change the select statement in declaration section to match 

--    your input table; 

--    If you are placing the geocode result into the same table, make sure 

--     rowid is selected; if you are geocoding into a different table, make sure 

--    the primary keys are selected.

--

-- 2. In the update call at the end, if you are placing all your results

--    back to the same table, use update ... where rowid = r.rowid;

--    otherwise, use insert into ... where pk = r.pk;

--

-- 3. Exception handling:

--    The routine generates http_error and geocoder_error.

--    HTTP_ERROR corresponds to transmission problem. 

--    GEOCODER_ERROR is when an address record cannot be matched by the 

--     geocoder from the vendor Web site, and the result you get back is likely 

--    to be null.

--    You should decide how to handle these errors according to your 

--    own needs.

--    The GEOCODER_ERROR exception can be examined in the result variable.

--

declare

  CURSOR crs is

     select company, address, city, state, zipcode, rowid from 

nh_computer_stores;

  standard_address MDSYS.GEOCODE_RESULT;

  geom_location MDSYS.SDO_GEOMETRY;

  result varchar2(255);

begin

  for r in crs loop

   begin

    result := geocoder_http.GEOCODE1(

      'http://www.centrus-software.com/oracle/geoservice.dll',

      'www-proxy.us.acme.com',

      'user','password',

      r.company,

      r.address, '',

      r.city, r.state, r.zipcode,

      'normal',

      standard_address,

      geom_location);

   exception

   when geocoder_http.geocoder_error then

      dbms_output.put_line('Geocoder error, continuing');

   when others then

      dbms_output.put_line('HTTP or server error, quit');

      exit;

   end;

   update nh_computer_stores

      set std_street = standard_address.address,

      std_city = standard_address.city,

      std_state = standard_address.state,

      std_zip = standard_address.zip,

      std_zip4 = standard_address.zip4,

      location = geom_location

      where rowid = r.rowid;

<<end_loop>>

    null;

  end loop;

end;

/

GEOCODE1 Function (with city, state, and postal code (zip) fields)

Purpose

This function is used for geocoding and includes city, state, and postal (zip) code fields.

Syntax


function GEOCODE1(url       in varchar2, 

                  proxy     in varchar2, 

                  name      in varchar2, 

                  pwd       in varchar2, 

                  firmname  in varchar2, 

                  addrline  in varchar2, 

                  addrline2 in varchar2, 

                  city      in varchar2, 

                  state     in varchar2, 

                  zip       in varchar2, 

                  mm        in varchar2, 

                  stdaddr   out MDSYS.GEOCODE_RESULT, 

                  location  out MDSYS.SDO_GEOMETRY) return varchar2; 

pragma restrict_references(GEOCODE1, WNDS, WNPS);

Parameters

url

Vendor Web site for geocoding: for example, www.centrus-software.com/oracle/geoservice.dll

proxy

Security protection mechanisms (firewall) address, NULL or '' if none

name

Customer name, (for accounting)

pwd

Password (for accounting)

firmname

Firm name

addrline

Address line 1

addrline2

Address line 2

city

City name

state

State name

zip

Postal (zip) code

mm

Matchmode; a string telling the vendor which match mode to use, such as STANDARD, NORMAL, and so forth
See vendor sites for more information.

stdaddr

Standard address object or output geocode result object (defined previously)

location

Locator geometry object, SDO_GEOMETRY, containing latitude and longitude information

Return Value

The return value is the error code returned as a string by the geocode vendor; typically, the string contains an error code and a message, such as 0:SUCCESS. See the specific vendor documentation for more information.

Usage Notes

The city, state, and postal (zip) fields replace the lastline field described in the previous function.

Exceptions

None.

Examples

See the examples in the previous GEOCODE1 function description.

2.3 Estimate Level and Spatial Locator Index

This section describes the ESTIMATE_LEVEL function and the spatial locator index. If you must use the ESTIMATE_LEVEL function, call this function prior to creating the spatial locator index. The spatial locator index must be created before you can use the locator operator described in Section 2.4.

Function/Procedure	Description
ESTIMATE_LEVEL	Estimates an appropriate index_level parameter value when most of your LOCATOR_WITHIN_DISTANCE queries use a radius distance value that exceeds 100 miles.
SETUP_LOCATOR_INDEX	Creates the spatial locator index.

Table 2-2 interMedia Locator ESTIMATE_LEVEL Function and Spatial Locator Index

Function/Procedure Description

ESTIMATE_LEVEL

Estimates an appropriate index_level parameter value when most of your LOCATOR_WITHIN_DISTANCE queries use a radius distance value that exceeds 100 miles.

SETUP_LOCATOR_INDEX

Creates the spatial locator index.

ESTIMATE_LEVEL

Purpose

This function calculates an index_level parameter value for use in the SETUP_LOCATOR_INDEX procedure.

Note:
Only call this function if most of your LOCATOR_WITHIN_DISTANCE queries use a radius distance value greater than 100 miles; otherwise, the default value of 13 is appropriate as the index_level parameter value.

Syntax


function ESTIMATE_LEVEL(radius1 in number, 

                        radius2 in number) return integer;

Parameters

radius1

Small radius in miles.

radius2

Large radius in miles.

Return Value

The return value is the appropriate index_level parameter value to use in the SETUP_LOCATOR_INDEX procedure.

Usage Notes

If you expect to use a large radius distance for queries that is greater than 100 miles, you should call this function to determine the most appropriate index_level parameter value for your data.

A LOCATOR_WITHIN_DISTANCE query with a circular radius distance greater than 100 miles actually degenerates into an ellipse with two semiaxes (radii). Therefore, this function has two parameters, radius1 to represent the small semiaxis and radius2 to represent the large semiaxis of the ellipse. For Oracle8i Release 8.1.5, you should provide the same value for both radius1and radius2 parameters.

If you must call this function, call this function after you geocode your data and before you create your spatial locator index. A more appropriate index_level parameter value is expected to give you better performance on your data.

Exceptions

Application-specific exceptions:

unit_error, -20004

Examples

Create a setup spatial locator index.


select geocoder_http.estimate_level(200,200) from dual;

9

SETUP_LOCATOR_INDEX

Purpose

This procedure creates the spatial locator index.

Syntax


procedure SETUP_LOCATOR_INDEX(tabname in varchar2, 

                              colname in varchar2,

                              index_level in number := 13);

Parameters

tabname

Table name where the spatial information is stored

colname

Column name where the spatial information is stored within 'tabname'

index_level

Value determined by calling the ESTIMATE_LEVEL function when the radius distance exceeds 100 miles and a better index level is required to improve performance on your data
The default value is 13.

Return Value

None.

Usage Notes

This procedure creates a metadata table called SDO_GEOM_METADATA under the invoker's or current user's schema. It creates a special domain index of type spatial_locator_index. The name of the index is:


substr((tabname,1,5)||'_'substr(colname,1,5)||'_idx'||_HL6N1$

Do not delete these extra tables after creating the index.

This procedure must be executed to create the spatial locator index for the geocoded table before you can use the LOCATOR_WITHIN_DISTANCE operator; otherwise, an error message is returned indicating no spatial locator index is created. For example:


ERROR at line 1:

ORA-20000: Interface Not Supported without a Spatial Index

ORA-06512: at "MDSYS.SDO_3GL", line 184

ORA-06512: at line 1

Usually, you do not need to modify the value of the index_level parameter if most of your LOCATOR_WITHIN_DISTANCE queries are using a radius distance value of 100 miles or less. However, to achieve better performance on your data, you can change this value depending on the most popular radius distance for most of your LOCATOR_WITHIN_DISTANCE queries. To estimate a better value for the index_level parameter, call the ESTIMATE_LEVEL function. In this case, you must call the ESTIMATE_LEVEL function before you create your spatial locator index.

Exceptions

None.

Examples

Create a setup spatial locator index.


procedure SETUP_LOCATOR_INDEX('cust_table', 'location', 13);

2.4 Locator Operator

This section describes the function used when working with the interMedia Locator object type.

Function	Description
LOCATOR_WITHIN_DISTANCE	Determines if two points are within a specified geometric distance from one another.

Table 2-3 interMedia Locator Operator

Function Description

LOCATOR_WITHIN_DISTANCE

Determines if two points are within a specified geometric distance from one another.

LOCATOR_WITHIN_DISTANCE

Purpose

This operator uses geometric intersection algorithms and a spatial index to identify the set of spatial points that are within some specified geometric distance (radius distance) of a given point of interest (center of a circle).

Syntax


LOCATOR_WITHIN_DISTANCE(T.Column MDSYS.SDO_GEOMETRY, aGeom MDSYS.SDO_GEOMETRY, 

params VARCHAR2) ;

Parameters

params

Determines the behavior of the operator

Valid keywords and their semantics are described as follows:

distance

Required; the radius distance value

units

Required; the unit value; can be mile, ft (feet), or meter

Return Value

The expression LOCATOR_WITHIN_DISTANCE(arg1, arg2, arg3) = `TRUE' evaluates to TRUE for point pairs that are within the specified distance apart, and FALSE otherwise.

Usage Notes

The distance around a point of interest describes a circle and this distance is defined as the minimum radius distance between these two points.
The operator must always be used in a WHERE clause and the condition that includes the operator should be an expression of the form:
```
LOCATOR_WITHIN_DISTANCE(arg1, arg2, 


`distance = <some_dist_val>, units=mile') = `TRUE'.
```
It is required that T.Column have a spatial locator index built on it. See Section 2.3 for more information.
LOCATOR_WITHIN_DISTANCE( ) is not supported for spatial joins.
The default unit is latitude and longitude. Therefore, you should always specify a unit such as: mile, ft, or meter.

Exceptions

None.

Examples

Example 1: Simple point query.


SELECT A.GID FROM POINTS A WHERE LOCATOR_WITHIN_DISTANCE


(A.Geometry, :aGeom, `distance = 10 units=mile') = `TRUE' ;

Example 2: Computer store query.


Rem

Rem $Header: geolocate.sql 14-sep-98.11:51:16 pfwang Exp $

Rem

Rem geolocate.sql

Rem

Rem  Copyright (c) Oracle Corporation 1998. All Rights Reserved.

Rem



-- This routine dynamically creates a geometry of interest, 

-- for example, Oracle office location. Then it queries against the

-- NH_COMPUTER_STORES table to find out how many computer stores are

-- within a certain distance radius of the office. In this case, 10 miles.



set serveroutput on

set pagesize 50000



declare

  standard_address MDSYS.GEOCODE_RESULT;

  geom_location    MDSYS.SDO_GEOMETRY;

  result           varchar2(255);

  type cur_type is ref cursor ;

  crs cur_type;

begin

  result := geocoder_http.geocode1(

              'http://www.centrus-software.com/oracle/geoservice.dll',

              'www-proxy.us.acme.com',

              'user', 'password',

              'Oracle','1 Oracle Drive','', '03062',

              'tight', standard_address, geom_location);

  if (instr(upper(result),'SUCCESS') = 0) then

     raise geocoder_http.geocoder_error;

  end if;

  open crs for

    'select company from nh_computer_stores where '||

       'MDSYS.LOCATOR_WITHIN_DISTANCE(location, :1, ''distance=10 

units=Mile'')=''TRUE'''

  using geom_location;

  loop

    fetch crs into result;

    exit when crs%NOTFOUND;

    dbms_output.put_line(result);

  end loop;

  close crs;

exception

when geocoder_http.http_error then

   dbms_output.put_line('Internet problem - cannot connect');

when geocoder_http.geocoder_error then

   dbms_output.put_line('Geocoder problem - contact vendor');

when others then

   dbms_output.put_line('Oracle Error - check your PL/SQL');

end;

/

matchcode	Match result, indicating the quality of a match
firmname	Firm name
addrline	Address line 1
addrline2	Address line 2
city	City
state	State
zip	Postal (zip) code
zip4	Plus 4 digit zip code
lastline	City, state, zip code
county	Federal information processing (FIPS) county code
block	Census block identifier
loccode	Location code
cart	Carrier route (postal service)
dpbc	Delivery point bar code
lotcode	Line of travel code
lotnum	Line of travel number

url	Vendor Web site for geocoding: for example, www.centrus-software.com/oracle/geoservice.dll
proxy	Security protection mechanisms (firewall) address, NULL or '' if none
name	Customer name, (for accounting)
pwd	Password (for accounting)
firmname	Firm name
addrline	Address line 1
addrline2	Address line 2
lastline	Contains city, state, postal (zip) code, and zip4 information
mm	Matchmode; a string telling the vendor which match mode to use, such as STANDARD, NORMAL, and so forth. See vendor sites for more information.
stdaddr	Standard address object or output geocode result object (defined previously)
location	Locator geometry object, SDO_GEOMETRY, containing latitude and longitude information

2 interMedia Locator Functions

2.1 interMedia Locator Implementation

2.1.1 interMedia Locator Structures

2.2 Results Definition and Geocode Functions

Table 2-1 interMedia Locator Functions and Procedures

GEOCODE_RESULT Object

Purpose

Syntax

Usage Notes

Exceptions

GEOCODE1 Function (with lastline field)

Purpose

Syntax

Return Value

Usage Notes

Exceptions

Examples

GEOCODE1 Function (with city, state, and postal code (zip) fields)

Purpose

Syntax

Return Value

Usage Notes

Exceptions

Examples

2.3 Estimate Level and Spatial Locator Index

ESTIMATE_LEVEL

Purpose

Syntax

Parameters radius1 Small radius in miles. radius2 Large radius in miles.

Return Value

Usage Notes

Exceptions

Examples

SETUP_LOCATOR_INDEX

Purpose

Syntax

Return Value

Usage Notes

Exceptions

Examples

2.4 Locator Operator

LOCATOR_WITHIN_DISTANCE

Purpose

Syntax

Parameters

Return Value

Usage Notes

Exceptions

Examples

2
interMedia Locator Functions

***Table 2-1 interMedia Locator Functions and Procedures***

Parameters

radius1

Small radius in miles.

radius2

Large radius in miles.