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 check it, resolve the place to go, discover particulars and curious options in it. Then, I purchase extra specialised maps, corresponding to maps of nationwide parks.
I really like wild swimming! I really like these freezing, crystal clear swimming spots in mountain rivers. Take me to a kind of and you will notice me comfortable as a pet with a brand new bone and if, for some cause, you ever really feel like giving me a present, go for a “wild swim” sequence ebook from wild things publishing, you’ll win my coronary heart.
Considered one of today I used to be exploring OpenStreetMap (sure, I really like maps!) in search of new spots to dive in and got here to appreciate most of them don’t have images; and, go determine, in some instances I’ve my very own images of these locations!
So, I made a decision it was alternative to play a little bit with OpenStreetMap and Power BI to create a software to assist me discover accessible wild swimming spots and add images when wanted.
OpenStreetMap 101
As, I hope, you all know, OpenStreetMap (OSM) is a free to make use of crowdsourced venture which maps the world. OSM is the Wikipedia of maps and, like its encyclopedia good friend, anybody can contribute. Updating or including information could be very straightforward. As so, editors have an ideal accountability and needs to be sure of the modifications they make as a result of there isn’t a automated management of the edits and modifications seem instantly. Updates will likely be reviewed afterwards via group monitoring, validation instruments, and peer corrections, however till then incorrect information could also be proven to customers.
OSM is a large venture with many functionalities however for our venture 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’ll be able to create on OSM and represents a single level on the Earth floor.
Every 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 entire definition might be one thing like this:
Listing 1 – XML definition of the Central Café Node
Then we’ve methods and, whereas nodes are factors, methods are a sequence of nodes that kind strains and shapes. We will use them to create roads, for instance, and if the primary and final nodes of a approach are the identical, they turn out to be a polygon which we use to characterize areas like buildings or lakes. For instance, this is able to outline a constructing:
Listing 2 – XML Bulding definition
After which we’ve options that are too complicated to explain with a single node or approach. Think about a bus route, that spans a number of roads and stops. For that we want a relation; a relation can tie all these components right into a single object. Right here’s a bus line with two stops and a three-node approach (generated by Copilot).
Listing 3 – Bus route and all its defining components as created by Copilot
In our venture we’re going to use nodes, methods, relations and simply a few tags, particularly, name,description, leisure and image.
Identify and outline
These are fairly self-explanatory; identify is the first identify of the characteristic as identified in the true phrase and description ought to include extra details about it. We’re going to use these to determine and describe the options we’re viewing.
Leisure
According to OSM, “The leisure key’s used for options related to leisure actions — locations folks go to of their free time for sport, recreation, rest, or leisure.”
In our venture the leisure key will likely be used to filter the objects we wish to see. Now, that is tougher than it appears at a primary look! There are a number of values for leisure which can be linked to swimming spots: swmming_area; bathing_place; swimming_pool; and so forth.
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. Studying the article, we are able to see there are two tags of curiosity to us; leisure=bathing_place: “Public bathing place with out amenities, pure water our bodies” and leisure=swimming_area: “Enclosed pure water space inside a facility”. So, we’ll filter on any of these tags.
Picture
We’re going to use picture for visualizing and attaching, if wanted, the images. The secret is described in OSM as “The picture key has been utilized by mappers to hyperlink an externally hosted picture that depicts the tagged object.”.
Observe the “has been used”. It seems like there’s been a variety of dialogue in regards to the issues of utilizing a single hyperlink to a probably unknown picture. There’s additionally a web page discussing find out how to do photo linking, describing the assorted methods of attaining this consequence.
For now, there appears to be no consensus on find out how to remedy this, so we’ll hold the custom and go along with “is being utilized by mappers”. Not excellent, I do know, however sufficient to have some enjoyable with out going towards the group guidelines.
And that is all we have to learn about OSM for our venture. Now, how will we get it?
Overpass API/Overpass QL
Overpass is a read-only API for OpenStreetMap, 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 want and can also be a bonus, we are able to make sure that any mistake we do received’t spoil something inside OSM.
To question Overpass API we’re going to use Overpass Question Language (Overpass QL), a C-syntax-like language. There’s additionally 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 respect in our venture.
Even when I wished, 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 consultants to have the ability to do attention-grabbing issues with them or with another topic for that matter.
So, what will we wish to retrieve from OSM database? We would like identify, description, picture, lat and lon tags, for wild swimming spots in Portugal and Spain (my playground, different folks playgrounds could range).
We’re 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 are going to 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 Energy Bi.
Determine 1 exhibits us what Overpass Turbo seems like. You’ll be able to 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 could 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 observe that {{field}} isn’t a component of Overpass API, it solely works inside Overpass Turbo.
After a profitable run you’ll be able to see the outcomes on the map:
Constructing our question
As I’m not an knowledgeable on Overpass QL, my method goes step-by-step. We will 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;Listing 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. With out the (space) clause, we’d be getting the data in all of the world. Don’t overlook it!
This prepares the information, however to return it, we want the out command. We will likely be utilizing the weather as factors in our Energy BI map, so when methods or relations are returned, we have to get a single pair of coordinates for it, not your complete definition, thus we use the heart output modifier.
Observe 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;Listing 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;);. After we run the out command, the set simply created will likely be returned.
Discover that in Overpass QL, variables should be referenced with a dot earlier than the identify.
Lastly, 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;Listing 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 should always use space.iberia and never simply .iberia.
That is all we want from Overpass, however as you’ll be able to think about there’s much more! You’ll be able to study all about it on the Overpass site.
Getting the URL to make use of in Energy BI
We now have our question virtually prepared; we simply want a little bit tweak. We now have already seen the results of our question on the map, let’s have a look at the uncooked information now. For that, press the Information button on the highest proper nook.
As you’ll be able to see, the question returns the information in XML. We will use this in Energy BI, however it’s going to want further work to rework the question within the information we want. It’s not troublesome, but it surely’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; ';')];Listing 7 – Line of code to pressure the question to return CSV
The syntax is straightforward to know:
- Return ends in CSV;
- Return identify, picture, and description tags;
- Return Overpass lat e lon. Overpass wants the particular notation :: for inner components which aren’t tags, corresponding to lat, lon or id;
- true to incorporate a header row;
- Use semicolonas column separator;
At this level, we’ve two methods to get the hyperlink for use in Energy BI.
One is to generate a “full” hyperlink. Click on the Export button within the prime left nook. The choice we would like is uncooked information instantly from Overpass API, however if you happen to click on it, it could obtain the file with out supplying you with the hyperlink, so in all probability you’ll have to right-click the hyperlink and select copy hyperlink tackle. This can give us the hyperlink we have to use in Energy BI:
Hyperlink 1 – Full tackle of our question
The opposite approach is way cleaner. You simply have to know that Overpass hyperlinks begin with:
Hyperlink 2 – frequent tackle to all Overpass queries
I’ll present you each methods, however I desire the second. It’s simpler to know and to make any wanted change.
Getting Openstreetmap information in Energy BI
At this level, we go away Overpass Turbo and enter Energy BI. Open a clean report and select Get information from one other supply on the principle display and search for Internet on the checklist of knowledge sources.
Now, as I informed you earlier than, you’ll be able to both enter the entire hyperlink utilizing Fundamental mode like in Determine 8 or you’ll be able to change to Superior mode and break the tackle in bits like in Determine 9.
Within the Superior mode there are not any guidelines as to what goes in every half so long as all the pieces is entered. You select what fits you, however I wish to enter the frequent tackle on the primary half, which you’ll see in Hyperlink 2 after which copy the code line by line from Overpass Turbo. You’ll be able to break it in a number of elements if you happen to like. As you’ll be able to simply see, future modifications are fairly easy to use on this mode.
After urgent OK, and ready a number of seconds, you need to see the preview information window. Please observe that the server may be too busy and unable to answer. When that occurs, endurance is required; that’s the worth of free.
All going effectively, the wizard will retrieve and perceive the returned information: CSV with the semicolon as column separator. Nonetheless, it didn’t work out that our first row has columns names, not information. We will remedy that simply on Energy Question Editor which we enter by urgent Rework Information.
To have the information prepared to make use of, we simply want to advertise the primary row to header which may be achieved on the choice Rework → Use First Row as Headers.
The information is now prepared for use, however we wish to add an additional column. The thought of the venture is so as to add images the place they’re lacking, so to make it simpler to differentiate those which have picture from those which don’t, we’ll add a column with information data. We received’t be doing any superior take a look at; we’ll solely examine if there’s any textual content in picture. If there may be, we’ll assume there’s a photograph there.
So as to add a column, we open Add Column → Customized Column and for our take a look at we enter the next DAX code:
if [image] <> "" then "with picture" else "with out picture"Listing 8 – DAX to examine if there’s a hyperlink to a photograph.
We now have what we have to plot our map, however there are some further steps we would wish to take. Energy Question Editor could add some steps altering the kind of the columns, however we’re OK with the default textual content kind, so we are able to delete these. Additionally, now is an efficient time to rename columns and even the question or do another cosmetics we wish, however what we’ve now could be fairly sufficient for our wants.
Information, Map, Motion!
The information half is completed; it’s now time to play with the visuals.
Add an Azure maps visible (there are a number of maps, make sure you choose the best one).
Now, add the next fields:
- @lat in Latitude
- @lon in Longitude
- Photograph in Legend
And that’s all! Wait a few minutes and you need to see your map with the wild swimming spots marked and divided between those with picture and those with out.
For our subsequent step we’re going to add the images. We wish to see the picture of the spot after 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 Web page 1. Web page 2 is created; proper click on its identify and choose Conceal, since we would like the web page to be seen solely as a tooltip on the map.
Now, go to the Format → Web page data and on Web page kind choose Tooltip. The canvas is resized to the default tooltip measurement. You’ll be able to change its measurement, however the default one is okay for now.
Our tooltip goes to indicate the picture, so we have to insert the picture visible onto our tooltip web page and set the Information area to our picture area. If you insert the picture area it’s going to change to “First picture”. This occurs as a result of the visible can solely present one image, so it’s going to select the primary one on the information; however because the popup web page will likely be known as after we are hovering one information report, there will likely be just one image to indicate.
The tooltip web page is all set. We simply want to return to the map on Web page 1 and to make use of the web page as its tooltip. Choose the map visible and on Properties → Tooltips set the Kind to Report web page and Web page to Web page 2.
Now, after we hover over a spot with a photograph we’ll see it. And that’s it! Since we’ve model 1 prepared, we simply want some bells and whistles.
Ending touches
The attention-grabbing elements 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 Classes and Worth fields, respectively. On the principle/map web page, I added:
- A desk which exhibits rely of spots, with and with out picture.
- A filter to decide on between with and/or with out picture.
- A filter to search for spots by identify.
- A listing of all of the spots.
- Some frames (textual content bins) to make the format extra interesting.
You’ll be able to see under the ultimate consequence:
You’ll be able to see it stay in Wild Swimming Spots and you’ll obtain/clone the code from GitHub.
Simply be warned that there appears to be a bug in azure maps. If you apply your first filter, the map doesn’t replace; it solely updates after the second filter or if you happen to 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 data about it. If anybody is aware of extra about this and find out how to remedy it, I’d love to listen to from you!
Contributing to my very own venture
Now that all the pieces is prepared, I can begin to do some contributions. I’ve ready a folder in certainly one of my subdomains to retailer images of the wild swimming spots I’ve visited; I simply have to add the images I’ve and use them in OSM.
I do have these images in Google images, however OSM tips say that the picture key ought to level to a file and google picture doesn’t have that characteristic, the hyperlink is to a web page that exhibits the picture but it surely’s not the file per se.
After all, if a Google picture hyperlink was used it wouldn’t work in Energy BI, anyway.
So, all I have to do now, is add a photograph to my web site, replace OSM and voilà, it’s finished!
Determine 20 – My internet tackle for the images I’ll use – screenshot by the creator
Determine 21 – Updating the Picture key on the ingredient – screenshot by the creator
Determine 22 – The PowerBI app displaying my first picture – screenshot by the creator
I’m actually pleased with the results of this work! I now have a software which could be very helpful for my travels and, hopefully, for different folks as effectively.
Utilizing OSM information in Energy BI is a subject I’m obsessed with; I will likely be again on this topic as quickly as attainable.
Information and Instruments
- Information sourced from OpenStreetMap through Overpass API. © OpenStreetMap contributors. Licensed underneath ODbL 1.0.
- Overpass API is free software program licensed underneath the GNU AGPLv3.
