Calculating distance from a dynamically selected location

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

3 6 2,387
6 REPLIES 6

jvogel
Participant I

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.

DeeV
Participant I

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. 

Top Labels in this Space
Top Solution Authors