12.4 Geocoding from a Place Name

If you know a place name (point of interest) but not its locality details, you can create a PL/SQL function to construct an SDO_GEO_ADDR object from placename and country input parameters.

This is shown in Example 12-2, which creates a function named create_addr_from_placename. The SELECT statement in this example uses the SDO_GCDR.GEOCODE_ADDR function to geocode the address constructed using the create_addr_from_placename function.

Example 12-2 Geocoding from a Place Name and Country

create or replace function create_addr_from_placename(
placename varchar2,
country varchar2)
return sdo_geo_addr
deterministic
as
  addr sdo_geo_addr ;
  begin
  addr := sdo_geo_addr() ;
  addr.country := country ;
  addr.placename := placename ;
  addr.matchmode := 'default' ;
  return addr ;
  end;
  /
 
SELECT sdo_gcdr.geocode_addr('SCOTT', 
  create_addr_from_placename('CALIFORNIA PACIFIC MEDICAL CTR', 'US')) 
FROM DUAL;

Example 12-3 Geocoding from a Place Name, Country, and Other Fields

If you know at least some of the locality information, such as settlement, region, and postal code, you can get better performance if you can provide such information. Example 12-3 provides an alternate version of the create_addr_from_placename function that accepts additional parameters. To call this version of the function, specify actual values for the placename and country parameters, and specify an actual value or a null value for each of the other input parameters.

create or replace function create_addr_from_placename(
placename varchar2,
city varchar2,
state varchar2,
postalcode varchar2,
country varchar2)
return sdo_geo_addr
deterministic
as
  addr sdo_geo_addr ;
  begin
  addr := sdo_geo_addr() ;
  addr.settlement := city ;
  addr.region := state ;
  addr.postalcode := postalcode ;
  addr.country := country ;
  addr.placename := placename ;
  addr.matchmode := 'default' ;
  return addr ;
  end;
  /
 
SELECT sdo_gcdr.geocode_addr('SCOTT', 
  create_addr_from_placename('CALIFORNIA PACIFIC MEDICAL CTR', 
    'san francisco', 'ca', null, 'US')) FROM DUAL;