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
andconverting
itto latitude and longitude
geographic coordinates.Reverse Geocoding is the opposite - the process that
takes a latitude and longitude
andconverts
itto
anaddress 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.
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:
- More info about the
Geocoded Address
in the APEX App Builder User's Guide - More details about the Oracle Elocation Map Cloud service - Here and Here
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 theGeocoded Address item
are nowhere near the actual places I was looking for.
Depending on your use case, the built-in
Geocoded Address
might be fully enough. In some situations however, there is a need for anexternal 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
- Docs: apidocs.geoapify.com/playground/geocoding
- Pricing: geoapify.com/pricing
- Free tier:
Yes
- Free credits:
3,000 per day
Mapbox
- Docs: docs.mapbox.com/playground/geocoding
- Pricing: mapbox.com/pricing#search
- Free tier:
Yes
- Free credits:
100,000 per month
Google Geolocation API
- Docs: developers.google.com/maps/documentation/ge..
- Pricing: developers.google.com/maps/documentation/ge..
- Free tier:
No
- Free credits:
No
Here Technologies
- Docs: developer.here.com/documentation/examples/r..
- Pricing: here.com/pricing
- Free tier:
Yes
- Free credits:
30,000 per month
Amazon Location Service
- Docs: aws.amazon.com/location/pricing
- Pricing: aws.amazon.com/location/pricing
- Free tier:
Yes
- Free credits:
10,000 per month
(for the first 3 months)
OpenWeatherMap
- Docs: openweathermap.org/api/geocoding-api
- Pricing: openweathermap.org/price
- Free tier:
Yes
- Free credits:
60 per minute
,1,000,000 per month
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
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 TypePoints
select :P29_ADDRESS_GEOCODED geolocation, :P29_API_FORMATTED_ADDRESS formatted_address from dual
Page Items to Submit
- P29_ADDRESS_GEOCODEDGeometry Column Data Type
- GeoJSONGeoJSON Column
- GEOLOCATIONTooltip / Column
- FORMATTED_ADDRESS
Dynamic Action
- onButtonClick - triggerGeocoding
- Call Geocoding REST API
- Refresh the Map Region
- Pin the Geocoded Address on the Map and zoom
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;
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