Sometimes you might want to plot data points on a map that are within a specific distance from a dynamically selected location. In our example, we’ll generate a visualisation showing all users that have signed up to our e-commerce platform within a dynamically selected distance from a dynamically selected city.
1) First, you’ll need both latitude and longitude fields. Based on these we’ll be able to create a user location dimension:
dimension: lat {
type: number
sql: ${TABLE}.latitude ;;
}
dimension: lon {
type: number
sql: ${TABLE}.longitude ;;
}
dimension: location {
type: location
sql_latitude: ${lat} ;;
sql_longitude: ${lon} ;;
}
2) Now we need to create a parameter with all the cities we want to calculate users’ distance from:
parameter: city {
type: string
allowed_value: {
label: "Viena"
value: "viena"
}
allowed_value: {
label: "London"
value: "london"
}
allowed_value: {
label: "Milan"
value: "milan"
}
allowed_value: {
label: "Rome"
value: "rome"
}
allowed_value: {
label: "Berlin"
value: "berlin"
}
}
3) Now we need to create a latitude, longitude and location dimensions of all cities defined in our parameter field. Note that we’re using a CASE WHEN
statement to make sure the lat and long assigned to a city location is based on what we select in the city parameter:
dimension: distance_city_lat {
hidden: yes
type: number
sql: CASE WHEN {% parameter city %} = 'viena' THEN 48.864716
WHEN {% parameter city %} = 'london' THEN 51.509865
WHEN {% parameter city %} = 'milan' THEN 45.4642
WHEN {% parameter city %} = 'rome' THEN 40.4168
WHEN {% parameter city %} = 'berlin' THEN 52.5200
ELSE 48.864716 END ;;
}
dimension: distance_city_long {
hidden: yes
type: number
sql: CASE WHEN {% parameter city %} = 'viena' THEN 2.349014
WHEN {% parameter city %} = 'london' THEN -0.118092
WHEN {% parameter city %} = 'milan' THEN 9.1900
WHEN {% parameter city %} = 'rome' THEN -3.703790
WHEN {% parameter city %} = 'berlin' THEN 13.404954
ELSE 2.349014 END ;;
}
dimension: location_of_selected_city {
hidden: yes
type: location
sql_latitude: ${distance_city_lat} ;;
sql_longitude: ${distance_city_long} ;;
}
4) Finally, we can just create a distance dimension to calculate the distance between user location and selected city in our preferred unit:
dimension: distance_between_selected_city_and_user {
label: "Distance in km"
type: distance
start_location_field: location
end_location_field: location_of_selected_city
units: kilometers
}
Now you can show data where the actual user location is less then the filtered distance from the specified city.
Below example shows all travel agencies around the selected city
This is cool example, but how do we make this scaleable? I’d like to have a drop down with 5,000 cities.
If you’ve got 5000 options, I think a non-dropdown filter would be a better choice. That way users could start typing and get suggestions. You could do pretty much exactly the same thing, just change the parameter block to
filter: city {
type: string
suggest_dimension: whatever dimension has your city data
}
And you’d get a text entry filter.
I think if I have 5000 cities, I wont be able to create parameter and if my filter city dimension is present in another table and not the main table… How can I still do the same thing as above example?
Check out the suggest_dimension field, that might let you add a filter but reference the filter city dimension!
How would you get the specific location for the city from another dimension?
The parameter would return a string for city name. How to pass a single value for that city’s gps location to the distance city log CASE WHEN statement?
I have a hard time making a fixed dimension based on the City Selection for the entire table ignoring GROUP BY statement. For each row regardless of the columns and rows I would need a column with the GPS location for that one selected City.
Is there a way to do this without hardcoding the gps coordinates of the selected city into distance_city_lat and distance_city_long dimensions? I have a huge list of cities and it is not practical to use thousands of CASE WHEN statements to find distances between cities.