Kyle challenged us this week with another take on zooming into maps, but this time not using spatial parameters.

Modelling the data

There are 4 sheets of data within the provided Excel workbook. The data needs to be related as follows:

• start with free_bike_status
• add vehicle_types and relate to free_bike_status on the Vehicle Type Id field
• add station_info and relate to free_bike_status using a calculated field where1 =1
• add station_status and relate to station_info on the Station Id field

Building the Core Map

We will be using map layers and spatial functions throughout this challenge. We start by defining the location of each bike and each station

Bike Location

MAKEPOINT([Lat],[Lon])

Station Location

MAKEPOINT([Lat (Station!Info)],[Lon (Station!Info)])

For the bikes, we need to know how much charge it has left

Charge %

SUM([Current Range Meters]) / SUM([Max Range Meters])

format this to % with 0 dp

Add Bike Location to a new sheet. Add Bike Id to Detail and change the mark type to circle. Add Charge % to Colour, and adjust the colour palette as required, and also edit so it is fixed to range from 0 to 1 (ie 0-100%).

Adjust the opacity of the colour to around 70% and add a pale grey border around the circles. Click on the 1 null indicator to the bottom left and select filter data to exclude that record from the display.

Select Map > Map Options from the menu and uncheck all the values to prevent the map from bing manually zoomed in/ changed. Then select Map >Background Layers from the menu, and set the Style to dark and click the Streets,Highways etc map option.

Drag Station Location onto the canvas and drop when the Add a Marks Layer option appears. Add Name the Detail shelf and Num Bikes Available to Colour. Change the mark type to square and adjust the colour palette as required and fix to range from 0 to 50.

Adjust the opacity of the colour to around 70% and add a pale grey border around the circles. Then move the stations marks card so it is listed below the bikes marks card. This means the bikes are displayed ‘on top’

Identifying the selected bike

Create 3 parameters

pSelectedBike

string parameter defaulted to <empty string>

pLat

float parameter, defaulted to 38.9358 (this is the central point mentioned in the requirements)

pLon

float parameter defaulted to -77.1069 (this is the central point mentioned in the requirements)

Note – originally I planned to just capture the ID of the selected bike then determine the lat & lon of that bike using a FIXED LOD to in turn determine the selected bike’s location, but that really hampered the performance, so I just used the parameter action to capture the required Lat & Lon directly

Show the 3 parameters on the sheet.

Update the 3 entries with a Bike Id and its associated Lat & Lon values (eg Bike Id =
8ec444bc696c2c8837ca0dcad39de819 , Lat = 38.8965 , Lon = -77.0334)

We need to identify the selected bike on the map

Is Selected Bike

[Bike Id]=[pSelectedBike]

Add this to the Size shelf on the bikes marks card. Adjust the sizes so True is listed before False and the sizes are therefore reversed. You may need to adjust the slider on the Size shelf too.

Zooming in to the selected bike

Create a new field

Selected Bike Location

MAKEPOINT([pLat],[pLon])

then create a buffer of 2000m around this (the requirements state 1000m, but I found that there were free bikes that were over 1000m from their nearest station, and if they were clicked on in the grid, the map didn’t display).

Selected Bike Buffer

BUFFER([Selected Bike Location],2000,’m’)

We want the map to ‘zoom’ into this buffer area if a bike has been selected, but show all bikes & stations so we need

Within 2000m

([pSelectedBike]=”) OR ((INTERSECTS([Bike Location],[Selected Bike Buffer])) AND (INTERSECTS([Station Location],[Selected Bike Buffer])))

Add this to the Filter shelf and select True

The map should zoom in, and the bike selected should be quite central to the display (the middle point of the buffer). To verify this, create

Buffer for Zoom

IF [pSelectedBike] <> ”
THEN [Selected Bike Buffer]
END

Add this to the map as another marks layer, and the circular buffer ‘zone’ will be displayed (we’ll keep this here for now for validation purposes).

Reset the pSelectedBike to <empty> and set pLat and pLon back to their default values – the buffer circle disappears.

Kyle hinted that we need to make sure that on ‘zooming out’ the display should be centred on the default values. To ensure this, we want to create a buffer around that central point that encapsulates all the stations and bikes. So we need

Default Location

MAKEPOINT(38.9358,-77.1069)

Default Buffer

BUFFER([Default Location],30,’km’)

Choosing a 30km buffer was just trial and error.

Now update the Buffer for Zoom field to

IF [pSelectedBike] = ” // then we’re in the default ‘show all’ view
THEN [Default Buffer]
ELSE [Selected Bike Buffer]
END

A buffer zone for the whole display is now shown

Ensure the buffer marks card is displayed at the bottom, reduce the opacity of the colour to 0 and remove any border to make the circle disappear. Then click on the eye symbol to the left of the marks card name to make the map layer disabled, so it doesn’t show up on hover.

Finally adjust the Tooltips on the relevant marks cards and then name the sheet Map or similar.

Building the Bike Selector Grid

To build this we will need to identify the closest station to each bike. First we need the distance between each bike and each station

Distance Bike to Station

DISTANCE([Bike Location], [Station Location],’m’)

and then we can create

Distance Bike to Closest Station

{FIXED [Bike Id]:MIN([Distance Bike to Station])}

On a new sheet add Bike Id to Detail and Distance Bike to Closet Station to Colour. Change the mark type to square. Sort the Bike Id by the field Distance Bike to Closet Station ascending.

Add Lat and Lon to the Detail shelf, and update the Tooltip as required. Name the sheet Bike Grid or similar.

Adding the interactivity

Add the two sheets onto a dahsboard, then create 3 dashboard parameter actions

Select Bike

On select of the Bike Grid sheet, set the pSelectedBike parameter with the value from the Bike Id field. When the selection is cleared, reset to <empty string>

Set Bike Lat

On select of the Bike Grid sheet, set the pLat parameter with the value from the Lat field. When the selection is cleared, reset to 38.9358

Set Bike Lon

On select of the Bike Grid sheet, set the pLon parameter with the value from the Lon field. When the selection is cleared, reset to -77.1069

And with that, hopefully the map should zoom in and out as required, albeit a bit slowly… (gif below recorded on Desktop)

My published viz is here.

Happy vizzin’!

Donna