Many applications allow us to track the history of our travels by marking the country, region, city, or place we have visited. Can we implement a similar application in Oracle APEX? Of course! It's very simple thanks to the Map region. Plan for today? Prepare spatial data. Create a map and dynamic actions. Write a few lines of code. Save. Run. The end!

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" (primary key), "COUNTRY" (country name) and "SELECTED" (flag whether the country is marked on the map or not). We can store our geographic data in the GoeJSON, SDO_GEOMETRY format or as a regular VARCHAR (longitude and latitude). More about spatial data formats here: here

Map and layers

No surprises, 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 later you will use it to mark the visited country).

Configure the second layer, "MY_COUNTRIES", the same way with a small exception. In the "Source" section, set the "Where Clause" field to "SELECTED = 1". Thanks to this, we will only display selected countries 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 Map region, provides four events that allow us to create interactive maps. First, we create a Hidden Item called "P800_COUNTRY_ID". We will store the ID of the selected object in it. 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. After clicking on the object again, it will be deselected and removed 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 select polygons, but selecting lines or points will be implemented in the same way (the type of layer that displays selected objects will change). Once again, thanks to Oracle APEX and low code magic, we can deliver new functionality in a few minutes.