Photo by Greg Rosenke on Unsplash
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 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.