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
Table of contents
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.
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.
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.
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 Layers
Having created the Map, add three new Layers:
Map Search
select :P27_GEOLOCATOR geolocation from dual
Distance Circle
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
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.
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
Trigger Geocoding on Search Button click
Refresh and centre the Map
The last True Action you have to add is the one that Zooms and centres the Map, using the Geolocation selected:
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 );
//}