Format

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

or

SDO_GCDR.ELOC_GEOCODE_AS_GEOM(
     address IN VARCHAR2
) RETURN SDO_GEOMETRY;

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 as an SDO_GEOMETRY object.

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

Usage Notes

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.

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

Examples

The following example geocodes a formatted address using the default match mode and returns the output as an SDO_GEOMETRY object.

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

MDSYS.SDO_GEOMETRY(2001, 4326,
        MDSYS.SDO_POINT_TYPE(-71.07355166666666, 42.355174166666664, NULL), NULL, NULL)

Note that if you are using the SQL Worksheet in Database Actions on your Autonomous Database instance, any object type (including SDO_GEOMETRY) is displayed as [object Object]. To display the result in the SQL Worksheet you can convert the result to a GeoJSON string by applying the Get_GeoJSON( ) method as follows:

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

{ "type": "Point", "coordinates": [-71.07355166666666, 42.355174166666664] }