Marking Countries on the Map - Oracle APEX, Map and 'Have Been' App

Many applications allow us to track the history of our travels by marking the countries, regions, cities, or places we have visited. Can we implement a similar application in Oracle APEX? Of course! It’s very simple, thanks to the Map region. What’s the plan for today? Prepare spatial data, create a map, set up dynamic actions, write a few lines of code, save, and run. That’s it!

Data

On the Internet, we can find a lot of ready-made geographic data that we can use in our applications. In this particular case, I use GeoJSON polygons for all countries of the world.

In addition to geographic data, the table includes columns such as "ID" (the primary key), "COUNTRY" (country name), and "SELECTED" (a flag indicating whether the country is marked on the map or not). We can store our geographic data in GeoJSON, SDO_GEOMETRY format, or as a regular VARCHAR (longitude and latitude). More about spatial data formats can be found here

Map and Layers

No surprises here - we'll start by creating a map. Select the "Map" component, then create two layers. The first layer, "My World," displays all the countries from our table. Configure it as follows:

Identification

Name: MY_WORLD
Layer Type: Polygons

Label

Label: My World

Source

Location: Local Database
Table Name: COUNTRIES_ALL (select the table where you store the data)

Column Mapping

Geometry Column Data Type: GeoJSON
GeoJSON Column: Geometry

Primary Key Column: ID(it is very important to indicate the column with the primary key because you will use it later to mark the visited country).

Next, configure the second layer, "MY_COUNTRIES," in the same way, with one small exception. In the "Source" section, set the "Where Clause" field to SELECTED = 1. This will ensure that only selected countries are displayed with the new layer.

Of course, in the "Appearance" section, you can configure parameters such as "Fill Color," "Fill Opacity," and "Stroke Color," which will make the created layers more user-friendly.

Now, our application should look something like this:

Selecting Objects

Oracle APEX, with the Map region, provides four events that allow us to create interactive maps. First, we create a Hidden Item called "P800_COUNTRY_ID" to store the ID of the selected object. Then, create a dynamic action and configure it as follows:

Identification

Name: Select Country

When

Event: Map Object Clicked [Map]
Selection Type: Region
Region: Map (our Map region)

Then create three TRUE Actions:

Action: Set Value

Settings:

Set Type: JavaScript Expression
JavaScript Expression: this.data.id

Affected Elements

Selection Type: Item(s)
Item(s): P800_COUNTRY_ID

Action: Execute Server-side Code

Settings:

Language: PL/SQL
PL/SQL Code:

declare
    l_selected number;
begin
    select selected 
    into l_selected 
    from countries_all 
    where id = :P800_COUNTRY_ID;

    if l_selected = 0 then 
        update countries_all 
        set selected = 1 
        where id = :P800_COUNTRY_ID;
    else
        update countries_all 
        set selected = 0 
        where id = :P800_COUNTRY_ID;
    end if;
end;

Items to Submit: P800_COUNTRY_ID

Action: Refresh

Affected Elements

Selection Type: Region
Region: Map (our Map region)

To sum up, our dynamic action should look like this:

After clicking on any country (object), the "SELECTED" flag will be set to 1, and the object will be displayed on the "My Countries" layer. Clicking the object again will deselect it and remove it from the mentioned layer. Our application might look something like this:

Conclusions

Building an application that allows you to mark the countries you have visited is very simple. It is worth mentioning that in this case, we are selecting polygons; however, selecting lines or points can be implemented in the same way (the type of layer that displays selected objects will change). Once again, thanks to Oracle APEX and the magic of low-code development, we can deliver new functionality in just a few minutes.