A
Sample Programs

Oracle interMedia Locator includes a number of scripts that you can modify and run.

A.1 Sample Scripts

Sample Oracle interMedia Locator scripts are available in the following directory after you install this product:

$ORACLE_HOME/md/demo/geocoder

These scripts consist of the following files:

• geohttp.sql

  This file contains two parts. One part is for running a geocode function in interactive mode and the other is for running the geocode function in batch mode.

  • Interactive mode.

    See Example 1 in "GEOCODE1 Function (with lastline field)" for a listing of this part of the file.

  • Batch mode.

    You must update the setup tables in the nh_cs.sql file before you run geohttp.sql in batch mode. See Example 2 in "GEOCODE1 Function (with lastline field)" or Example 3 in "GEOCODE1 Function (with lastline field)" for a listing of this part of the file.

• geoindex.sql

  This file contains:

  • A function named ESTIMATE_LEVEL to better estimate the index level for use with the spatial locator index for within-distance queries that use a radius distance greater than 100 miles. See the example in "ESTIMATE_LEVEL" for a listing of this file.

  • A procedure statement named SETUP_LOCATOR_INDEX. This statement builds a setup spatial locator index on the location column that contains the spatial information within the cust_table table where the spatial information is stored. See the example in "SETUP_LOCATOR_INDEX" for a listing of this file.

• geolocate.sql

  This file contains a routine that dynamically creates a geometry of interest and then queries against the NH_COMPUTER_STORES table to find out how many stores are within a 10-mile radius of the office. See Example 2 in "LOCATOR_WITHIN_DISTANCE" for a listing of this file.

A.2 Sample Code

Oracle interMedia Locator includes sample code that you can modify and run.

A.2.1 Using Oracle interMedia Locator Services to Find Nearest Neighbor

Oracle interMedia Locator services support the within_distance operator. To facilitate a nearest-neighbor search, you must write a PL/SQL routine to find a number of geometries, and subsequently sort them by distance. The shortest distance between the point of interest and a neighboring point gives the nearest neighbor.

The following code sample illustrates how to find the top three points using Oracle interMedia Locator services. Assume the Porsche dealer and spatial tables are as follows:

create table porsche_dealer ( 
id number constraint pk_id primary key, 
dealer_name varchar2(50), 
address varchar2(50), 
lastline varchar2(50), 
phone varchar2(12), 
fax varchar2(12)); 

create table porsche_spatial ( 
id constraint fk_id references porsche_dealer(id), 
geo_address mdsys.geocode_result, 
location mdsys.sdo_geometry, 
map ordsys.ordimage, 
last_modified date); 

These two tables contain the database on US Porsche dealers. The Porsche dealer table contains dealer attribute data. The Porsche spatial table contains the geocoded location and result for each record in the Porsche dealer table. The goal is to find the top three Porsche dealers around a given customer location. This customer location (in address form) must first be geocoded. After the initial geocoding, the location object (sdo_geometry) is populated with the location for the center of the search. The following sample code illustrates how to find the top three points, using the Oracle interMedia Locator features:

procedure find_top3 
found3 boolean; 
dist number; 
type cursor_type is ref cursor; 
addr mdsys.geocode_result; 
loc mdsys.sdo_geometry; 
crs cursor_type; 
dist number; 
cnt number; 
i number; 
tmp number; 

id_a dbms_sql.number_table; 
company_a dbms_sql.varchar2_table; 
address_a dbms_sql.varchar2_table; 
lastline_a dbms_sql.varchar2_table; 
phone_a dbms_sql.varchar2_table; 
fax_a dbms_sql.varchar2_table; 
x_a dbms_sql.number_table; 
y_a dbms_sql.number_table; 
dist_a dbms_sql.number_table; 
no1 number; 
no2 number; 
no3 number; 

begin 

-- populate the location object by dynamically geocoding an address 
-- geocode_http.geocode1(..., loc, addr); 
-- ... 

found3 := false; 
-- keep increasing distance until you find 3 points 

dist := 0.25; 
while found3 = false loop 

begin 
dist := dist + 0.5; 
open crs for 'select a.id, b.geo_address.firmname, b.geo_address.addrline, 
b.geo_address.lastline, a.phone, a.fax, '|| 
'b.location.sdo_point.x, b.location.sdo_point.y from '|| 
'porsche_dealer a, porsche_spatial b where ' || 
'a.id=b.id and '|| 
'mdsys.locator_within_distance(b.location,'|| 
':1,''distance='|| dist || ''')=''TRUE''' 
using loc; 
cnt := 1; 
loop 
fetch crs into id_a(cnt), company_a(cnt), address_a(cnt), lastline_a(cnt), 
phone_a(cnt), fax_a(cnt), x_a(cnt), y_a(cnt); 
exit when crs%NOTFOUND; 
dbms_output.put_line(company_a(cnt)||'/'||address_a(cnt)||'/'||lastline_a(cnt)); 
cnt := cnt + 1; 
end loop; 
close crs; 

-- dbms_output.put_line(cnt); 
if cnt >= 4 then 
found3 := true; 
end if; 
exception when NO_DATA_FOUND then 

-- htp.print('Start all over again'); 
-- dbms_output.put_line('new radius='||dist); 
if dist > 100 then 
exit; 
end if; 
end; 
end loop; 
cnt := cnt - 1; 

-- find the top 3 candidates 
no1 := 1; 
no2 := 2; 
no3 := 3; 
for i in 1 .. cnt loop 

-- dbms_output.put_line(x_a(i) ||','|| y_a(i)); 
-- calculate distance 

dist_a(i) := (loc.sdo_point.x - x_a(i))*(loc.sdo_point.x - x_a(i)) + 
(loc.sdo_point.y - y_a(i))*(loc.sdo_point.y - y_a(i)); 

end loop; 
for i in 4 .. cnt+1 loop 

-- order the 3 numbers in the bucket 
if dist_a(no1) > dist_a(no2) then 
tmp := no2; 
no2 := no1; 
no1 := tmp; 
end if; 

if dist_a(no1) > dist_a(no3) then 
tmp := no1; 
no1 := no3; 
no3 := tmp; 
end if; 

if dist_a(no2) > dist_a(no3) then 
tmp := no2; 
no2 := no3; 
no3 := tmp; 
end if; 

if (i > cnt) then 
exit; 
end if; 
if dist_a(i) < dist_a(no3) then 
no3 := i; 
end if; 
end loop; 

dbms_output.put_line(company_a(no1)||'\'||address_a(no1)||'\'||lastline_a(no1)); 
dbms_output.put_line(company_a(no2)||'\'||address_a(no2)||'\'||lastline_a(no2)); 
dbms_output.put_line(company_a(no3)||'\'||address_a(no3)||'\'||lastline_a(no3)); 
end; 

A.2.2 Using Oracle interMedia Locator Services to Find Distance Between Two Points

This function is provided as a sample function with an open source. It calculates the true earth distance between two latitude/longitude points in meters. It assumes the earth to be a true spherical object, and the distance returned represents the distance on a great circle on the sphere. Results will have a small percent of inaccuracy (0.5% or less) depending on the point locations on earth.

Input: two SDO_GEOMETRY objects, each with SDO_POINT field filled, including x and y values.

The return value is the distance on earth in meters.

No exception is thrown, because this is a sample program illustrating the algorithm of the code.

function earth_distance(a mdsys.sdo_geometry, b mdsys.sdo_geometry) return 
number as
  R number;
  c number;
  s1 number;
  s2 number;
  t1 number;
  t2 number;
  l number;
  ac number;
begin
  R := 6371.2;
  c := 3.14159265359 / 180.0;
  s1 := a.sdo_point.x;
  t1 := a.sdo_point.y;
  s2 := b.sdo_point.x;
  t2 := b.sdo_point.y;

  s1 := s1 * c;
  t1 := t1 * c;
  s2 := s2 * c;
  t2 := t2 * c;
  ac := cos(t1)*cos(t2)*cos(s2-s1)+sin(t1)*sin(t2);
  l := R * acos(ac) * 1000.0;
  return l;
end earth_distance;