24.4 SDO_GCDR.ELOC_GEOCODE_AS_GEOM
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');
or
SDO_GCDR.ELOC_GEOCODE_AS_GEOM(address IN 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 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:
TheSDO_GCDR.ELOC_GEOCODE_AS_GEOM
function is only supported in Oracle Autonomous Database
Serverless deployments.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
, andcc2
).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] }
Parent topic: SDO_GCDR Package (Geocoding)