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