SDO_GCDR.ELOC

Format

SDO_GCDR.ELOC_GEOCODE(
     street      IN VARCHAR2, 
     city        IN VARCHAR2, 
     region      IN VARCHAR2,
     postal_code IN VARCHAR2,
     cc2         IN VARCHAR2, 
     match_mode  IN VARCHAR2 default 'DEFAULT'
) RETURN VARCHAR2;

or

SDO_GCDR.ELOC_GEOCODE(
     address IN VARCHAR2
) RETURN VARCHAR2;

or

SDO_GCDR.ELOC_GEOCODE( 
     longitude IN NUMBER,
     latitude  IN NUMBER
) RETURN VARCHAR2;

Description

Geocodes a formatted (address parts in separate fields) or an unformatted (complete address in a single string field) address and returns the standardized address with geographic coordinates and geocoding metadata in JSON format.

For longitude and latitude input, the function reverse geocodes the location and returns the address in JSON format.

Parameters

street: Name of the street.
city: Name of the city.
region: Name of the region.
postal_code: Postal code.
cc2: ISO 2-character country code. See Country codes in ISO Online Browsing Platform (OBP) to view the list of supported codes.
match_mode: Match mode for the geocoding operation. Match modes are explained in Match Modes.
address: Complete address (not formatted into separate fields).
longitude: Longitude value for reverse geocoding operation.
latitude: Latitude value for reverse geocoding operation.

Usage Notes

Note:

The SDO_GCDR.ELOC_GEOCODE function is only supported on Oracle Autonomous Database.

In order to use this function on your Autonomous Database instance, ensure that you have been granted the required permission. See SDO_GCDR.ELOC_GRANT_ACCESS for more information.

This function performs the following operations depending on the input parameters. Note that each parameter input can be a column from a table or view, or an explicit string or number value.

Geocoding a formatted address: Provide the address fields corresponding to the parameters of the function (such as street, city, region, postal_code, and cc2).
Note that the function uses 'DEFAULT' as the default match mode for the geocoding operation. See Match Modes for more details.

Refer to Example-1.
Geocoding an unformatted address: If the complete address is stored in a single field (that is, unformatted), then provide the address field corresponding to the complete address.
Refer to Example-2.
Reverse geocoding a location: You must provide the longitude and latitude coordinates and the function returns the address in JSON format.
Refer to Example-3.

Examples

Example-1

The following example geocodes a formatted address using the default match mode. It returns the longitude and latitude coordinates of this address as -71.07355166666666 and 42.355174166666664, respectively.

SELECT SDO_GCDR.ELOC_GEOCODE('123 Beacon St', 'Boston', 'MA', '02116' , 'US') FROM DUAL;

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":-71.07355166666666,"y":42.355174166666664,
  "houseNumber":"123","street":"Beacon St","settlement":"Boston","municipality":"Suffolk","region":"MA",
  "postalCode":"02116","country":"US","language":"ENG","name":"","edgeId":946710796,
  "percent":0.08333333333333333,"side":"R","matchCode":1,"matchVector":"???10101010??000?"}]}]

Example-2

The following example geocodes an unformatted address. It returns the longitude and latitude coordinates of this address as -71.07355166666666 and 42.355174166666664, respectively.

SELECT SDO_GCDR.ELOC_GEOCODE('123 Beacon St, Boston MA, 02116, US') FROM DUAL;

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":-71.07355166666666,"y":42.355174166666664,
  "houseNumber":"123","street":"Beacon St","settlement":"Boston","municipality":"Suffolk","region":"MA",
  "postalCode":"02116","country":"US","language":"ENG","name":"","edgeId":946710796,
  "percent":0.08333333333333333,"side":"R","matchCode":1,"matchVector":"???10101010??000?"}]}]

Example-3

The following example reverse geocodes a geographic location. It returns the address for the longitude (-71.073551) and latitude (42.355174) coordinates.

SELECT SDO_GCDR.ELOC_GEOCODE(-71.073551, 42.355174) FROM DUAL;

[{"id":"0","matchCount":"1","matches":[{"sequence":"0","x":-71.07355109772594,"y":42.35517433341787,
  "houseNumber":"123","street":"Beacon St","settlement":"Boston","municipality":"Suffolk","region":"MA",
  "postalCode":"02116","country":"US","language":"ENG","name":"","edgeId":946710796,
  "percent":0.08431426223078922,"side":"R","matchCode":1,"matchVector":"???14141414??404?"}]}]