Oracle8i interMedia Locator User's Guide and Reference
Release 8.1.5

A67298-01

Library

Product

Contents

Index

Prev Next

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:

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.

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.

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

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;

/








Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index