Now available: Geocoding API in Oracle Autonomous Database Serverless
March 14, 2023 | 23 minute readMaps are the standard way of visualizing location information. To plot any location on a map, you must have a pair of geographic coordinates assigned. Depending on the referenced coordinate system, the pair of coordinates is called, for example, x/y, longitude/latitude, or easting/northing.
Oracle Autonomous Database Serverless (ADB-S) now includes a PL/SQL package that directly converts address information into such pairs of geographic coordinates (geocoded addresses) as an operation in the database. This operation is called geocoding.
Oracle provides several options to geocode address data or place names. Previously, you had to obtain a geocoding reference data set and store it in the database to perform in-database geocoding. Several content providers, such as HERE or TomTom, provide reference data that users can license separately, typically as DataPump files or Transportable Tablespaces. For Oracle Autonomous Database users having reference data is no longer required. You can now use a PL/SQL package called SDO_GCDR to perform the geocoding. This makes it easier and faster and lowers the cost for you to visualize your business data on maps and apply a location perspective.
ADB Geocoder Package
Overview
The package contains the following procedures and functions:
- Two procedures to grant access to a database user and to revoke access from a database user.
- Execute these functions as user ADMIN.
- Function ELOC_GEOCODE is an overloaded function that can be used for both (forward) geocoding and reverse geocoding. Depending on the available address data and the desired format of the return value, you call the function as follows:
- ELOC_GEOCODE( street VARCHAR2, city VARCHAR2, region VARCHAR2, postal_code VARCHAR2, cc2 VARCHAR2, match_mode IN VARCHAR2 DEFAULT 'RELAX_POSTAL_CODE')
- Return value: JSON
- The GeoJSON can contain zero, one, or more matches being the best matches according to the input parameters, including the MATCH_MODE (see explanations after this summary).
- Use this function when the address fields are known and correspond to the parameters of the function.
- ELOC_GEOCODE( address_line VARCHAR2)
- Return value: JSON
- Use this function when the address fields are unknown and only an unstructured address is available (as in a single line of address).
- ELOC_GEOCODE_AS_GEOM( street VARCHAR2, city VARCHAR2, region VARCHAR2, postal_code VARCHAR2, cc2 VARCHAR2, match_mode IN VARCHAR2 DEFAULT 'RELAX_POSTAL_CODE')
- Return value SDO_GEOMETRY object (point geometry defined by longitude and latitude)
- Same as the structured address input further up (see 1).
- ELOC_GEOCODE_AS_GEOM( address_line VARCHAR2)
- Same as the unstructured address input function further up (see 2).
- ELOC_GEOCODE( longitude NUMBER, latitude NUMBER)
- Reverse geocoder function that determines the actual address based on given longitude and latitude values.
- ELOC_GEOCODE( street VARCHAR2, city VARCHAR2, region VARCHAR2, postal_code VARCHAR2, cc2 VARCHAR2, match_mode IN VARCHAR2 DEFAULT 'RELAX_POSTAL_CODE')
You execute either of the functions on a single address. Batch geocoding for a list of addresses is not supported.
What is the MATCH MODE? The match mode for a geocoding operation determines how closely the attributes of an input address must match the reference data being used for the geocoding. The default value used by SDO_GCDR is 'RELAX_POSTAL_CODE'. See the Oracle Spatial Developer´s Guide 19c for the list of all available match modes.
Enable or disable in-database geocoding
Before using the forward or reverse geocoding functions, you must grant the required permissions to the database user(s). There are two functions in the SDO_GCDR package to support granting or revoking access.
Replace <db_user_name> in the function call below. Then execute it as ADMIN to grant access.
exec sdo_gcdr.eloc_grant_access('<db_user_name>');
Replace <db_user_name> in the function call below. Then execute it as ADMIN to revoke access:
exec sdo_gcdr.eloc_revoke_access('<db_user_name>');
Forward geocoding
Forward geocoding (or just geocoding) means starting with an address or named place and converting it into geographic coordinates. Let´s see how you call the functions, what they return, and how you can use other functions to extract specific values from the results, either as JSON or SDO_GEOMETRY. We use a set of sample addresses from several countries stored in a sample table.
-- Sample address data street,city,region,postal_code,country Bötzower Str. 24,Berlin,BE,10407,DE Bd Carl-Vogt 67,Genève,,1205,CH 100 N Renfrew St,Vancouver,BC,V5K 4W3,CA 2 Chome-5-8 Kitaaoyama,Tokyo,,107-0061,JP 123 Beacon St,Boston,MA,02116,US -- Structured input returning JSON or SDO_GEOMETRY select sdo_gcdr.eloc_geocode(street, city, region, postal_code, country, 'RELAX_POSTAL_CODE') from sample_addresses; -- Request 1 select sdo_gcdr.eloc_geocode_as_geom(street, city, region, postal_code, country, 'RELAX_POSTAL_CODE') from sample_addresses; -- Request 2 -- Unstructured input returning JSON or SDO_GEOMETRY select sdo_gcdr.eloc_geocode(address_line) from sample_addresses; -- Request 3 select sdo_gcdr.eloc_geocode_as _geom(address_line) from sample_addresses; -- Request 4
Note: ADDRESS_LINE is a concatenated string with street, city, region, postal_code, and country using a comma as the separator.
Here is the response for each of the requests.
-- Return values for request 1
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 13.43335,
"y": 52.53107,
"houseNumber": "24",
"street": "Bötzowstraße",
"settlement": "Prenzlauer Berg",
"municipality": "Berlin",
"region": "BERLIN",
"postalCode": "10407",
"country": "DE",
"language": "GER",
"name": "",
"edgeId": 53499872,
"percent": 0.82,
"side": "R",
"matchCode": 4,
"matchVector": "??010121010??200?"
}
]
}
]
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 6.13693,
"y": 46.19739,
"houseNumber": "67",
"street": "Boulevard Carl-Vogt",
"settlement": "Genève",
"municipality": "Genève",
"region": "GENÈVE",
"postalCode": "1205",
"country": "CH",
"language": "GER",
"name": "",
"edgeId": 56045106,
"percent": 0.4,
"side": "R",
"matchCode": 1,
"matchVector": "??010101010??400?"
}
]
}
]
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": -123.04406,
"y": 49.28577,
"houseNumber": "100",
"street": "N Renfrew St",
"settlement": "Vancouver",
"municipality": "Vancouver",
"region": "BC",
"postalCode": "V5K",
"country": "CA",
"language": "ENG",
"name": "",
"edgeId": 811555206,
"percent": 0.13,
"side": "R",
"matchCode": 1,
"matchVector": "??010001010??000?"
}
]
}
]
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 139.7333,
"y": 35.65,
"houseNumber": "",
"street": "",
"settlement": "",
"municipality": "null",
"region": "TOKYO TO",
"postalCode": "107-0061",
"country": "JP",
"language": "JPN",
"name": "",
"edgeId": -1,
"percent": 0.0,
"side": "",
"matchCode": 4,
"matchVector": "???10101110??400?"
}
]
}
]
[
{
"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?"
}
]
}
]
-- Return values for request 2
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(13.43335, 52.53107, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(6.13693, 46.19739, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-123.04406, 49.28577, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(139.7333, 35.65, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-71.07355166666666, 42.355174166666664, NULL), NULL, NULL)
-- Return values for request 3
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 13.45507,
"y": 52.53206,
"houseNumber": "",
"street": "STORKOWER STRAßE",
"settlement": "PRENZLAUER BERG",
"municipality": "BERLIN",
"region": "BERLIN",
"postalCode": "10407",
"country": "DE",
"language": "GER",
"name": "REIFENBESTELLUNG 24",
"edgeId": 53594421,
"percent": 0.29,
"side": "R",
"matchCode": 1,
"matchVector": "???01101010??000?"
}
]
}
]
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 6.1358,
"y": 46.19802,
"houseNumber": "46",
"street": "Boulevard Carl-Vogt",
"settlement": "Genève",
"municipality": "Genève",
"region": "GENÈVE",
"postalCode": "1205",
"country": "CH",
"language": "GER",
"name": "",
"edgeId": 723493856,
"percent": 0.0,
"side": "L",
"matchCode": 3,
"matchVector": "???14101010??000?"
}
]
}
]
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": -123.04406,
"y": 49.28577,
"houseNumber": "100",
"street": "N Renfrew St",
"settlement": "Vancouver",
"municipality": "Vancouver",
"region": "BC",
"postalCode": "V5K",
"country": "CA",
"language": "ENG",
"name": "",
"edgeId": 811555206,
"percent": 0.13,
"side": "R",
"matchCode": 1,
"matchVector": "??010001010??000?"
}
]
}
]
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 139.80885,
"y": 35.68408,
"houseNumber": "",
"street": "",
"settlement": "Tokyo",
"municipality": "Tokyo",
"region": "Japan",
"postalCode": "null",
"country": "JP",
"language": "DAN",
"name": "",
"edgeId": 720645277,
"percent": 0.0,
"side": "L",
"matchCode": 4,
"matchVector": "???31111110??201?"
}
]
}
]
[
{
"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?"
}
]
}
]
-- Return values for request 4
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(13.45507, 52.53206, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(6.1358, 46.19802, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-123.04406, 49.28577, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(139.80885, 35.68408, NULL), NULL, NULL)
MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(-71.07355166666666, 42.355174166666664, NULL), NULL, NULL)
Looking at the JSON document returned, you find more than the longitude and latitude returned. You get additional information, such as the distinction between municipality and settlement and the side of the street. This way, you can enrich your address data and use geocoding for address data cleansing. The values specified as MATCH CODE and MATCH VECTOR in the JSON document indicate which address attributes were used for the geocoding and provide the detailed insights into the accuracy of the geocoding result. If you want to learn more about how to interpret the geocoding results, please check out this blogpost.
You can further process the returned result, persist it by executing UPDATE on the address data table, or use it in your SQL and PL/SQL code. Examples of the latter are extracting the longitude and latitude values from the geocoding result.
-- Extract lon/lat from returned JSON document
with lonlat_from_json as (
select
sdo_gcdr.eloc_geocode(street, city, region, postal_code, country) as j
from
sample_addresses
)
select
json_value(j,'$[0].matches[0].matchCode') as matchCode,
json_value(j,'$[0].matches[0].x') as lon,
json_value(j,'$[0].matches[0].y') as lat
from
lonlat_from_json;
-- Extract lon/lat from SDO_GEOMETRY object
with lonlat_from_sdo_geometry as (
select
sdo_gcdr.eloc_geocode_as_geom(street, city, region, postal_code, country) geom
from
sample_addresses
)
select
g.geom.sdo_point.x as lon,
g.geom.sdo_point.y as lat
from
lonlat_from_sdo_geometry g;
Here are the results of these two queries:
| MATCHCODE | LON | LAT |
|---|---|---|
| 4 | 13.43335 | 52.53107 |
| 1 | 6.13693 | 46.19739 |
| 1 | -123.04406 | 49.28577 |
| 4 | 139.7333 | 35.65 |
| 1 | -71.07355166666666 | 42.355174166666664 |
| LON | LAT |
|---|---|
| 13.43335 | 52.53107 |
| 6.13693 | 46.19739 |
| -123.04406 | 49.28577 |
| 139.7333 | 35.65 |
| -71.07355166666666 | 42.355174166666664 |
Reverse geocoding
With reverse geocoding, you start with geographic coordinates and convert those into an address.
Calling:
select
sdo_gcdr.eloc_geocode(13.4129, 52.52115)
from
dual;
returns:
[
{
"id": "0",
"matchCount": "1",
"matches": [
{
"sequence": "0",
"x": 13.412898244410691,
"y": 52.521153112181054,
"houseNumber": "",
"street": "Alexanderplatz",
"settlement": "Mitte",
"municipality": "Berlin",
"region": "BERLIN",
"postalCode": "10178",
"country": "DE",
"language": "GER",
"name": "",
"edgeId": 860397302,
"percent": 0.559600997510756,
"side": "R",
"matchCode": 1,
"matchVector": "???11141414??404?"
}
]
}
]
You can extract the different parts of the address again using again JSON_VALUE as described above.
Summary
Oracle Autonomous Database provides the valuable capability to geocode address data without requiring you to get reference data in the Oracle Database or as a location-based service. This allows you to develop applications that visualize your business data on maps and perform location-based analysis. The PL/SQL package SDO_GCDR uses an Oracle-managed service with geocoding reference data provided by HERE. Using both the package and the reference data does not require additional licensing. But make sure to read the following end-user terms:
Documentation
Related content
Karin Patenge
Senior Principal Product Manager, Oracle Spatial and Graph
Karin Patenge is a Product Manager for Oracle's Spatial and Graph Technologies. She works closely with customers, partners, and tech communities in mostly Europe, and the Middle East. She engages with developers, solution architects, data engineers & analysts in order to bring added value to their projects by integrating spatial and graph capabilities into solution architectures. Karin has a master's degree in Computer Science and has followed the UNIGIS postgraduate study program. She is based in Berlin, Germany, and collaborates closely with Oracle´s Spatial and Graph development teams.
Previous Post
Long-Term Backups (up to 10 years) on Autonomous Dedicated!
Next Post