2
Locator Functions

2.1 Locator Implementation

The implementation of Oracle 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 Locator Structures

The geometric description of a 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 - 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 - An object type with attributes X, Y, and Z; all of type NUMBER represented as longitude, latitude, and NULL, respectively.
SDO_ELEM_INFO - Always NULL.
SDO_ORDINATES - 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 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

Format

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)

);

Description

Describes the geocode result definition.

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 standards (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 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)

Format

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);

Description

Used for geocoding and includes a lastline field that contains city, state, and zip code information.

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 the 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 in 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 a 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 in 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)

Format

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);

Description

Used for geocoding and includes city, state, and postal (zip) code fields.

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

Format

function ESTIMATE_LEVEL(radius1 in number,

radius2 in number) return integer;

Description

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.

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.7, 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

Format

procedure SETUP_LOCATOR_INDEX(tabname in varchar2,

colname in varchar2,

index_level in number := 13);

Description

Creates the spatial locator index.

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 USER_SDO_GEOM_METADATA under the invoker's or current user's schema. It creates a special domain index of type spatial_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 Locator object type.

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

Table 2-3 Locator Operator

Function Description

LOCATOR_WITHIN_DISTANCE

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

LOCATOR_WITHIN_DISTANCE

Format

LOCATOR_WITHIN_DISTANCE(T.Column MDSYS.SDO_GEOMETRY, aGeom MDSYS.SDO_GEOMETRY, params VARCHAR2);

Description

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).

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: Perform a simple point query.

SELECT A.GID FROM POINTS A WHERE LOCATOR_WITHIN_DISTANCE

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

Example 2: Perform a 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, an 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, the 
-- distance is 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 standards (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 Locator Functions

2.1 Locator Implementation

2.1.1 Locator Structures

2.2 Results Definition and Geocode Functions

Table 2-1 Locator Functions and Procedures

GEOCODE_RESULT Object

Format

Description

Usage Notes

Exceptions

GEOCODE1 Function (with lastline field)

Format

Description

Return Value

Usage Notes

Exceptions

Examples

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

Format

Description

Return Value

Usage Notes

Exceptions

Examples

2.3 Estimate Level and Spatial Locator Index

ESTIMATE_LEVEL

Format

Description

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

Return Value

Usage Notes

Exceptions

Examples

SETUP_LOCATOR_INDEX

Format

Description

Return Value

Usage Notes

Exceptions

Examples

2.4 Locator Operator

LOCATOR_WITHIN_DISTANCE

Format

Description

Parameters

Return Value

Usage Notes

Exceptions

Examples

2
Locator Functions

Parameters

radius1

Small radius in miles.

radius2

Large radius in miles.