From OpenAvenueMap to Power BI: Visualizing Wild Swimming Places

sport is orienteering and, when I’m planning a trip, the primary merchandise I purchase is a map of the vacation spot. I like to review it, determine the place to go, discover particulars and curious options in it. Then, I purchase extra specialised maps, reminiscent of maps of nationwide parks.

I like wild swimming! I like these freezing, crystal clear swimming spots in mountain rivers. Take me to a kind of and you will notice me glad as a pet with a brand new bone and if, for some purpose, you ever really feel like giving me a present, go for a “wild swim” collection guide from wild things publishing, you’ll win my coronary heart.

One of as of late I used to be exploring OpenStreetMap (sure, I like maps!) in search of new spots to dive in and got here to appreciate most of them don’t have photographs; and, go determine, in some circumstances I’ve my very own photographs of these locations!

So, I made a decision it was a very good alternative to play a little bit with OpenAvenueMap and Power BI to create a software to assist me discover out there wild swimming spots and add photographs when wanted.

OpenAvenueMap 101

As, I hope, you all know, OpenAvenueMap (OSM) is a free to make use of crowdsourced undertaking which maps the world. OSM is the Wikipedia of maps and, like its encyclopedia pal, anybody can contribute. Updating or including information may be very straightforward. As so, editors have an awesome accountability and must be sure of the modifications they make as a result of there isn’t any automated management of the edits and modifications seem instantly. Updates can be reviewed afterwards by way of neighborhood monitoring, validation instruments, and peer corrections, however till then incorrect information could also be proven to customers.

OSM is a big undertaking with many functionalities however for our undertaking we actually simply want to know a few them.

Nodes, methods, relations and tags

Nodes are the elemental blocks of OSM. A node is like an atom; it’s the smallest unit you may create on OSM and represents a single level on the Earth floor.

Each node can carry tags. A tag is a key=worth mixture. For instance, a node tagged with amenity=restaurant and identify=Central Pub tells us that this level is a restaurant named Central Pub. The full definition may very well be one thing like this:

List 1 – XML definition of the Central Café Node

Then now we have methods and, whereas nodes are factors, methods are a sequence of nodes that type strains and shapes. We can use them to create roads, for instance, and if the primary and final nodes of a manner are the identical, they change into a polygon which we use to characterize areas like buildings or lakes. For instance, this is able to outline a constructing:

List 2 – XML Bulding definition

And then now we have options that are too complicated to explain with a single node or manner. Imagine a bus route, that spans a number of roads and stops. For that we’d like a relation; a relation can tie all these parts right into a single object. Here’s a bus line with two stops and a three-node manner (generated by Copilot).

List 3 – Bus route and all its defining parts as created by Copilot

In our undertaking we’re going to use nodes, methods, relations and simply a few tags, particularly, name,description, leisure and image.

Name and outline

Those are fairly self-explanatory; identify is the first identify of the characteristic as identified in the actual phrase and description ought to comprise extra details about it. We are going to make use of these to establish and describe the options we’re 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 undertaking the leisure key can be used to filter the objects we need to see. Now, that is harder than it appears at a primary look! There are a number of values for leisure which are related to swimming spots: swmming_area; bathing_place; swimming_pool; and many others.

To assist us kind all this out, OSM as a web page discussing the place to make use of every of them: Swimming and bathing. Reading the article, we are able to see there are two tags of curiosity 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’ll filter on any of these tags.

Image

We are going to make use of picture for visualizing and attaching, if wanted, the photographs. The secret’s 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 seems to be like there’s been quite a lot of dialogue in regards to the issues of utilizing a single hyperlink to a probably unknown photograph. There’s additionally a web page discussing the right way to do photo linking, describing the varied methods of attaining this end result.

For now, there appears to be no consensus on the right way to resolve this, so we’ll maintain the custom and go together with “is being used by mappers”. Not good, I do know, however sufficient to have some enjoyable with out going towards the neighborhood guidelines.

And that is all we have to learn about OSM for our undertaking. Now, how will we get it?

Overpass API/Overpass QL

Overpass is a read-only API for OpenAvenueMap, as so we are able to’t use it to replace any information, however it may be used to get any data from the OSM database. Being read-only is all we’d like and can be a bonus, we are able to make certain that any mistake we do received’t break something inside OSM.

To question Overpass API we’re going to use Overpass Query Language (Overpass QL), a C-syntax-like language. There can be Overpass XML, one other language to question the API however QL is extra concise, cleaner and has higher dealing with of variables and units, all options we’ll recognize in our undertaking.

Even if I needed, I’d be unable to elucidate both the API or the QL language intimately. I’m a rookie on each, however we don’t have to be specialists to have the ability to do attention-grabbing issues with them or with another topic for that matter.

So, what will we need to retrieve from OSM database? We need identify, description, picture, lat and lon tags, for wild swimming spots in Portugal and Spain (my playground, different individuals playgrounds might range).

We are going to do the question in Overpass QL and we’ll use Overpass Turbo, a web-based interface for Overpass API, to assist us construct the question. We will use it to check our question and when it’s working as we would like, we’ll copy the question to make use of in Power Bi.

Figure 1 exhibits us what Overpass Turbo seems to be like. You can see the default question within the left and the map on the best. There’s a Run button on the highest left which runs the question and exhibits the outcomes. It’s so simple as it will get!

The default question may be very easy: node [amenity=drinking_water] which returns all of the nodes of potable water inside {{field}} which represents the world seen within the map. Please be aware that {{field}} will not be a component of Overpass API, it solely works inside Overpass Turbo.

After a profitable run you may see the outcomes on the map:

Building our question

As I’m not an knowledgeable on Overpass QL, my strategy goes step-by-step. We can begin with one thing easy like: Give me all of the swimming_area in Spain:

space["ISO3166-1"="ES"];
nwr[leisure=swimming_area](space);
out heart;

List 4 – Get all of the swimming areas in Spain

We begin by defining the space we would like. We use the worldwide customary ISO 3166 to restrict the world to Spain.

Then we ask for all of the nodes, methods and relations (nwr) which have the tag leisure=swimming_area in that space. Without the (space) clause, we might be getting the information in all of the world. Don’t neglect it!

This prepares the information, however to return it, we’d like the out command. We can be utilizing the weather as factors in our Power BI map, so when methods or relations are returned, we have to get a single pair of coordinates for it, not your entire definition, thus we use the heart output modifier.

Note additionally that, as in C programming language, all statements finish with a semicolon.

Now, let’s add the bathing_place.

space["ISO3166-1"="ES"];
nwr[leisure=bathing_place](space)->.tub;
nwr[leisure=swimming_area](space)->.swim;
(.tub;.swim;);
out heart;

List 5 – Get all of the swimming areas and bathing locations in Spain

This time, we’re utilizing variables. We save the separated outcomes to variables tub and swim after which create a set with each: (.tub;.swim;);. When we run the out command, the set simply created can be returned.

Notice that in Overpass QL, variables should be referenced with a dot earlier than the identify.

Finally, we add Portugal:

(
  space["ISO3166-1"="PT"];
  space["ISO3166-1"="ES"];
)->.iberia;
nwr[leisure=bathing_place](space.iberia)->.tub;
nwr[leisure=swimming_area](space.iberia)->.swim;
(.tub;.swim;);
out heart;

List 6 – Get all of the swimming areas and bathing locations for Spain and Portugal

We begin by creating the world set iberia together with each Portugal and Spain. Then we simply want to make use of that space when calling nwr. Iberia is an space set so we must always use space.iberia and never simply .iberia.

This is all we’d like from Overpass, however as you may think about there’s much more! You can study all about it on the Overpass site.

Getting the URL to make use of in Power BI

We have our question virtually prepared; we simply want a little bit tweak. We have already seen the results of our question on the map, let’s take a look at the uncooked information now. For that, press the Data button on the highest proper nook.

As you may see, the question returns the information in XML. We can use this in Power BI, however it is going to want further work to rework the question within the information we’d like. It’s not tough, however it’s simpler if we get the information already in tabular format, for instance in CSV, which Overpass permits us to do, merely including this line at first of the question:

[out:csv(name,::lat, ::lon, image, description; true; ';')];

List 7 – Line of code to pressure the question to return CSV

The syntax is simple to know:

• Return leads to CSV;
• Return identify, picture, and description tags;
• Return Overpass lat e lon. Overpass wants the particular notation :: for inside parts which aren’t tags, reminiscent of lat, lon or id;
• true to incorporate a header row;
• Use semicolonas column separator;

At this level, now we have two methods to get the hyperlink for use in Power BI.

One is to generate a “complete” hyperlink. Click the Export button within the high left nook. The possibility we would like is uncooked information instantly from Overpass API, however in case you click on it, it could obtain the file with out supplying you with the hyperlink, so most likely you’ll have to right-click the hyperlink and select copy hyperlink tackle. This will give us the hyperlink we have to use in Power BI:

Link 1 – Complete tackle of our question

The different manner is far cleaner. You simply have to know that Overpass hyperlinks begin with:

Link 2 – widespread tackle to all Overpass queries

I’ll present you each methods, however I favor the second. It’s simpler to know and to make any wanted change.

Getting Openstreetmap information in Power BI

At this level, we go away Overpass Turbo and enter Power BI. Open a clean report and select Get information from one other supply on the primary display screen and search for Web on the listing of information sources.

Now, as I informed you earlier than, you may both enter the entire hyperlink utilizing Basic mode like in Figure 8 or you may change to Advanced mode and break the tackle in bits like in Figure 9.

In the Advanced mode there are not any guidelines as to what goes in every half so long as the whole lot is entered. You select what fits you, however I wish to enter the widespread tackle on the primary half, which you’ll see in Link 2 after which copy the code line by line from Overpass Turbo. You can break it in a number of components in case you like. As you may simply see, future modifications are fairly easy to use on this mode.

After urgent OK, and ready a number of seconds, you must see the preview information window. Please be aware that the server will be too busy and unable to answer. When that occurs, endurance is required; that’s the worth of free.

All going properly, the wizard will retrieve and perceive the returned information: CSV with the semicolon as column separator. However, it didn’t determine that our first row has columns names, not information. We can resolve that simply on Power Query Editor which we enter by urgent Transform Data.

To have the information prepared to make use of, we simply want to advertise the primary row to header which will be achieved on the choice Transform → Use First Row as Headers.

The information is now prepared for use, however we need to add an additional column. The concept of the undertaking is so as to add photographs the place they’re lacking, so to make it simpler to differentiate those which have photograph from those which don’t, we’ll add a column with information information. We received’t be doing any superior take a look at; we’ll solely test if there’s any textual content in picture. If there may be, we’ll assume there’s a photograph there.

To add a column, we open Add Column → Custom Column and for our take a look at we enter the next DAX code:

if [image] <> "" then "with photo" else "without photo"

List 8 – DAX to test if there’s a hyperlink to a photograph.

We have what we have to plot our map, however there are some further steps we’d need to take. Power Query Editor might add some steps altering the kind of the columns, however we’re OK with the default textual content sort, so we are able to delete these. Also, now is an effective time to rename columns and even the question or do another cosmetics we wish, however what now we have now’s fairly sufficient for our wants.

Data, Map, Action!

The information half is finished; it’s now time to play with the visuals.

Add an Azure maps visible (there are a number of maps, you should definitely choose the best one).

Now, add the next fields:

• @lat in Latitude
• @lon in Longitude
• Photo in Legend

And that’s all! Wait a few minutes and you must see your map with the wild swimming spots marked and divided between those with photograph and those with out.

For our subsequent step we’re going to add the photographs. We need to see the photograph of the spot once we hover over it. For that we’ll create a tooltip web page and add an picture view to it.

Add a web page urgent the + on the underside subsequent to Page 1. Page 2 is created; proper click on its identify and choose Hide, since we would like the web page to be seen solely as a tooltip on the map.

Now, go to the Format → Page data and on Page sort choose Tooltip. The canvas is resized to the default tooltip dimension. You can change its dimension, however the default one is okay for now.

Our tooltip goes to indicate the photograph, so we have to insert the picture visible onto our tooltip web page and set the Data subject to our picture subject. When you insert the picture subject it is going to change to “First image”. This occurs as a result of the visible can solely present one image, so it is going to select the primary one on the information; however because the popup web page can be referred to as once we are hovering one information report, there can be just one image to indicate.

The tooltip web page is all set. We simply want to return to the map on Page 1 and to make use of the web page as its tooltip. Select the map visible and on Properties → Tooltips set the Type to Report web page and Page to Page 2.

Now, once we hover over a spot with a photograph we’ll see it. And that’s it! Since now we have model 1 prepared, we simply want some bells and whistles.

Finishing touches

The attention-grabbing components are all finished, and just some aesthetics are lacking, which I hope don’t have to be defined intimately. On the tooltip web page, I added a card with the identify and description fields on the Categories and Value fields, respectively. On the primary/map web page, I added:

• A desk which exhibits depend of spots, with and with out photograph.
• A filter to decide on between with and/or with out photograph.
• A filter to search for spots by identify.
• An inventory of all of the spots.
• Some frames (textual content bins) to make the structure extra interesting.

You can see under the ultimate end result:

You can see it reside in Wild Swimming Spots and you’ll obtain/clone the code from GitHub.

Just be warned that there appears to be a bug in azure maps. When you apply your first filter, the map doesn’t replace; it solely updates after the second filter or in case you work together with the map visible earlier than the primary filter. There are some discussions on-line about this bug, however I couldn’t discover any official information about it. If anybody is aware of extra about this and the right way to resolve it, I’d love to listen to from you!

Contributing to my very own undertaking

Now that the whole lot is prepared, I can begin to do some contributions. I’ve ready a folder in considered one of my subdomains to retailer photographs of the wild swimming spots I’ve visited; I simply have to add the photographs I’ve and use them in OSM.

I do have these photographs in Google photographs, however OSM tips say that the picture key ought to level to a file and google photograph doesn’t have that characteristic, the hyperlink is to a web page that exhibits the photograph however it’s not the file per se.

Of course, if a Google photograph hyperlink was used it wouldn’t work in Power BI, anyway.

So, all I have to do now, is add a photograph to my web site, replace OSM and voilà, it’s finished!

Figure 20 – My internet tackle for the photographs I’ll use – screenshot by the writer

Figure 21 – Updating the Image key on  the factor – screenshot by the writer

Figure 22 – The PowerBI app  displaying my first photograph – screenshot by the writer

I’m actually proud of the results of this work! I now have a software which may be very helpful for my travels and, hopefully, for different individuals as properly.

Using OSM information in Power BI is a subject I’m captivated with; I can be again on this topic as quickly as potential.

Data and Tools

• Data sourced from OpenAvenueMap by way of Overpass API. © OpenAvenueMap contributors. Licensed below ODbL 1.0.
• Overpass API is free software program licensed below the GNU AGPLv3.