SDO_GCDR.ELOC_GEOCODE_AS

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

Note:

The SDO_GCDR.ELOC_GEOCODE_AS_GEOM function is supported only on Oracle Autonomous AI Database.
To use this function on an Autonomous AI Database instance, ensure that you have been granted the required privileges. 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 AI 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] }

24.5 SDO_GCDR.ELOC_GEOCODE_AS_GEOM