Maps 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.
The package contains the following procedures and functions:
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.
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 (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 |
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.
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:
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
Next Post