Geocoding with PL/JSON

September 17, 2013 at 3:23 pm | Posted in Oracle Developement | 7 Comments
Tags: , , , , , , ,

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:

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.


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:

"address_components" :  [{ "name 1" : value} ,{"name 2" : "value"},{"name 3" : "value"}]

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

"status" : "OK"

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.

  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;


  dbms_application_info.set_module(module_name => l_prc, action_name => 'START');

  -- replace empty spaces with +
  l_address := REPLACE(TRIM(p_address), ' ', '+');


    l_request := '' ||
                 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
      dbms_application_info.set_client_info(client_info => 'HTTP REQUEST try ' ||

      -- 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
        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'
        dbms_output.put_line(l_prc || '  status [' || l_status || ']');
        IF l_status = 'OVER_QUERY_LIMIT' AND l_tries >= 3
          dbms_output.put_line(l_prc || ': query limit reached. Exiting');
          RAISE e_query_limit;
        ELSIF l_status = 'ZERO_RESULTS'
          RAISE e_zero_results;
          l_tries := l_tries + 1;
        END IF;

      END IF;

      l_json := NULL;



      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
        l_addr := json(l_addr_comps.get(i));

        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;
        END CASE;

      END LOOP;

      -- now get lat/lng
      l_geom_obj := json(l_tempobj.get(3));

      l_loc := json_ext.get_json(l_geom_obj, 'location');

      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);

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


  dbms_application_info.set_client_info(client_info => 'DONE');

END geocode_address;


RSS feed for comments on this post. TrackBack URI

  1. 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:!topic/google-checkout-api-other/iGd7SvCR5S4



    • 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.


  2. 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,
      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.


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

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

  4. 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Entries and comments feeds.

%d bloggers like this: