Calculating distance from a dynamically selected location

done
low_priority
reply

(aleks) #1

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


(Josh) #2

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


(Izzy) #3

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.


#4

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?


(Izzy) #5

Check out the suggest_dimension field, that might let you add a filter but reference the filter city dimension!