12.2 Data Types for Geocoding
This topic describes the data types specific to geocoding functions and procedures.
12.2.1 SDO_GEO_ADDR Type
The SDO_GEO_ADDR object type is used to describe an address. When a geocoded address is output by an SDO_GCDR function or procedure, it is stored as an object of type SDO_GEO_ADDR.
Table 12-6 lists the attributes of the SDO_GEO_ADDR type. Not all attributes will be relevant in any given case. The attributes used for a returned geocoded address depend on the geographical context of the input address, especially the country.
Table 12-6 SDO_GEO_ADDR Type Attributes
Attribute | Data Type | Description |
---|---|---|
Id |
NUMBER |
(Not used.) |
AddressLines |
SDO_KEYWORDARRAY |
Address lines. (The SDO_KEYWORDARRAY type is described in SDO_KEYWORDARRAY Type.) |
PlaceName |
VARCHAR2(200) |
Point of interest (POI) name. Example: CALIFORNIA PACIFIC MEDICAL CTR |
StreetName |
VARCHAR2(200) |
Street name, including street type. Example: MAIN ST |
IntersectStreet |
VARCHAR2(200) |
Intersecting street. |
SecUnit |
VARCHAR2(200) |
Secondary unit, such as an apartment number or building number. |
Settlement |
VARCHAR2(200) |
Lowest-level administrative area to which the address belongs. (See Table 12-1.) |
Municipality |
VARCHAR2(200) |
Administrative area above settlement. (See Table 12-1.) |
Region |
VARCHAR2(200) |
Administrative area above municipality (if applicable), or above settlement if municipality does not apply. (See Table 12-1.) |
Country |
VARCHAR2(100) |
Country name or ISO country code. |
PostalCode |
VARCHAR2(20) |
Postal code (optional if administrative area information is provided). In the United States, the postal code is the 5-digit ZIP code. |
PostalAddOnCode |
VARCHAR2(20) |
String appended to the postal code. In the United States, the postal add-on code is typically the last four numbers of a 9-digit ZIP code specified in "5-4" format. |
FullPostalCode |
VARCHAR2(20) |
Full postal code, including the postal code and postal add-on code. |
POBox |
VARCHAR2(100) |
Post Office box number. |
HouseNumber |
VARCHAR2(100) |
House or building number. Example: 123 in 123 MAIN ST |
BaseName |
VARCHAR2(200) |
Base name of the street. Example: MAIN in 123 MAIN ST |
StreetType |
VARCHAR2(20) |
Type of the street. Example: ST in 123 MAIN ST |
StreetTypeBefore |
VARCHAR2(1) |
(Not used.) |
StreetTypeAttached |
VARCHAR2(1) |
(Not used.) |
StreetPrefix |
VARCHAR2(20) |
Prefix for the street. Example: S in 123 S MAIN ST |
StreetSuffix |
VARCHAR2(20) |
Suffix for the street. Example: NE in 123 MAIN ST NE |
Side |
VARCHAR2(1) |
Side of the street ( |
Percent |
NUMBER |
Number from 0 to 1 (multiply by 100 to get a percentage value) indicating how far along the street you are when traveling following the road segment orientation. |
EdgeID |
NUMBER |
Edge ID of the road segment. |
ErrorMessage |
VARCHAR2(20) |
Error message (see Error Messages for Output Geocoded Addresses). Note: You are encouraged to use the |
MatchCode |
NUMBER |
Match code (see Match Codes). |
MatchMode |
VARCHAR2(30) |
Match mode (see Match Modes). |
Longitude |
NUMBER |
Longitude coordinate value. |
Latitude |
NUMBER |
Latitude coordinate value. |
MatchVector |
VARCHAR2(20) |
A string that indicates how each address attribute has been matched against the data used for geocoding (see Match Vector for Output Geocoded Addresses). |
You can return the entire SDO_GEO_ADDR object, or you can specify an attribute using standard "dot" notation. Example 12-1 contains statements that geocode the address of the San Francisco City Hall; the first statement returns the entire SDO_GEO_ADDR object, and the remaining statements return some specific attributes.
Example 12-1 Geocoding, Returning Address Object and Specific Attributes
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?') SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME').StreetType FROM DUAL; SDO_GCDR.GEOCODE('SCOTT',SDO_KEYWORDARRAY('1CARLTONBGOODLETTPL','SANFRANCISCO -------------------------------------------------------------------------------- PL SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME').Side RROM DUAL; S - L SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME').Percent FROM DUAL; SDO_GCDR.GEOCODE('SCOTT',SDO_KEYWORDARRAY('1CARLTONBGOODLETTPL','SANFRANCISCO -------------------------------------------------------------------------------- .01 SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME').EdgeID FROM DUAL; SDO_GCDR.GEOCODE('SCOTT',SDO_KEYWORDARRAY('1CARLTONBGOODLETTPL','SANFRANCISCO -------------------------------------------------------------------------------- 23614360 SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME').MatchCode FROM DUAL; SDO_GCDR.GEOCODE('SCOTT',SDO_KEYWORDARRAY('1CARLTONBGOODLETTPL','SANFRANCISCO -------------------------------------------------------------------------------- 1 SELECT SDO_GCDR.GEOCODE('SCOTT', SDO_KEYWORDARRAY('1 Carlton B Goodlett Pl', 'San Francisco, CA 94102'), 'US', 'RELAX_BASE_NAME').MatchVector FROM DUAL; SDO_GCDR.GEOCODE('SC -------------------- ????0101010??000?
Parent topic: Data Types for Geocoding
12.2.2 SDO_ADDR_ARRAY Type
The SDO_ADDR_ARRAY type is a VARRAY of SDO_GEO_ADDR objects (described in SDO_GEO_ADDR Type) used to store geocoded address results. Multiple address objects can be returned when multiple addresses are matched as a result of a geocoding operation.
The SDO_ADDR_ARRAY type is defined as follows:
CREATE TYPE sdo_addr_array AS VARRAY(1000) OF sdo_geo_addr;
Parent topic: Data Types for Geocoding
12.2.3 SDO_KEYWORDARRAY Type
The SDO_KEYWORDARRAY type is a VARRAY of VARCHAR2 strings used to store address lines for unformatted addresses. (Formatted and unformatted addresses are described in Address Representation.)
The SDO_KEYWORDARRAY type is defined as follows:
CREATE TYPE sdo_keywordarray AS VARRAY(10000) OF VARCHAR2(9000);
Parent topic: Data Types for Geocoding