Calculating distance from a dynamically selected location

[aleks1]

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