How to find nearest stadiums using Oracle APEX maps and geolocation

See how I combined the APEX Geocoded Address item, several map layers, GeoJSON, SDO_UTIL and SDO_GEOMETRY packages to find nearest stadiums around

How to find nearest stadiums using Oracle APEX maps and geolocation

Table of contents

No heading

No headings in the article.

The use case

In this article, I will show you a solution for a situation that I often have while travelling abroad - finding the nearest stadium around me to watch some sports game. What I used is some native APEX functionality, available since version 21.2 - the new Maps region and the Geocoded Address item. The Maps region allows us to have several different layers inside, as the ones I have in my app are:

  • Map Search - containing the location I have searched for (Layer type Points)
  • Distance Circe - the range in which I want to see all stadiums (Layer type Polygons)
  • Nearby Stadiums - All individual map points which represent a stadium within the desired range (Layer type Points)

The demo

See a demo of the app on my free Oracle APEX workspace here:
US Nearby Stadiums Locator

The stadiums information I have is for all stadiums in the US, used by NBA, MLS, MLB and NHL elite teams. So to get some good results please do search on addresses in the USA. Hovering over a map item will give you the name of the stadium, the league they are playing in and the distance to your location.

What components and features are there in use in this application?

  • My location, or the location I'm interested in, found and converted to GeoJSON using the Geocoded Address item
  • Several Text Field and Select List items, needed for the Geocoded Address to work
  • A Map region with 3 different Layers (listed above)
  • SDO_GEOMETRY Data Type
  • JSON_VALUE function to extract the latitude and Longitude out of the Geocoded Address
  • sdo_util.circle_polygon function to generate SDO_GEOMETRY object used to render a circle on the map
  • sdo_geom.sdo_distance function to determine the distance between two points (in our case the Latitude/Longitude of the searched place and the latitude/Longitude of each stadium in our database)
  • mapRegion APEX JavaScript API to centre and zoom our map

Step by Step Guide

Items

  • Create a similar structure to the one I have. I won't go into much details here, as the items except P27_GEOLOCATOR and the Maps Region are standard and there is nothing special as settings there.

Regions and Items

Geocoded Address

  • Create the P27_GEOLOCATOR item. It should be of type Geocoded Address. What is important here is to specify which items should be used as Country, City Item and Street Item. This item by default comes with a map, which renders the location you have selected.

Default Geocoded Address display map

However it's not as rich as the Map region, so I will just use it to get the GeoJSON of my search and keep it hidden using the hidden-xxs-up CSS class.

Geocoded Address settings

Map

  • Create a new Map Region. Give it a static ID map_search.It will have three layers. We should have P27_GEOLOCATOR as item to submit.

Map Region settings

Map Layers

Having created the Map, add three new Layers:

Map Search

Map Search Layer

select :P27_GEOLOCATOR geolocation from dual

Distance Circle

Distance Circle Layer

with trip_details as (
    select  :P27_GEOLOCATOR geolocation --'{"type": "Point", "coordinates": [-0.03377, 51.50347]}' geolocation 
    from dual
)

select sdo_util.circle_polygon (
         --point            => geolocation,  
         center_longitude => JSON_VALUE(geolocation,'$.coordinates[0]'), 
         center_latitude  => JSON_VALUE(geolocation,'$.coordinates[1]'),
         radius           => mcl_app_util.fn_convert_distance (
                                distance  => :P27_DISTANCE,
                                unit_from => :P27_UNIT,
                                unit_to   => 'km' ) * 1000,  -- in meters 
         arc_tolerance    => 5 )                             
       as geometry
   from trip_details

Nearby Stadiums

Nearby Stadiums Layer

with nearby_stadiums as (
    select distinct
           sdo_geometry(2001, 4326, sdo_point_type(longitude, latitude, null), null, null) sdo_geometry_stadium,
           sdo_geometry(2001, 4326, sdo_point_type( JSON_VALUE(:P27_GEOLOCATOR,'$.coordinates[0]'), 
                                                    JSON_VALUE(:P27_GEOLOCATOR,'$.coordinates[1]'), 
                                                    null), null, null ) sdo_geometry_selected,
           a.*
    from stadiums a
    where longitude is not null
        and latitude is not null
)

select round(sdo_geom.sdo_distance( sdo_geometry_stadium, sdo_geometry_selected, 0.01, 'unit=KM' ),0) distance,
       a.* 
from nearby_stadiums a
where sdo_geom.sdo_distance( sdo_geometry_stadium, sdo_geometry_selected, 0.01, 'unit=KM' ) < mcl_app_util.fn_convert_distance (
                                                                                                    distance  => :P27_DISTANCE,
                                                                                                    unit_from => :P27_UNIT,
                                                                                                    unit_to   => 'km' )

Additionally, add the following HTML after selecting the Tooltip Advanced Formatting option:

<h6>&TEAM.</h6>
<p>&LEAGUE.{if STATE_NAME/} ,&STATE_NAME.{endif/}, &DIVISION.</p>
<p>Distance: &DISTANCE. km</p>

Note that Template Directives are supported here, so you can create some really advanced Tooltips for each data point on your map.

Computation

Add the following Before Header Computation, so your map has some default state. Otherwise, it might fail, because no Geocoded Address is currently selected.

Before Header Computation

Dynamic Actions

You will need two Dynamic Actions:

  • One to trigger the Geocoding
  • Another one to refresh the Map and all of its Layers, using the Geocoded Address selected by the user

Dynamic Actions

Trigger Geocoding on Search Button click

Trigger Geocoding Dynamic Action

Refresh and centre the Map

Refresh and centre Map Dynamic Action

The last True Action you have to add is the one that Zooms and centres the Map, using the Geolocation selected:

Zoom and Centre

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,
    lLocationId  = apex.item("P27_GEOLOCATOR").getValue(),
    lFeature     = lMapRegion.call("getFeature", lLayerId, lLocationId ),
    lPosition;

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

//if ( lFeature.geometry ) {

    //lPosition    = lFeature.geometry.coordinates;
    lPosition = jQuery.parseJSON( lLocationId );

    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 12, zoom to 9. Otherwise do nothing.
    if ( lCurrentZoom < 12 ) {
        lMapRegion.call( "setZoomLevel", 9 );
    }
    setTimeout( function() {lMapRegion.call( "displayPopup", "infoWindow", lLayerId, lLocationId.toString(), false )}, 500 );

//}