Deneb/Vega-Lite can be used to generate map visuals with a variety of projections. The example presented herein uses a simple calculator of the time difference between 2 world cities with a map background, a latitude/longitude grid, and an arrow and label displaying the difference.
This was my first experience with mapping in Deneb/Vega-Lite, and I used the “Turkey Ring of Fire” example from last year by Isin Koseman as a learning source.
I’d like to thank Isin as well for the help in determining the method and syntax of adding graticules (grid of latitude and longitude) to a projection.
(NOTE: this was a personal learning exercise and capability exploration rather than the creation of a useful end-product, as there are already many online resources to more properly calculate time difference intervals between locations. Also, this example uses the provided data sources without validation, and the inclusion of daylight saving time in the time difference calculation is left to further development.)
Two standard Power BI slicers are used to select the “from” and “to” locations.
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block for the composite visual
- array used for subtitle to display on multiple lines
- a shared “params” block to enhance the visual with:
- 7x named parameters for setting all visual colours (map fill and stroke, city fill and stroke, arrow colour [behind and ahead])
- a “vconcat” block to vertically concatenate the arrow and map visuals
1 - Arrow:
- the Power BI dataset of the 2 selected cities (including latitude, longitude, and UTC offset)
- a shared “transform” block with:
- 3x “calculate” transforms to standardize the naming of the city, longitude, and UTC offset for the 1st city
- 3x “window/lag” transforms to determine the values of city, longitude, and UTC offset for the 2nd city
- a “filter” transform to restrict the dataset to 1 record
- a nested “layer” block for the arrow body, arrow head, and time difference label:
Arrow:
- a “rule” mark using conditional colours (behind = medium blue, ahead = dark blue) using the arrow colour parameters
Arrowhead:
- a “point” mark using conditional shape (behind = triangle-left, ahead = triangle-right) and conditional colour (matches the “arrow” above)
Time Difference Label:
- a “text” mark using 3x nested “calculate” transforms to determine the longitude midpoint, difference, and label
2 - Map:
- a nested “layer” block for the graticules, map, city (point and label)
Graticules:
- a graticule dataset generated internally and automatically by Vega-Lite with major steps (longitude 30) and minor steps (latitude/longitude 10)
- a “geoshape” mark with dashed stroke
Map:
- a hard-coded dataset obtained from the “world-110m.json” sample Vega-Lite dataset
- a “equirectanglar” projection
- a “filter” transform to remove Antarctica from the dataset
- a “geoshape” mark using the fill and stroke colour parameters
City:
- a nested “transform” block with:
- 2x “calculate” transforms to determine the N/S latitude and E/W longitude of the signed dataset values
- a “calculate” transform to determine the UTC offset for display (always signed and including 1 decimal place for 1/2 hour offsets [e.g., St. John’s, Newfoundland, Canada])
- a “calculate” transform to determine the 5-line city label (array of city, country, latitude N/S, longitude E/W, and UTC offset)
- the Power BI dataset of the 2 selected cities (including latitude, longitude, and UTC offset)
- a “point” mark using the fill and stroke colour parameters
- a “text” mark using the city label calculated above
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": 20,
"text": "Power BI Time Difference Calculator in Deneb",
"font": "Verdana",
"fontSize": 24,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": [
"Map Data: Vega-Lite dataset (world-110m.json) (Antarctica hidden)",
"Latitude/Longitude Grid: graticule data generated internally by Vega-Lite",
"City Data: GitHub SQL database (/fulvio999/world_city_location_times/tree/master)",
"[10,600 available; filtered to 120 for illustration purposes]"
],
"subtitleFont": "Verdana",
"subtitleFontSize": 16,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"params": [
{
"name": "_colour_map_fill",
"value": "#E3E3E3"
},
{
"name": "_colour_map_stroke",
"value": "#B0B0B0"
},
{
"name": "_colour_city_fill1",
"value": "#28C3B6"
},
{
"name": "_colour_city_fill2",
"value": "#FF4500"
},
{
"name": "_colour_city_stroke",
"value": "#969696"
},
{
"name": "_colour_arrow1",
"value": "#0A017E"
},
{
"name": "_colour_arrow2",
"value": "#5E7B9C"
}
],
"spacing": 1,
"vconcat": [
{
"name": "ARROW",
"width": 1200,
"height": 20,
"data": {
"name": "dataset"
},
"transform": [
{
"calculate": "datum['longitude']",
"as": "_longitude1"
},
{
"calculate": "datum['utcoffset']",
"as": "_utcoffset1"
},
{
"calculate": "datum['city']",
"as": "_city1"
},
{
"window": [
{
"op": "lag",
"field": "longitude",
"as": "_longitude2"
}
],
"sort": [
{
"field": "City Number",
"order": "descending"
}
]
},
{
"window": [
{
"op": "lag",
"field": "utcoffset",
"as": "_utcoffset2"
}
],
"sort": [
{
"field": "City Number",
"order": "descending"
}
]
},
{
"window": [
{
"op": "lag",
"field": "city",
"as": "_city2"
}
],
"sort": [
{
"field": "City Number",
"order": "descending"
}
]
},
{
"filter": "datum['City Number'] == 1"
}
],
"layer": [
{
"name": "TIME_DIFFERENCE_ARROW_BODY",
"mark": {
"type": "rule",
"strokeWidth": 4,
"color": {
"expr": "datum['_utcoffset1'] < datum['_utcoffset2'] ? _colour_arrow1 : _colour_arrow2"
}
},
"encoding": {
"y": {
"axis": null,
"datum": 0
},
"x": {
"field": "_longitude1",
"type": "quantitative",
"axis": null,
"scale": {
"domain": [
-180,
180
]
}
},
"x2": {
"field": "_longitude2"
}
}
},
{
"name": "TIME_DIFFERENCE_ARROW_HEAD",
"transform": [
{
"calculate": "if( datum['_utcoffset2'] > datum['_utcoffset1'], 'triangle-right', 'triangle-left' )",
"as": "_arrowhead_shape"
},
{
"calculate": "datum['_longitude2']",
"as": "_arrowhead_longitude"
}
],
"mark": {
"type": "point",
"shape": {
"expr": "datum['_arrowhead_shape']"
},
"filled": true,
"opacity": 1,
"size": 200,
"color": {
"expr": "datum['_utcoffset1'] < datum['_utcoffset2'] ? _colour_arrow1 : _colour_arrow2"
}
},
"encoding": {
"y": {
"axis": null,
"datum": 0
},
"x": {
"field": "_arrowhead_longitude",
"type": "quantitative",
"axis": null,
"scale": {
"domain": [
-180,
180
]
}
}
}
},
{
"name": "TIME_DIFFERENCE_TEXT",
"transform": [
{
"calculate": "( datum['_longitude1'] + datum['_longitude2'] ) / 2",
"as": "_longitude_midpoint"
},
{
"calculate": "datum['_utcoffset1'] < 0 && datum['_utcoffset2'] < 0 ? abs( datum['_utcoffset2'] - datum['_utcoffset1'] ) : datum['_utcoffset1'] < 0 && datum['_utcoffset2'] > 0 ? ( -1 * datum['_utcoffset1'] ) + datum['_utcoffset2'] : datum['_utcoffset1'] > 0 && datum['_utcoffset2'] > 0 ? datum['_utcoffset2'] - datum['_utcoffset1'] : datum['_utcoffset1'] > 0 && datum['_utcoffset2'] < 0 ? datum['_utcoffset2'] - datum['_utcoffset1'] : datum['_utcoffset1'] == 0 ? abs( datum['_utcoffset2'] ) : datum['_utcoffset2'] == 0 ? abs( datum['_utcoffset1'] ) : 0",
"as": "_difference"
},
{
"calculate": "datum['_city2'] + ' is ' + if( datum['_difference'] == floor( datum['_difference'] ), format( abs( datum['_difference'] ), '.0f' ), format( abs( datum['_difference'] ), '.1f' ) ) + ' hours ' + if( datum['_utcoffset2'] > datum['_utcoffset1'], 'ahead of ', 'behind ' ) + datum['_city1']",
"as": "_difference_label"
}
],
"mark": {
"type": "text",
"align": "center",
"dy": -20,
"font": "Segoe UI",
"fontSize": 20,
"fontWeight": "bold",
"fontStyle": "italic",
"color": "black"
},
"encoding": {
"y": {
"axis": null,
"datum": 0
},
"x": {
"field": "_longitude_midpoint",
"type": "quantitative",
"axis": null,
"scale": {
"domain": [
-180,
180
]
}
},
"text": {
"field": "_difference_label"
}
}
}
]
},
{
"name": "MAP",
"width": 1200,
"height": 600,
"layer": [
{
"name": "GRATICULES",
"data": {
"graticule": {
"stepMajor": [
30,
60
],
"stepMinor": [
10,
10
]
}
},
"mark": {
"type": "geoshape",
"stroke": "#969696",
"strokeWidth": 1,
"strokeDash": [
4,
2
]
}
},
{
"name": "WORLD_MAP",
"data": {
"values": {...
// deleted for space reasons; see PBIX for details
},
"format": {
"type": "topojson",
"feature": "countries"
}
},
"projection": {
"type": "equirectangular"
},
"transform": [
{
"filter": "datum['id'] != 10"
}
],
"mark": {
"type": "geoshape",
"fill": {
"expr": "_colour_map_fill"
},
"stroke": {
"expr": "_colour_map_stroke"
}
}
},
{
"data": {
"name": "dataset"
},
"transform": [
{
"calculate": "format( abs( datum['latitude'] ), '.2f' ) + if( datum['latitude'] < 0, ' S', ' N')",
"as": "_latitude_ns"
},
{
"calculate": "format( abs( datum['longitude'] ), '.2f' ) + if( datum['longitude'] < 0, ' E', ' W')",
"as": "_longitude_ew"
},
{
"calculate": "'UTC offset: ' + if( datum['utcoffset'] == floor( datum['utcoffset'] ), format( datum['utcoffset'], '+.0f' ), format( datum['utcoffset'], '+.1f' ) )",
"as": "_utcoffset_label"
},
{
"calculate": "[datum['city'], datum['country'], datum['_latitude_ns'], datum['_longitude_ew'], datum['_utcoffset_label']]",
"as": "_city_label"
}
],
"layer": [
{
"name": "CITY_POINT",
"mark": {
"type": "point",
"filled": true,
"size": 200,
"stroke": {
"expr": "_colour_city_stroke"
},
"fill": {
"expr": "datum['City Number'] == 1 ? _colour_city_fill1 : _colour_city_fill2"
}
},
"encoding": {
"latitude": {
"field": "latitude",
"type": "quantitative"
},
"longitude": {
"field": "longitude",
"type": "quantitative"
}
}
},
{
"name": "CITY_TEXT",
"mark": {
"type": "text",
"align": "left",
"dy": 20,
"font": "Segoe UI",
"fontSize": 12,
"fontWeight": "bold",
"fontStyle": "italic",
"color": "black"
},
"encoding": {
"latitude": {
"field": "latitude",
"type": "quantitative"
},
"longitude": {
"field": "longitude",
"type": "quantitative"
},
"text": {
"field": "_city_label",
"type": "nominal"
}
}
}
]
}
]
}
]
}
The intent of this example is not to provide a finished visual, but rather to explore the use of the Deneb custom visual and the Vega-Lite language within Power BI and to serve as a starting point for further development.
Also included is the development PBIX using sample city data from the SQL script [world_country_city_latitude_longitude.sql] found on GitHub (at https://github.com//fulvio999/world_city_location_times/tree/master); this script produced a SQL database consisting of 10,600 records, which were exported to Excel and filtered to 120 records for illustration purposes.
This example is provided as-is for information purposes only, and its use is solely at the discretion of the end user; no responsibility is assumed by the author.
Greg
Deneb Example - Mapping - Time Difference Calculator - V5.pbix (2.7 MB)