24.2 SDO_GCDR.ELOC_GEOCODE
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');
or
SDO_GCDR.ELOC_GEOCODE(address IN VARCHAR2);
or
SDO_GCDR.ELOC_GEOCODE(
longitude IN NUMBER,
latitude IN NUMBER);
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:
TheSDO_GCDR.ELOC_GEOCODE
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.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
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?"}]}]
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?"}]}]
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?"}]}]
Parent topic: SDO_GCDR Package (Geocoding)