Skip Headers

Oracle® Spatial User's Guide and Reference
10g Release 1 (10.1)

Part Number B10826-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

20 Geocoding Subprograms

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 5.

Table 20-1 lists the geocoding subprograms.

Table 20-1 Subprograms for Geocoding Address Data

Subprogram Description
SDO_GCDR.GEOCODE
Geocodes an unformatted address and returns an SDO_GEOR_ADDR object.
SDO_GCDR.GEOCODE_ALL
Geocodes all addresses associated with an unformatted address and returns the result as an SDO_ADDR_ARRAY object.
SDO_GCDR.GEOCODE_AS_GEOMETRY
Geocodes an unformatted address and returns an SDO_GEOMETRY object.

The rest of this chapter provides reference information on the subprograms, listed in alphabetical order.


SDO_GCDR.GEOCODE

Format

SDO_GCDR.GEOCODE(

     username IN VARCHAR2,

     addr_lines IN SDO_KEYWORDARRAY,

     country IN VARCHAR2,

     match_mode IN VARCHAR2

     ) RETURN SDO_GEO_ADDR;

Description

Geocodes an unformatted address and returns the result as an SDO_GEO_ADDR object.

Parameters

username

Name of the user that owns the tables containing the geocoding data.

addr_lines

An array of quoted strings representing the unformatted address to be geocoded. The SDO_KEYWORDARRAY type is described in Section 5.2.3.

country

Country name or ISO country code.

match_mode

Match mode for the geocoding operation. Match modes are explained in Section 5.1.2.

Usage Notes

This function returns an object of type SDO_GEOR_ADDR, which is described in Section 5.2.1. It performs the same operation as the SDO_GCDR.GEOCODE_AS_GEOMETRY function; however, that function returns an SDO_GEOMETRY object.

Examples

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, 'nul?#ENUT?B281CP?',
1, 'DEFAULT', -122.41815, 37.7784183) 

SDO_GCDR.GEOCODE_ALL

Format

SDO_GCDR.GEOCODE_ALL(

     gc_username IN VARCHAR2,

     addr_lines IN SDO_KEYWORDARRAY,

     country IN VARCHAR2,

     match_mode IN VARCHAR2

     ) RETURN SDO_ADDR_ARRAY;

Description

Geocodes all addresses associated with an unformatted address and returns the result as an SDO_ADDR_ARRAY object.

Parameters

gc_username

Name of the user that owns the tables containing the geocoding data.

addr_lines

An array of quoted strings representing the unformatted address to be geocoded. The SDO_KEYWORDARRAY type is described in Section 5.2.3.

country

Country name or ISO country code.

match_mode

Match mode for the geocoding operation. Match modes are explained in Section 5.1.2.

Usage Notes

This function returns an object of type SDO_ADDR_ARRAY, which is described in Section 5.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.

Examples

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', '94108', NULL, '94108', NULL, '978', 'CLAY', 
'ST', 'F', 'F', NULL, NULL, 'L', 0, 23600689, 'nul?#ENUT?B281CP?', 1, 'DEFAULT',
-122.40904, 37.79385), SDO_GEO_ADDR(1, SDO_KEYWORDARRAY(), NULL, 'CLAY ST', 
NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94115', NULL, '94115', NULL, '27
98', 'CLAY', 'ST', 'F', 'F', NULL, NULL, 'L', 0, 23600709, 'nul?#ENUT?B281CP?',
1, 'DEFAULT', -122.43909, 37.79007), SDO_GEO_ADDR(1, SDO_KEYWORDARRAY(), NULL
, 'CLAY ST', NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94118', NULL, '9411
8', NULL, '3698', 'CLAY', 'ST', 'F', 'F', NULL, NULL, 'L', 0, 23600718, 'nul?
#ENUT?B281CP?', 1, 'DEFAULT', -122.45372, 37.78822), SDO_GEO_ADDR(1, SDO_KEYWORD
ARRAY(), NULL, 'CLAY ST', NULL, NULL, 'SAN FRANCISCO', NULL, 'CA', 'US', '94109'
, NULL, '94109', NULL, '1698', 'CLAY', 'ST', 'F', 'F', NULL, NULL, 'L', 0, 23
600700, 'nul?#ENUT?B281CP?', 1, 'DEFAULT', -122.42093, 37.79236))

SDO_GCDR.GEOCODE_AS_GEOMETRY

Format

SDO_GCDR.GEOCODE_AS_GEOMETRY(

     username IN VARCHAR2,

     addr_lines IN SDO_KEYWORDARRAY,

     country IN VARCHAR2

     ) RETURN SDO_GEOMETRY;

Description

Geocodes an unformatted address and returns the result as an SDO_GEOMETRY object.

Parameters

username

Name of the user that owns the tables containing the geocoding data.

addr_lines

An array of quoted strings representing the unformatted address to be geocoded. The SDO_KEYWORDARRAY type is described in Section 5.2.3.

country

Country name or ISO country code.

Usage Notes

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 5.1.2.

Examples

The following example geocodes the address of City Hall in San Francisco, California, using the RELAX_BASE_NAME match mode. 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', 'RELAX_BASE_NAME') 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)