Calculating Mileage in an APEX 5.0 Application

As part of our recent redevelopment of our Oracle Forms expenses application into APEX 5.0, I looked into creating a mileage calculator using the Google Maps API. This blog explains what I found and how I implemented the new feature into the application.

First of all I looked into the available APIs that might be suitable for creating this feature. The Google Directions API and the Google Distance Matrix API looked like good places to start. The Directions API takes a starting address and destination address and returns detailed directions as JSON data. The following API call will provide you with an example.

Explorer UK LS166EB to Oracle RG61RA

As you can see from the response, the directions are accompanied by a distance for the journey which can be used to calculate mileage.

The Google Distance Matrix API takes a list of start addresses and a list of end addresses and provides the distances between them. The following API call will provide you with an example.

Leeds or London to Edinbrough or Cornwall

The JSON results received from this API consist of just the distances between all origin and destination addresses. This API would be very useful for calculating simple distances over a number of legs however, I wanted to try and use the direction results available from the directions API to make my mileage calculator smarter.

Using the API and a map region (and the example map here), I was able to display the directions on a Google Map based on the JSON response received. The APEX_JSON package really comes in useful here for parsing the response! I also installed a Chrome extension called JSONView which formats JSON responses nicely and displays the path to a node when you hover over it.

This visual representation of the direction response allows users to see whether the route used to calculate their mileage is the actual route they travelled. If the calculated route is not the route travelled then the user needs to be able to alter this, so draggable was set to ‘true’ in the renderer options. Using the following piece of code I was also able to recalculate the direction results and draw them on the map whenever the directions are altered:

So there we have it, a simple distance calculator with an interactive map allowing users to alter the route taken. I added some quick links to allow users to select a popular address to automatically fill the origin or destination field, with ‘Home’ being the user’s home address looked up from the database.

Next I am planning on linking this piece of work with the address search I discussed in an earlier blog to allow users to look up the Origin and Destination from the Places and Geocode APIs.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.