sport is orienteering and, when I am planning a vacation, the first item I buy is a map of the destination. I like to study it, decide where to go, find details and curious features in it. Then, I buy more specialized maps, such as maps of national parks.
I love wild swimming! I love those freezing, crystal clear swimming spots in mountain rivers. Take me to one of those and you will see me happy as a puppy with a new bone and if, for some reason, you ever feel like giving me a gift, go for a “wild swim” series book from wild things publishing, you’ll win my heart.
One of these days I was exploring OpenStreetMap (yes, I love maps!) looking for new spots to dive in and came to realize most of them don’t have photos; and, go figure, in some cases I have my own photos of those places!
So, I decided it was a good opportunity to play a little with OpenStreetMap and Power BI to create a tool to help me explore available wild swimming spots and add photos when needed.
OpenStreetMap 101
As, I hope, you all know, OpenStreetMap (OSM) is a free to use crowdsourced project which maps the world. OSM is the Wikipedia of maps and, like its encyclopedia friend, anyone can contribute. Updating or adding data is very easy. As so, editors have a great responsibility and should be certain of the changes they make because there is no automatic control of the edits and changes appear immediately. Updates will be reviewed afterwards through community monitoring, validation tools, and peer corrections, but until then incorrect data may be shown to users.
OSM is a huge project with many functionalities but for our project we really just need to understand a couple of them.
Nodes, ways, relations and tags
Nodes are the fundamental blocks of OSM. A node is like an atom; it’s the smallest unit you can create on OSM and represents a single point on the Earth surface.
Each node can carry tags. A tag is a key=value combination. For example, a node tagged with amenity=restaurant and name=Central Pub tells us that this point is a restaurant named Central Pub. The complete definition could be something like this:
List 1 – XML definition of the Central Café Node
Then we have ways and, while nodes are points, ways are a sequence of nodes that form lines and shapes. We can use them to create roads, for example, and if the first and last nodes of a way are the same, they become a polygon which we use to represent areas like buildings or lakes. For example, this would define a building:
List 2 – XML Bulding definition
And then we have features which are too complex to describe with a single node or way. Imagine a bus route, that spans several roads and stops. For that we need a relation; a relation can tie all these elements into a single object. Here’s a bus line with two stops and a three-node way (generated by Copilot).
List 3 – Bus route and all its defining elements as created by Copilot
In our project we are going to use nodes, ways, relations and just a couple of tags, specifically, name,description, leisure and image.
Name and description
Those are quite self-explanatory; name is the primary name of the feature as known in the real word and description should contain additional information about it. We are going to use those to identify and describe the features we are viewing.
Leisure
According to OSM, “The leisure key is used for features associated with leisure activities — places people go to in their free time for sport, recreation, relaxation, or entertainment.”
In our project the leisure key will be used to filter the objects we want to see. Now, this is more difficult than it seems at a first glance! There are multiple values for leisure that are connected to swimming spots: swmming_area; bathing_place; swimming_pool; etc.
To help us sort all this out, OSM as a page discussing where to use each of them: Swimming and bathing. Reading the article, we can see there are two tags of interest to us; leisure=bathing_place: “Public bathing place without facilities, natural water bodies” and leisure=swimming_area: “Enclosed natural water area inside a facility”. So, we will filter on any of those tags.
Image
We are going to use image for visualizing and attaching, if needed, the photos. The key is described in OSM as “The image key has been used by mappers to link an externally hosted image that depicts the tagged object.”.
Note the “has been used”. It looks like there’s been a lot of discussion about the problems of using a single link to a possibly unknown photo. There’s also a page discussing how to do photo linking, describing the various ways of achieving this result.
For now, there seems to be no consensus on how to solve this, so we will keep the tradition and go with “is being used by mappers”. Not perfect, I know, but enough to have some fun without going against the community rules.
And this is all we need to know about OSM for our project. Now, how do we get it?
Overpass API/Overpass QL
Overpass is a read-only API for OpenStreetMap, as so we can’t use it to update any data, but it can be used to get any information from the OSM database. Being read-only is all we need and is also a bonus, we can be sure that any mistake we do won’t ruin anything inside OSM.
To query Overpass API we are going to use Overpass Query Language (Overpass QL), a C-syntax-like language. There is also Overpass XML, another language to query the API but QL is more concise, cleaner and has better handling of variables and sets, all features we will appreciate in our project.
Even if I wanted, I would be unable to explain either the API or the QL language in detail. I’m a rookie on both, but we don’t need to be experts to be able to do interesting things with them or with any other subject for that matter.
So, what do we want to retrieve from OSM database? We want name, description, image, lat and lon tags, for wild swimming spots in Portugal and Spain (my playground, other people playgrounds may vary).
We are going to do the query in Overpass QL and we will use Overpass Turbo, a web-based interface for Overpass API, to help us build the query. We will use it to test our query and when it’s working as we want, we will copy the query to use in Power Bi.
Figure 1 shows us what Overpass Turbo looks like. You can see the default query in the left and the map on the right. There’s a Run button on the top left which runs the query and shows the results. It’s as simple as it gets!
The default query is very simple: node [amenity=drinking_water] which returns all the nodes of potable water within {{box}} which represents the area visible in the map. Please note that {{box}} is not an element of Overpass API, it only works inside Overpass Turbo.
After a successful run you can see the results on the map:
Building our query
As I am not an expert on Overpass QL, my approach is going step by step. We can start with something simple like: Give me all the swimming_area in Spain:
area["ISO3166-1"="ES"];
nwr[leisure=swimming_area](area);
out center;List 4 – Get all the swimming areas in Spain
We start by defining the area we want. We use the international standard ISO 3166 to limit the area to Spain.
Then we ask for all the nodes, ways and relations (nwr) which have the tag leisure=swimming_area in that area. Without the (area) clause, we would be getting the info in all the world. Don’t forget it!
This prepares the data, but to return it, we need the out command. We will be using the elements as points in our Power BI map, so when ways or relations are returned, we need to get a single pair of coordinates for it, not the entire definition, thus we use the center output modifier.
Note also that, as in C programming language, all statements end with a semicolon.
Now, let’s add the bathing_place.
area["ISO3166-1"="ES"];
nwr[leisure=bathing_place](area)->.bath;
nwr[leisure=swimming_area](area)->.swim;
(.bath;.swim;);
out center;List 5 – Get all the swimming areas and bathing places in Spain
This time, we are using variables. We save the separated results to variables bath and swim and then create a set with both: (.bath;.swim;);. When we run the out command, the set just created will be returned.
Notice that in Overpass QL, variables must be referenced with a dot before the name.
Finally, we add Portugal:
(
area["ISO3166-1"="PT"];
area["ISO3166-1"="ES"];
)->.iberia;
nwr[leisure=bathing_place](area.iberia)->.bath;
nwr[leisure=swimming_area](area.iberia)->.swim;
(.bath;.swim;);
out center;List 6 – Get all the swimming areas and bathing places for Spain and Portugal
We start by creating the area set iberia including both Portugal and Spain. Then we just need to use that area when calling nwr. Iberia is an area set so we should use area.iberia and not just .iberia.
This is all we need from Overpass, but as you can imagine there’s a lot more! You can learn all about it on the Overpass site.
Getting the URL to use in Power BI
We have our query almost ready; we just need a little tweak. We have already seen the result of our query on the map, let’s look at the raw data now. For that, press the Data button on the top right corner.
As you can see, the query returns the data in XML. We can use this in Power BI, but it will need extra work to transform the query in the data we need. It’s not difficult, but it’s easier if we get the data already in tabular format, for example in CSV, which Overpass allows us to do, simply adding this line in the beginning of the query:
[out:csv(name,::lat, ::lon, image, description; true; ';')];List 7 – Line of code to force the query to return CSV
The syntax is easy to understand:
- Return results in CSV;
- Return name, image, and description tags;
- Return Overpass lat e lon. Overpass needs the special notation :: for internal elements which aren’t tags, such as lat, lon or id;
- true to include a header row;
- Use semicolonas column separator;
At this point, we have two ways to get the link to be used in Power BI.
One is to generate a “complete” link. Click the Export button in the top left corner. The option we want is raw data directly from Overpass API, but if you click it, it may download the file without giving you the link, so probably you will have to right-click the link and choose copy link address. This will give us the link we need to use in Power BI:
Link 1 – Complete address of our query
The other way is much cleaner. You just need to know that Overpass links start with:
Link 2 – common address to all Overpass queries
I will show you both ways, but I prefer the second one. It’s easier to understand and to make any needed change.
Getting Openstreetmap data in Power BI
At this point, we leave Overpass Turbo and enter Power BI. Open a blank report and choose Get data from another source on the main screen and look for Web on the list of data sources.
Now, as I told you before, you can either enter the complete link using Basic mode like in Figure 8 or you can change to Advanced mode and break the address in bits like in Figure 9.
In the Advanced mode there are no rules as to what goes in each part as long as everything is entered. You choose what suits you, but I like to enter the common address on the first part, which you can see in Link 2 and then copy the code line by line from Overpass Turbo. You can break it in several parts if you like. As you can easily see, future changes are quite simple to apply in this mode.
After pressing OK, and waiting a few seconds, you should see the preview data window. Please note that the server can be too busy and unable to reply. When that happens, patience is required; that’s the price of free.
All going well, the wizard will retrieve and understand the returned data: CSV with the semicolon as column separator. However, it didn’t figure out that our first row has columns names, not data. We can solve that easily on Power Query Editor which we enter by pressing Transform Data.
To have the data ready to use, we just need to promote the first row to header which can be achieved on the option Transform → Use First Row as Headers.
The data is now ready to be used, but we want to add an extra column. The idea of the project is to add photos where they are missing, so to make it easier to distinguish the ones which have photo from the ones which don’t, we will add a column with data info. We won’t be doing any advanced test; we will only check if there’s any text in image. If there is, we will assume there’s a photo there.
To add a column, we open Add Column → Custom Column and for our test we enter the following DAX code:
if [image] <> "" then "with photo" else "without photo"List 8 – DAX to check if there is a link to a photo.
We have what we need to plot our map, but there are some extra steps we might want to take. Power Query Editor may add some steps changing the type of the columns, but we are OK with the default text type, so we can delete those. Also, now is a good time to rename columns or even the query or do any other cosmetics we would like, but what we have now is quite enough for our needs.
Data, Map, Action!
The data part is done; it’s now time to play with the visuals.
Add an Azure maps visual (there are several maps, be sure to select the right one).
Now, add the following fields:
- @lat in Latitude
- @lon in Longitude
- Photo in Legend
And that’s all! Wait a couple of minutes and you should see your map with the wild swimming spots marked and divided between the ones with photo and the ones without.
For our next step we are going to add the photos. We want to see the photo of the spot when we hover over it. For that we will create a tooltip page and add an image view to it.
Add a page pressing the + on the bottom next to Page 1. Page 2 is created; right click its name and select Hide, since we want the page to be visible only as a tooltip on the map.
Now, go to the Format → Page information and on Page type select Tooltip. The canvas is resized to the default tooltip size. You can change its size, but the default one is ok for now.
Our tooltip is going to show the photo, so we need to insert the image visual onto our tooltip page and set the Data field to our image field. When you insert the image field it will change to “First image”. This happens because the visual can only show one picture, so it will choose the first one on the data; but as the popup page will be called when we are hovering one data record, there will be only one picture to show.
The tooltip page is all set. We just need to go back to the map on Page 1 and to use the page as its tooltip. Select the map visual and on Properties → Tooltips set the Type to Report page and Page to Page 2.
Now, when we hover over a spot with a photo we will see it. And that’s it! Since we have version 1 ready, we just need some bells and whistles.
Finishing touches
The interesting parts are all done, and only some aesthetics are missing, which I hope don’t need to be explained in detail. On the tooltip page, I added a card with the name and description fields on the Categories and Value fields, respectively. On the main/map page, I added:
- A table which shows count of spots, with and without photo.
- A filter to choose between with and/or without photo.
- A filter to look for spots by name.
- A list of all the spots.
- Some frames (text boxes) to make the layout more appealing.
You can see below the final result:
You can see it live in Wild Swimming Spots and you can download/clone the code from GitHub.
Just be warned that there seems to be a bug in azure maps. When you apply your first filter, the map doesn’t update; it only updates after the second filter or if you interact with the map visual before the first filter. There are some discussions online about this bug, but I couldn’t find any official info about it. If anyone knows more about this and how to solve it, I would love to hear from you!
Contributing to my own project
Now that everything is ready, I can start to do some contributions. I have prepared a folder in one of my subdomains to store photos of the wild swimming spots I have visited; I just need to upload the photos I have and use them in OSM.
I do have those photos in Google photos, but OSM guidelines say that the image key should point to a file and google photo doesn’t have that feature, the link is to a page that shows the photo but it’s not the file per se.
Of course, if a Google photo link was used it wouldn’t work in Power BI, anyway.
So, all I need to do now, is upload a photo to my site, update OSM and voilà, it’s done!
Figure 20 – My web address for the photos I will use – screenshot by the author
Figure 21 – Updating the Image key on the element – screenshot by the author
Figure 22 – The PowerBI app showing my first photo – screenshot by the author
I am really happy with the result of this work! I now have a tool which is very useful for my travels and, hopefully, for other people as well.
Using OSM data in Power BI is a topic I am passionate about; I will be back on this subject as soon as possible.
Data and Tools
- Data sourced from OpenStreetMap via Overpass API. © OpenStreetMap contributors. Licensed under ODbL 1.0.
- Overpass API is free software licensed under the GNU AGPLv3.