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, as shown in Example 11-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 11-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 11-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 11-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;