The MDSYS.SDO_GCDR package contains subprograms for geocoding address data.
To use the subprograms in this chapter, you must understand the conceptual and usage information about geocoding in Chapter 11.
Table 23-1 lists the geocoding subprograms.
Table 23-1 Subprograms for Geocoding Address Data
| Subprogram | Description |
|---|---|
|
Creates the CG_COUNTRY_PROFILE, GC_PARSER_PROFILES, and GC_PARSER_PROFILEAFS tables in the caller's schema. |
|
|
Geocodes an unformatted address and returns an SDO_GEOR_ADDR object. |
|
|
Geocodes an input address using attributes in an SDO_GEO_ADDR object, and returns the first matched address as an SDO_GEO_ADDR object. |
|
|
Geocodes an input address using attributes in an SDO_GEO_ADDR object, and returns matching addresses as an SDO_ADDR_ARRAY object. |
|
|
Geocodes all addresses associated with an unformatted address and returns the result as an SDO_ADDR_ARRAY object. |
|
|
Geocodes an unformatted address and returns an SDO_GEOMETRY object. |
|
|
Reverse geocodes a location, specified by its spatial geometry object and country, and returns an SDO_GEO_ADDR object. |
The rest of this chapter provides reference information on the subprograms, listed in alphabetical order.
Creates the CG_COUNTRY_PROFILE, GC_PARSER_PROFILES, and GC_PARSER_PROFILEAFS tables in the caller's schema.
Use this procedure only if your geocoding data provider does not supply the GC_PARSER_PROFILES and GC_PARSER_PROFILEAFS tables. See Section 11.6 for more information.
SDO_GCDR.GEOCODE(
username IN VARCHAR2,
addr_lines IN SDO_KEYWORDARRAY,
country IN VARCHAR2,
match_mode IN VARCHAR2
) RETURN SDO_GEO_ADDR;
Name of the user that owns the tables containing the geocoding data.
An array of quoted strings representing the unformatted address to be geocoded. The SDO_KEYWORDARRAY type is described in Section 11.2.3.
Country name or ISO country code.
Match mode for the geocoding operation. Match modes are explained in Section 11.1.2.
This function returns an object of type SDO_GEOR_ADDR, which is described in Section 11.2.1. It performs the same operation as the SDO_GCDR.GEOCODE_AS_GEOMETRY function; however, that function returns an SDO_GEOMETRY object.
The following example geocodes the address of City Hall in San Francisco, California, using the RELAX_BASE_NAME match mode. It returns the longitude and latitude coordinates of this address as -122.41815 and 37.7784183, respectively.
SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl',
'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME') FROM DUAL;
SDO_GCDR.GEOCODE('SCOTT',SDO_KEYWORDARRAY('1CARLTONBGOODLETTPL','SANFRANCISCO
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'CARLTON B GOODLETT PL', NULL, NULL, '
SAN FRANCISCO', NULL, 'CA', 'US', '94102', NULL, '94102', NULL, '1', 'CARLTON B
GOODLETT', 'PL', 'F', 'F', NULL, NULL, 'L', .01, 23614360, '????#ENUT?B281CP?',
1, 'RELAX_BASE_NAME', -122.41815, 37.7784183, '????0101010??000?')
SDO_GCDR.GEOCODE_ADDR(
gc_username IN VARCHAR2,
address IN SDO_GEO_ADDR
) RETURN SDO_GEO_ADDR;
Geocodes an input address using attributes in an SDO_GEO_ADDR object, and returns the first matched address as an SDO_GEO_ADDR object.
Name of the user that owns the tables containing the geocoding data.
An SDO_GEO_ADDR object with one or more attributes set. The SDO_GEO_ADDR type is described in Section 11.2.1.
This function enables you to specify as many attributes in the input SDO_GEO_ADDR object as you can or want to set. It finds the first matching address, and returns an SDO_GEO_ADDR object with all possible attributes set.
Unlike the SDO_GCDR.GEOCODE function, which geocodes input addresses specified by unformatted address lines, the SDO_GCDR.GEOCODE_ADDR function input addresses specified by individual addressing fields defined in SDO_GEO_ADDR objects. When you use unformatted address lines, you rely on the geocoding software to parse the input address and decompose it into individual address fields. This process usually works well, but it can produce undesirable results if the input addresses are not well formatted. By contrast, when you specify parts of the input address as SDO_GEO_ADDR object attributes, you can reduce the chance of geocoding errors and produce more desirable results.
For examples of the SDO_GCDR.GEOCODE_ADDR function, see Example 11-2 and Example 11-3 in Section 11.4.
See also the SDO_GCDR.GEOCODE_ADDR_ALL function, which performs the same operation as this function, but which can return more than one address.
The following example returns the geocoded result for a point of interest named CALIFORNIA PACIFIC MEDICAL CTR. The example uses a user-defined function named create_addr_from_placename (as defined in Example 11-2 in Section 11.4) to construct the input SDO_GEO_ADDR object.
SELECT sdo_gcdr.geocode_addr('SCOTT',
create_addr_from_placename('CALIFORNIA PACIFIC MEDICAL CTR', 'US'))
FROM DUAL;
SDO_GCDR.GEOCODE_ADDR('SCOTT',CREATE_ADDR_FROM_PLACENAME('CALIFORNIAPACIFICME
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), 'CALIFORNIA PACIFIC MEDICAL CTR-SF', 'BUCHAN
AN ST', NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94115', NULL, '94115', N
ULL, '2333', NULL, NULL, 'F', 'F', NULL, NULL, 'L', 0, 23599031, '??????????B281
CP?', 4, 'DEFAULT', -122.43097, 37.79138, '????4141114??404?')
SDO_GCDR.GEOCODE_ADDR_ALL(
gc_username IN VARCHAR2,
address IN SDO_GEO_ADDR,
max_res_num IN NUMBER DEFAULT 4000
) RETURN SDO_ADDR_ARRAY;
Geocodes an input address using attributes in an SDO_GEO_ADDR object, and returns matching addresses as an SDO_ADDR_ARRAY object (described in Section 11.2.2).
Name of the user that owns the tables containing the geocoding data.
An SDO_GEO_ADDR object with one or more attributes set. The SDO_GEO_ADDR type is described in Section 11.2.1.
Maximum number of results to return in the SDO_ADDR_ARRAY object. The default value is 4000.
This function enables you to specify as many attributes in the input SDO_GEO_ADDR object as you can or want to set. It finds matching addresses (up to 4000 or the limit specified in the max_res_num parameter), and returns an SDO_ADDR_ARRAY object in which each geocoded result has all possible attributes set.
This function performs the same operation as the SDO_GCDR.GEOCODE_ADDR function, except that it can return more than one address. See the Usage Notes for the SDO_GCDR.GEOCODE_ADDR function for more information.
The following example returns up to three geocoded results for a point of interest named CALIFORNIA PACIFIC MEDICAL CTR. (In this case only one result is returned, because the geocoding data contains only one address matching that point of interest.) The example uses a user-defined function named create_addr_from_placename (as defined in Example 11-2 in Section 11.4) to construct the input SDO_GEO_ADDR object.
SELECT sdo_gcdr.geocode_addr_all('SCOTT',
create_addr_from_placename('CALIFORNIA PACIFIC MEDICAL CTR', 'US'), 3)
FROM DUAL;
SDO_GCDR.GEOCODE_ADDR_ALL('SCOTT',CREATE_ADDR_FROM_PLACENAME('CALIFORNIAPACIF
--------------------------------------------------------------------------------
SDO_ADDR_ARRAY(SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), 'CALIFORNIA PACIFIC MEDICAL C
TR-SF', 'BUCHANAN ST', NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94115', N
ULL, '94115', NULL, '2333', NULL, NULL, 'F', 'F', NULL, NULL, 'L', 0, 23599031,
'??????????B281CP?', 4, 'DEFAULT', -122.43097, 37.79138, '????4141114??404?'))
SDO_GCDR.GEOCODE_ALL(
gc_username IN VARCHAR2,
addr_lines IN SDO_KEYWORDARRAY,
country IN VARCHAR2,
match_mode IN VARCHAR2
) RETURN SDO_ADDR_ARRAY;
Geocodes all addresses associated with an unformatted address and returns the result as an SDO_ADDR_ARRAY object.
Name of the user that owns the tables containing the geocoding data.
An array of quoted strings representing the unformatted address to be geocoded. The SDO_KEYWORDARRAY type is described in Section 11.2.3.
Country name or ISO country code.
Match mode for the geocoding operation. Match modes are explained in Section 11.1.2.
This function returns an object of type SDO_ADDR_ARRAY, which is described in Section 11.2.2. It performs the same operation as the SDO_GCDR.GEOCODE function; however, it can return results for multiple addresses, in which case the returned SDO_ADDR_ARRAY object contains multiple SDO_GEO_ADDR objects. If your application needs to select one of the addresses for some further operations, you can use the information about each returned address to help you make that selection.
Each SDO_GEO_ADDR object in the returned SDO_ADDR_ARRAY array represents the center point of each street segment that matches the criteria in the addr_lines parameter. For example, if Main Street extends into two postal codes, or if there are two separate streets named Main Street in two separate postal codes, and if you specify Main Street and a city and state for this function, the returned SDO_ADDR_ARRAY array contains two SDO_GEO_ADDR objects, each reflecting the center point of Main Street in a particular postal code. The house or building number in each SDO_GEO_ADDR object is the house or building number located at the center point of the street segment, even if the input address contains no house or building number or a nonexistent number.
The following example returns an array of geocoded results, each result reflecting the center point of Clay Street in all postal codes in San Francisco, California, in which the street extends. The resulting array includes four SDO_GEOR_ADDR objects, each reflecting the house at the center point of the Clay Street segment in each of the four postal codes (94108, 94115, 94118, and 94109) into which Clay Street extends.
SELECT SDO_GCDR.GEOCODE_ALL('SCOTT',
SDO_KEYWORDARRAY('Clay St', 'San Francisco, CA'),
'US', 'DEFAULT') FROM DUAL;
SDO_GCDR.GEOCODE_ALL('SCOTT',SDO_KEYWORDARRAY('CLAYST','SANFRANCISCO,CA'),'US
--------------------------------------------------------------------------------
SDO_ADDR_ARRAY(SDO_GEO_ADDR(1, SDO_KEYWORDARRAY(), NULL, 'CLAY ST', NULL, NULL,
'SAN FRANCISCO', NULL, 'CA', 'US', '94109', NULL, '94109', NULL, '1698', 'CLAY',
'ST', 'F', 'F', NULL, NULL, 'L', 0, 23600700, '????#ENUT?B281CP?', 1, 'DEFAULT'
, -122.42093, 37.79236, '????4101010??004?'), SDO_GEO_ADDR(1, SDO_KEYWORDARRAY()
, NULL, 'CLAY ST', NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94111', NULL,
'94111', NULL, '398', 'CLAY', 'ST', 'F', 'F', NULL, NULL, 'L', 0, 23600678, '??
??#ENUT?B281CP?', 1, 'DEFAULT', -122.40027, 37.79499, '????4101010??004?'), SDO_
GEO_ADDR(1, SDO_KEYWORDARRAY(), NULL, 'CLAY ST', NULL, NULL, 'SAN FRANCISCO', NU
LL, 'CA', 'US', '94108', NULL, '94108', NULL, '978', 'CLAY', 'ST', 'F', 'F', NUL
L, NULL, 'L', 0, 23600689, '????#ENUT?B281CP?', 1, 'DEFAULT', -122.40904, 37.793
85, '????4101010??004?'), SDO_GEO_ADDR(1, SDO_KEYWORDARRAY(), NULL, 'CLAY ST', N
ULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94115', NULL, '94115', NULL, '279
8', 'CLAY', 'ST', 'F', 'F', NULL, NULL, 'L', 0, 23600709, '????#ENUT?B281CP?', 1
, 'DEFAULT', -122.43909, 37.79007, '????4101010??004?'), SDO_GEO_ADDR(1, SDO_KEY
WORDARRAY(), NULL, 'CLAY ST', NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94
118', NULL, '94118', NULL, '3698', 'CLAY', 'ST', 'F', 'F', NULL, NULL, 'L', 0, 2
3600718, '????#ENUT?B281CP?', 1, 'DEFAULT', -122.45372, 37.78822, '????4101010??
004?'))
SDO_GCDR.GEOCODE_AS_GEOMETRY(
username IN VARCHAR2,
addr_lines IN SDO_KEYWORDARRAY,
country IN VARCHAR2
) RETURN SDO_GEOMETRY;
Name of the user that owns the tables containing the geocoding data.
An array of quoted strings representing the unformatted address to be geocoded. The SDO_KEYWORDARRAY type is described in Section 11.2.3.
Country name or ISO country code.
This function returns an object of type SDO_GEOMETRY. It performs the same operation as the SDO_GCDR.GEOCODE function; however, that function returns an SDO_GEOR_ADDR object.
This function uses a match mode of 'DEFAULT' for the geocoding operation. Match modes are explained in Section 11.1.2.
The following example geocodes the address of City Hall in San Francisco, California. It returns an SDO_GEOMETRY object in which the longitude and latitude coordinates of this address are -122.41815 and 37.7784183, respectively.
SELECT SDO_GCDR.GEOCODE_AS_GEOMETRY('SCOTT',
SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'),
'US') FROM DUAL;
SDO_GCDR.GEOCODE_AS_GEOMETRY('SCOTT',SDO_KEYWORDARRAY('1CARLTONBGOODLETTPL','
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-122.41815, 37.7784183, NULL), NULL, NUL
L)
SDO_GCDR.REVERSE_GEOCODE(
username IN VARCHAR2,
location IN SDO_GEOMETRY,
country IN VARCHAR2
) RETURN SDO_GEO_ADDR;
Reverse geocodes a location, specified by its spatial geometry object and country, and returns the result as an SDO_GEO_ADDR object.
Name of the user that owns the tables containing the geocoding data.
An SDO_GEOMETRY object that specifies the point location to be reverse geocoded.
Country name or ISO country code.
This function returns an object of type SDO_GEOR_ADDR, which is described in Section 11.2.1.
A spatial index must be created on the table GC_ROAD_SEGMENT_<table-suffix>.
The following example reverse geocodes a point with the longitude and latitude values (-122.41815, 37.7784183). For this example, a spatial index was created on the GEOMETRY column in the GC_ROAD_SEGMENT_US table.
SELECT SDO_GCDR.REVERSE_GEOCODE('SCOTT',
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-122.41815, 37.7784183, NULL), NULL, NULL),
'US') FROM DUAL;
SDO_GCDR.REVERSE_GEOCODE('SCOTT',SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.4
--------------------------------------------------------------------------------
SDO_GEO_ADDR(0, SDO_KEYWORDARRAY(), NULL, 'POLK ST', NULL, NULL, 'SAN FRANCISCO'
, NULL, 'CA', 'US', '94102', NULL, '94102', NULL, '200', 'POLK', 'ST', 'F', 'F',
NULL, NULL, 'R', .00966633, 23614360, '', 1, 'DEFAULT', -122.41815, 37.7784177,
'????4141414??404?')