I got data related to streets in my city. Each street has at least 2 sets of coordinates.
My coworkers go through the city and grade the cleanliness.
I would like to show lines on the map with the Azure Maps visual from coordinate to coordinate and color the lines depending on the grades they got.
I tried this GeoJSON format:
{ "type": "FeatureCollection", "features": [ { "type": "Feature", "properties": {}, "geometry": { "type": "LineString", "coordinates": [ [ 10.170448, 53.595264 ], [ 10.174717, 53.594398 ] ] } } ] }
and this WKT format:
LINESTRING(53.585325 10.057296,53.586012 10.058657)
All my data refers to Hamburg, Germany, but with both GeoJSON and WKT I only get bubbles at approximately 48 and -0.5 in France, all bubbles are in the same spot although the correct coordinates are shown in the tooltip when I hover over the bubbles. If I use only one set of coordinates in the regular Map Visual everything works fine.
I tried to solve it with Copilot and GPT, it seems like Azure maps doesn't interpret my Strings as Linestrings.
Ultimately I want to put the strings in the dimension of an SSAS OLAP Cube, but for now I am trying with a simple Excel sheet and can't get the desired results.
Can anyone help me how to set up my data to show lines on the map?
I got data related to streets in my city. Each street has at least 2 sets of coordinates.
My coworkers go through the city and grade the cleanliness.
I would like to show lines on the map with the Azure Maps visual from coordinate to coordinate and color the lines depending on the grades they got.
I tried this GeoJSON format:
{ "type": "FeatureCollection", "features": [ { "type": "Feature", "properties": {}, "geometry": { "type": "LineString", "coordinates": [ [ 10.170448, 53.595264 ], [ 10.174717, 53.594398 ] ] } } ] }
and this WKT format:
LINESTRING(53.585325 10.057296,53.586012 10.058657)
All my data refers to Hamburg, Germany, but with both GeoJSON and WKT I only get bubbles at approximately 48 and -0.5 in France, all bubbles are in the same spot although the correct coordinates are shown in the tooltip when I hover over the bubbles. If I use only one set of coordinates in the regular Map Visual everything works fine.
I tried to solve it with Copilot and GPT, it seems like Azure maps doesn't interpret my Strings as Linestrings.
Ultimately I want to put the strings in the dimension of an SSAS OLAP Cube, but for now I am trying with a simple Excel sheet and can't get the desired results.
Can anyone help me how to set up my data to show lines on the map?
Share Improve this question edited Feb 18 at 11:47 Sampath 3,5912 gold badges6 silver badges24 bronze badges Recognized by Microsoft Azure Collective asked Feb 17 at 12:50 J.Q. Higgins III. J.Q. Higgins III. 215 bronze badges 1- refer this doc for Add a line layer to the map – Sampath Commented Feb 18 at 3:33
1 Answer
Reset to default 0Well Known Text (WKT) is not currently supported by the Azure Maps visual. If you pass it into the location field, I believe it would be passed into a geocoder and likely to have a low-quality result match with a random location.
There are two ways to show lines on the Azure Maps visual in Power BI. The first is to use the new path layer capability: https://learn.microsoft/en-us/azure/azure-maps/power-bi-visual-add-path-layer This should allow you to do data filtering and coloring in a similar way to other visuals in Power BI. And allow for your data to change over time (e.g. you add new lines to your data source). Here is a good blog on how to use this that would work well for your scenario: https://www.sumproduct/blog/article/power-bi-tips/path-layer-for-azure-maps
The second is to use a reference layer: https://learn.microsoft/en-us/azure/azure-maps/power-bi-visual-add-reference-layer?tabs=upload With this you would upload your line data as a GeoJSON file and a static version of those lines would be stored in your report. This means you would have to manually update that file if you have new lines. You would then need a second data set that has your metrics and some unique identifier that can be used to join your reference layer with your data metrics. This will allow you to do data driven styling, however I believe there will be less options at the moment for this compared to the path layer. This approach may also be slower if you have a large data set of lines as it would always load the full set of line data, whereas the path layer approach would limit the number of lines loaded at any given time (I believe it is 30,000 which is a Power BI visual limit). With reference layers, you can also hardcode styling into the GeoJSON file itself, however, this would also mean more work to update as things change.
Update: Sample walkthrough
I've gone through and put together a simple example here using the path layer method.
The first step is to get your data into a format that works well with Power BI. The path layer prefers to have each coordinate of a path on a separate row in the data set, with a unique ID being assigned to path. With this in mind it makes sense to separate the path geometry information from the metric information. Using Excel as my data source for simplicity, I created two sheets (tables). The first is for the street geometries and has three columns; StreetID, Longitude, Latitude. The second is for the metric data and has columns; StreetID, and Metric. I made up a random metric in this case where the value can be 1, 2, or 3. We could add more columns if we want to have multiple bits of information linked to each path. The reason for using two tables is so that we only have to put the metric in the data once. This also simplifies things in a larger solution where your metrics might change constantly, but the path data is fairly static. I uploaded the Excel file here if you want download it and test it out: https://1drv.ms/x/s!AgFqp4QkIjUNr_EnU19NL6GYn1FEoQ?e=OZQ0Ud The data looks like this:
From here I went into Power BI and created a new report and used this Excel file as my data source (be sure to select both sheets).
I added the Azure Maps visual to the report and in its fields I did the following:
- Latitude: Pass in Latitude field from StreetGeometries data set. Remove summarization.
- Longitude: Pass in Longitude field from StreetGeometries data set. Remove summarization.
- Legend: Pass in the Metric field from the Metrics data set.
- Path ID: Pass in StreetID field from StreetGeometries data set (may also work if from the Metrics data set).
At this point the map looked like this:
As we can see each line is colored based on the metric. However, we also see circles/bubbles at the ends of each line which we likely won't want displayed. To remove it, simply go into the "Format your visual" tab and unselect the Bubble layer. You can also adjust the width of the lines under the Path layer settings.
Update 2: Change line colors
If you want to change the color of the lines you will likely see that the color option in path settings is disabled since the colors are being data driven. The color is actually set in the bubble layer options. This is one of the complexities of trying to create a Power BI visual with a lot of different rendering capabilities. The data driven coloring of layers is designed to be consistent as there is only one legend per visual allowed. So, if you enable the bubble layer, then go to the color options, you can customize the colors. When done, disable the bubble layer again to remove the circles/bubbles from the map. The path colors will be updated.