# How to find nearest stadiums using Oracle APEX maps and geolocation

<h1>The use case</h1>

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**)

<h1>The demo</h1>

See a demo of the app on my free Oracle APEX workspace here:<br>
[US Nearby Stadiums Locator](https://apex.oracle.com/pls/apex/r/gamma_dev/demo/us-stadiums-map)

> 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.

<h1>What components and features are there in use in this application?</h1>

- 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

<h1>Step by Step Guide</h1>
<h2>Items</h2>

- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1665516623076/Yctp7FgH6.jpg align="center")

  <h2>Geocoded Address</h2>

- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1665517269716/k6hXVVlOT.jpg align="center")

> 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1665526472493/BCAxtl01P.pngalign="center")

  <h2>Map</h2>

- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1665528075510/ENBMg4h4z.png align="center")

   <h3>Map Layers</h3>

Having created the **Map**, add three new **Layers**:
 
<h4>Map Search</h4>
 
![Map Search Layer](https://cdn.hashnode.com/res/hashnode/image/upload/v1665528896967/1jkgqFkVv.png align="center")

```sql
select :P27_GEOLOCATOR geolocation from dual
``` 

<h4>Distance Circle</h4>

![Distance Circle Layer](https://cdn.hashnode.com/res/hashnode/image/upload/v1665531404541/hjeJQPfW1.png align="center")

```sql
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
``` 

<h4>Nearby Stadiums</h4>

![Nearby Stadiums Layer](https://cdn.hashnode.com/res/hashnode/image/upload/v1665531845462/AhVtNuJgH.png align="center")

```sql
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:

```html
<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.

  <h3>Computation</h3>

> 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1665532301934/SIQIoNcvL.png align="center")

  <h3>Dynamic Actions</h3>

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1665532531181/1zqcHbj8p.png align="center")

<h4>Trigger Geocoding on Search Button click</h4>

![Trigger Geocoding Dynamic Action](https://cdn.hashnode.com/res/hashnode/image/upload/v1665533763023/XdqnWWB_V.png align="center")

<h4>Refresh and centre the Map</h4>

![Refresh and centre Map Dynamic Action](https://cdn.hashnode.com/res/hashnode/image/upload/v1665534174851/E4TPZbZkj.png align="center")

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

![Zoom and Centre](https://cdn.hashnode.com/res/hashnode/image/upload/v1665534324458/n3WQ2slaK.png align="center")

```javascript
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 );

//}
```