Getting the best address and location searches in Oracle APEX

Comparing the APEX built-in Geocoded Address item with third party APIs for getting the best search results.

Geocoding and Reverse Geocoding – what is that?

Geocoding is the process of taking an address or location and converting it to latitude and longitude geographic coordinates.

Reverse Geocoding is the opposite – the process that takes a latitude and longitude and converts it to an address or place such as (City, State, County, Zip etc.).

Read this short post if you want the long description or this Wikipedia article to learn more about the history of the Geocoding.

APEX and Geocoding

Geocoded Address page item

Since Oracle APEX version 21.2 we can enjoy the native Geocoded Address item. This new page item provides a list of possible matches to choose from when a user starts typing an address, and returns the coordinates as well as the normalized address.

It can either display a map with the selected point on it (saved as GeoJSON) or simply keep that value, so it can be used elsewhere.

It can either allow Structured Address or not. Structured address option, requires user to select a Country, Address and/or Postal Code to do the search. It is the more accurate of the two versions. The downside is that you need to select 3-4 input fields before you get a result. When Structured Address is turned off, you skip the Country, City and Postal Code and enter a free form text for your search. The downside of this is that the search might not be optimal as the one when the option is turned on.

An example of Address Geocoding, having the Structured Address option turned on

Geocoding Service used

Under the hood, the Geocoded Address item uses the Oracle Elocation Map Cloud service. The coordinates of the search are stored as we already mentioned in GeoJSON format, similar to the one below:

{"type": "Point", "coordinates": [-74.0060, 40.7128]}

Pros and Cons of the APEX built-in Geocoding Service

Pros:

  • ❇️ Well, it built-in. No need to configure anything. It’s there, waiting for you.
  • ❇️ It’s free. No mater how many searches you do, no additional fees are applied.
  • ❇️ It’s an Oracle service, used in other Oracle products as well (the data they supply is actually taken from HERE Technologies)
  • ❇️ The Geocoded Address item is fully declarative, making it very easy to get started with. And it is very well integrated with other Maps and Spatial features in Oracle APEX.

Useful links:

Cons:

  • 🟡 It doesn’t support the full range of addresses and street numbers as you would find them in Google Maps search and other geocoding services. Take this for example – A search for "50 Shelton Street, Covent Garden" would return two results only and they are not the one I have explicitly specified – "Covent Garden" and "Covent Garden 7".
  • 🟡 The built-in Geocoded Address doesn’t seem to be particularly good at finding landmarks. And that’s especially needed in real world searches, as people often look for tube stations, stadiums, parks, buildings. I had difficulties finding "Big Ben" or "Wembley stadium" for example. The search results I get from the Geocoded Address item are nowhere near the actual places I was looking for.

An example of Address Geocoding, failing to find landmarks, such as parks and stadiums

Depending on your use case, the built-in Geocoded Address might be fully enough. In some situations however, there is a need for an external API to be invoked and do a better job at geocoding. In the next chapter, I will present you several options, as they vary in price, ease of use and popularity. You can decide for yourself, which one fits best to your use case and budget. It’s worth mentioning that most of the APIs provided allow a number of free searches, which would be more than enough for a small APEX application with not so big user base.

Alternatives to the built-in APEX Geocoding

Geoapify

Mapbox

Google Geolocation API

Here Technologies

Amazon Location Service

OpenWeatherMap

Replacing the APEX built-in Address Geocoding with a 3rd party API

What I am going to do to in order to use my external Geocoding API is create the following setup:

Setup

  • Register for an account at any of the mentioned services. For this demo, I have created a free account at Geoapify. Check their page for more details and code samples.
  • Input items for Country and Free Text Search Address
  • Map region with Points Layer Type
  • A Dynamic Action, triggered on Search button click with three True actions
    • Call the Geocoding REST API with the text from the Input items
    • Refresh the Map Region
    • Pin the Geocoded result from the API call to the Map and re-center it

Page Items and Regions needed

Items

Since my page is number 29, I prefix all items with P29_

  • P29_COUNTRY – Select List (* You can find all the countries in the world* HERE)
  • P29_ADDRESS – Text Field (* Will be used as our Address search parameter)
  • P29_ADDRESS_GEOCODED – Hidden (* Will keep the GeoJSON, used to render the Map, value will be taken from the API call result JSON)
  • P29_API_JSON – Hidden (* The full API call result JSON, useful for debugging purposes)
  • P29_API_FORMATTED_ADDRESS – Hidden (* The human readable formatted address, found in the API call result JSON)

Region

  • Map
    • Layer – Layer Type Points
select :P29_ADDRESS_GEOCODED        geolocation,
           :P29_API_FORMATTED_ADDRESS  formatted_address
from dual
  • Page Items to Submit – P29_ADDRESS_GEOCODED
  • Geometry Column Data Type – GeoJSON
  • GeoJSON Column – GEOLOCATION
  • Tooltip / Column – FORMATTED_ADDRESS

Dynamic Action

  • onButtonClick – triggerGeocoding
    1. Call Geocoding REST API
    2. Refresh the Map Region
    3. Pin the Geocoded Address on the Map and zoom

Dynamic Action, triggered on Search button click

Calling the Geolocation REST API

declare
    l_json      clob;
    l_geojson   clob;
    l_formatted varchar2(4000);
begin 
    -- call GeoApify service
    l_json := apex_web_service.make_rest_request(
        p_url => 'https://api.geoapify.com/v1/geocode/autocomplete?text='||:P29_ADDRESS||','||:P29_COUNTRY||'&limit=1&format=json&apiKey=6dc7fb95a3b246cfa0f3bcef5ce9ed9a',
        p_http_method => 'GET'
    );

    with geoapify_json as (
        select l_json json_res 
        from dual )

    select --jt.*, 
           '{"type": "Point", "coordinates": ['||jt.longitude||', '||jt.latitude||']}' coordinates,
           jt.formatted
    into l_geojson, l_formatted 
    from geoapify_json,
    json_table(json_res, '$.results[*]'
       columns (longitude varchar2(100) path '$.lon',
                latitude  varchar2(100) path '$.lat',
                formatted varchar2(4000) path '$.formatted')
    ) as jt;

    :P29_ADDRESS_GEOCODED      := l_geojson;
    :P29_API_JSON              := l_json; 
    :P29_API_FORMATTED_ADDRESS := l_formatted;

exception
    when others then 
        :P29_ADDRESS_GEOCODED := '{"type": "Point", "coordinates": [-74.0060, 40.7128]}';
end;

Refresh the Map Region

Zoom and center the map

var lMapRegion   = apex.region("map_search"),
// important: Use the layer name exactly as specified in the "name" attribute in Page Designer
lLayerId     = lMapRegion.call("getLayerIdByName", "Map Search"),
lCurrentZoom = lMapRegion.call("getMapCenterAndZoomLevel").zoom,
lLocation    = apex.item("P29_ADDRESS_GEOCODED").getValue(),
lFeature     = lMapRegion.call("getFeature", lLayerId, lLocation ),
lPosition;

console.log("lLocation -> " + lLocation);    

lPosition = jQuery.parseJSON( lLocation );

console.log("lPosition -> " + lPosition);    
   
// close all Info Windows, which might currently be open
lMapRegion.call( "closeAllInfoWindows" );

// focus the map to the chosen feature
//lMapRegion.call( "setCenter", lPosition );
apex.region( "map_search" ).setCenter( lPosition.coordinates );

// if the current zoom level is below 8, zoom in. Otherwise do nothing.
if ( lCurrentZoom < 10 ) {
   lMapRegion.call( "setZoomLevel", 10 );
}
//setTimeout( function() {lMapRegion.call( "displayPopup", "infoWindow", lLayerId, lLocation.toString(), false )}, 500 );

Demo

See a demo of the above integration in my Demo Application.

Follow me

Liked that post? Follow me on Twitter and LinkedIn!

🔷 @plamen_9 on Twitter

🔷 Plamen Mushkov on LinkedIn

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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