Question

Calculating distance from a dynamically selected location

  • 2 February 2018
  • 6 replies
  • 632 views

Userlevel 2

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.



How to do it?


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



6 replies

This is cool example, but how do we make this scaleable? I’d like to have a drop down with 5,000 cities.

Userlevel 7
Badge +1

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?

Userlevel 7
Badge +1

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. 

Reply