Geocoding with PL/JSON

The Google Maps API geocoding web service makes it easy to geocode addresses. You can pass an address to the web service and receive either an XML or a JSON response back.

In this post I’ll show how to use the PL/JSON package to parse the JSON response from Google, in order to get to the various address elements and the coordinates associated with a given address.

PL/JSON is a library consisting of a PL/SQL package and some PL/SQL objects and types. It is designed to create and parse JSON. The documentation is a bit lacking, especially when it comes to handling a more complicated JSON object like the Google geocode response. Hopefully, this article will help to clarify its usage.

To get started, first download and install PL/JSON into a schema in your Oracle database. I used version 1.0.3. The installation is simply done by extracting the downloaded zip file, and running the install.sql script. Once installed, make sure all objects are valid. You can run a quick test of the package by running the json_test.sql included in the testsuite directory of the PL/JSON installation.

The Google Geocoding Web Service

Version 3 of the Google Maps API, no longer requires a key. So you can make a simple call to the service like this:

http://maps.googleapis.com/maps/api/geocode/json?address=5605+n+macarthur+blvd+irving+tx&sensor=false

The result of the URL is a JSON object containing the address and geocode data.

To make sense of the JSON object you need to understand that curly brackets {} contain objects, and square brackets [] contain arrays. Strings are enclosed with double quotes, numbers are not. Data is represented in name/value pairs separated by colons.

The Google response consists of two objects: results and status. A successful response will have a status of “OK”.

The results object is an array of four other objects:

  1. address_components – an array of elements such as street number, city, state, etc.
  2. formatted_address – a single string containing the full address
  3. geometry – an array of coordinate data elements
  4. types – an array of strings, which in this case is always “street_address”.

Calling the Web Service

Calling the Google web service from within PL/SQL, I use the ult_http package.  Since the JSON response can be quite large, I use the utl_http.request_pieces function to fetch the result. This works around the 2000 byte limit of the utl_http.request function .  I simply loop through all the pieces returned, and concatenate them to a CLOB variable inside the procedure.

Note that I make the http request inside a WHILE loop (line 50). This is helpful when you attempt to geocode a number of addresses in rapid succession (e.g. by looping through a table with addresses that need to be geocoded). Google will return an error (OVER_QUERY_LIMIT) when it receives too many requests per second. So the loop waits two seconds before the next attempt. I try a total of three attempts. If I still don’t get an OK status back, I give up. Note that Google limits the free geocoding to 2,500 requests per IP per day. If that limit is reached, you also receive an OVER_QUERY_LIMIT error.

In order to get to the status of the response, I use the json() function to convert the JSON string to a JSON object (lines 64 and 67).

Parsing the JSON response

Now that we have our geocoded address stored in a JSON object, I can now use PL/JSON to extract the various elements we need.

Since the results object consist of an array, I pass this array to a JSON_LIST object (l_results) (line 96). This array, however, consists only of a single element, so I pass this to a variable of type JSON, named l_tempobj (line 99).

The variable l_tempobj now contains the JSON of the four elements listed above (address_components, formatted_address, geometry, and status).

Since the address_components is yet another array, I pass it to another JSON_LIST object named l_addr_comps (line 102). Now I can loop through the address components and test the types array element to find out what type of address component I’m dealing with. I use the CASE statement (line 117) to test the types and to assign the address elements to their individual PL/SQL variables.

Once the loop is done, I have all my address components I looked for, but I still want to get to the coordinates. Those are stored in the third array element (geometry) of l_tempobj.  I pass the location element to the l_loc variable (line 140) of type JSON., then extract the lat/lng strings from it, and pass them to PL/SQL varchar2 variables (lines 146 and 147).

At this point the procedure is done, and I print the results.

Notes

The JSON and JSON_LIST data types are created on installation of PL/JSON. When parsing through a JSON object, you need to be keenly aware of what you are processing: Whether it is a nested JSON object or a string or number. Notice that I had to use the get_number function to extract lat/lng, while using get_string to get the status.

Use json_list() to pass the value of an element that consists of an array:

Use json_ext.get_string (or get_number) to get the value of a non-array element:

Other errors from the Google API:

ZERO_RESULTS – no address data was fund

REQUEST_DENIED – most likely the sensor parameter was not specified correctly. Check the error_message text included in the response.

I’ve also included a few commented out <obj>.print statements for you to see the contents of various objects.

Let me know how you fare with this. Comments are always appreciated.

[sourcecode language=”sql”]
CREATE OR REPLACE PROCEDURE geocode_address(p_address IN VARCHAR2) IS
l_obj json;
l_results json_list;
l_tempobj json;
l_addr_comps json_list;
l_addr json;
l_typesarr json_list;
l_geom_obj json;
l_loc json;

l_status VARCHAR2(255);
l_types VARCHAR2(30);
l_short_name VARCHAR2(200);
l_street_number VARCHAR2(200);
l_street VARCHAR2(200);
l_city VARCHAR2(200);
l_state VARCHAR2(30);
l_zip VARCHAR2(10);
l_county VARCHAR2(30);
l_country VARCHAR2(10);
l_tries PLS_INTEGER := 0;
l_address VARCHAR2(4000);
l_request VARCHAR2(4000);
l_pieces utl_http.html_pieces;
l_json CLOB;
l_lat VARCHAR2(40);
l_lng VARCHAR2(40);

e_query_limit EXCEPTION;
e_zero_results EXCEPTION;

l_prc CONSTANT VARCHAR2(16) := ‘GEOCODE ADDRESS’;

BEGIN
dbms_application_info.set_module(module_name => l_prc, action_name => ‘START’);

— replace empty spaces with +
l_address := REPLACE(TRIM(p_address), ‘ ‘, ‘+’);

BEGIN

l_request := ‘http://maps.googleapis.com/maps/api/geocode/json?address=’ ||
l_address || chr(38) || ‘sensor=false’;

l_tries := 0;
l_status := NULL;
l_json := NULL;

— Try three gets in case of OVER_QUERY_LIMIT errors
WHILE nvl(l_status, ‘*’) != ‘OK’ AND l_tries < 3
LOOP
dbms_application_info.set_client_info(client_info => ‘HTTP REQUEST try ‘ ||
l_tries);

— JSON object may be long, so split it into pieces
l_pieces := utl_http.request_pieces(l_request);

FOR i IN 1 .. l_pieces.count
LOOP
l_json := l_json || l_pieces(i);
END LOOP;

— Create json object from google response
l_obj := json(l_json);

— Check the response status. If it is OK then proceede with parsing.
l_status := json_ext.get_string(l_obj, ‘status’);

IF l_status != ‘OK’
THEN
dbms_output.put_line(l_prc || ‘ status [‘ || l_status || ‘]’);
IF l_status = ‘OVER_QUERY_LIMIT’ AND l_tries >= 3
THEN
dbms_output.put_line(l_prc || ‘: query limit reached. Exiting’);
RAISE e_query_limit;
ELSIF l_status = ‘ZERO_RESULTS’
THEN
RAISE e_zero_results;
ELSE
l_tries := l_tries + 1;
dbms_lock.sleep(2);
END IF;

END IF;

–dbms_output.put_line(l_json);
l_json := NULL;

END LOOP;

BEGIN

dbms_application_info.set_client_info(client_info => ‘PARSE JSON’);

— The overall JSON is an array named results
l_results := json_list(l_obj.get(‘results’));

— There is only a single element in the results array, so get the first (and last) one
l_tempobj := json(l_results.get(1));

— The next level contains an array named address_components
l_addr_comps := json_list(l_tempobj.get(1));

— loop through the address components and test the types array for address elements
FOR i IN 1 .. l_addr_comps.count
LOOP
l_addr := json(l_addr_comps.get(i));
–l_addr.print;

l_typesarr := json_list(l_addr.get(‘types’));

— Types is not a json array, but a string array so we have to get
— the first element using the types[1] syntax
l_types := json_ext.get_string(l_addr, ‘types[1]’);
l_short_name := json_ext.get_string(l_addr, ‘short_name’);

CASE l_types
WHEN ‘street_number’ THEN
l_street_number := l_short_name;
WHEN ‘route’ THEN
l_street := l_short_name;
WHEN ‘locality’ THEN
l_city := l_short_name;
WHEN ‘administrative_area_level_1’ THEN
l_state := l_short_name;
WHEN ‘administrative_area_level_2’ THEN
l_county := l_short_name;
WHEN ‘postal_code’ THEN
l_zip := l_short_name;
WHEN ‘country’ THEN
l_country := l_short_name;
ELSE
NULL;
END CASE;

END LOOP;

— now get lat/lng
l_geom_obj := json(l_tempobj.get(3));
–l_geom_obj.print;

l_loc := json_ext.get_json(l_geom_obj, ‘location’);
–l_loc.print;

l_lat := to_char(json_ext.get_number(l_loc, ‘lat’));
l_lng := to_char(json_ext.get_number(l_loc, ‘lng’));

dbms_output.put_line(‘Address: ‘ || l_street_number || ‘ ‘ ||
l_street || ‘ ‘ || l_city || ‘ ‘ || l_state || ‘ ‘ ||
l_zip || ‘ ‘ || l_county || ‘ ‘ || l_country);
dbms_output.put_line(‘lat/lng: ‘ || l_lat || ‘ ‘ || l_lng);

EXCEPTION
WHEN e_query_limit THEN
raise_application_error(-20001, ‘GOOGLE QUERY LIMIT REACHED’);
WHEN OTHERS THEN
dbms_output.put_line(l_prc || ‘: ‘ || SQLERRM);
END;
EXCEPTION
WHEN e_zero_results THEN
dbms_output.put_line(l_prc || ‘: Zero Results. Getting next address’);
END;

COMMIT;

dbms_application_info.set_client_info(client_info => ‘DONE’);
EXCEPTION
WHEN OTHERS THEN
RAISE;

END geocode_address;
[/sourcecode]

7 thoughts on “Geocoding with PL/JSON

  • September 18, 2013 at 1:54 pm
    Permalink

    Hi, Dude:

    Very helpful, thank you! I wonder if you could provide some examples to get digital signature for Google map service v3 with PL/SQL. As you know, Google map service for business account requires generating digital signature with HMAC-SHA1 algorithm. I could not find any example about it. Here are some links:

    https://developers.google.com/maps/documentation/business/webservices/auth
    https://groups.google.com/forum/#!topic/google-checkout-api-other/iGd7SvCR5S4

    Thanks.

    Sam

    Reply
    • September 18, 2013 at 3:27 pm
      Permalink

      Sam,
      thanks for the comment. If I understand correctly, you are asking how to provide the digital signature in a pl/sql http request. Unfortunately I haven’t done this yet and I’m not well versed in cryptology. If this should ever come up, I’ll make sure to add it to the post.

      Christoph

      Reply
  • September 19, 2013 at 1:25 am
    Permalink

    Hi, Christoph.

    Thank you for posting such an interesting article. I, too, have used the PL/JSON set of packages for a project at my work place (Stanford Linear Accelerator Center in CA). Like you, I used Oracle’s UTL_HTTP package to retrieve a JSON response from a web service maintained by another lab and then had to learn by trial-and-error how to parse through it using PL/JSON. I then stored the various data grams into Oracle tables an dthen used Apex to make several reports and charts from this data. Cool stuff.

    Question: I know Apex includes several built-in procedures for managing JSON objects. I’ve not used them yet, relying instead on the PL/JSON utilities. Have you any idea if the Apex procedures are as extensive as PL/JSON? Have you used these?

    Thank you, again, for a fine article. It was well written and informative. Please keep them coming.

    And, as I think about, I, too, have an idea for an Apex/Oracle article that may be of interest to others. Hmm. Perhaps I, too, can try my hand at blogging. Just a thought.

    Elie

    Reply
    • September 19, 2013 at 12:41 pm
      Permalink

      Elie,
      thank you for your response. I did look briefly at the apex_util api, but found it very limited. It seems to be OK for generating JSON, but not for parsing it.

      Let me know when you’ve written your article.

      Regards,
      Christoph

      Reply
  • September 24, 2013 at 3:13 pm
    Permalink

    Could this work as a function with the output as formatted varchar2 string?

    Reply
    • September 24, 2013 at 3:17 pm
      Permalink

      Absolutely, just change it into a function and return a string with the concatenated address items.

      Reply
  • February 26, 2014 at 10:42 am
    Permalink

    Hi to all,
    My compliments, this is a good work, a very initeresting post, and indeed somewhat “criptic”. But I miss the SRID (Coordinate Reference System), is it WGS84 or another one??
    Thanks by advance if someone can find how to get this information.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *