I have a table containing entity locations across the United States and I have a location filter that will allow me to find any entity within X miles from an a given point.
I was hoping to create calculated fields which contain the distance and bearing from the location entered into the filter.
Is this possible?
Hey
I hope all is well! I see what you mean. The goal here is the grab the explicit filter entry values. The option to grab these filter values in a table calculation is currently unavailable; however, I was sure to relay this to the product team on my end.
To reference values entered with a front end filter, we could use templated filters or parameters, both of which are LookML elements. Templated filters will translate the filter entries into logic while parameters take the values directly and does not insert logic. Hope this helps!
Cheers,
Leticia
We used the spherical law of cosines using the Lat and Lon coordinates which worked well for our use case (modelling radio broadcast reach)
Distance = acos(sin(Lat)*sin(radians(Lat1)) + cos(Lat)*cos(radians(Lat))*cos(radians(Lon1)-Lon)) * SizeOfTheEarth.
We then did a lookup table with the Lat/Lon of each city’s centre to figure out whether to include or not.
https://www.simplybusiness.co.uk/about-us/tech/2015/09/radio-advertising-campaign-analytics/
Hey
that is an interesting use case!
It seems a common approach to this issue is the Haversine formula to get the distance between two GPS coordinates following this formula:
dlon = lon2 - lon1
dlat = lat2 - lat1
a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2( sqrt(a), sqrt(1-a) )
d = R * c (where R is the radius of the Earth)
So I started looking at implementing it with table calcs which gets us close, however we do not have the atan2() function in Looker, but only atan() so we are’t able to compute the formula.
However this is possible with SQL, the example below shows an implementation on MySQL (which has the atan2() function:
dimension: dlon {
type: number
sql: ${lon2} - ${lon1} ;;
}
dimension: dlat {
type: number
sql: ${lat2} - ${lat1} ;;
}
measure: a {
type: number
sql: power(sin(${dlat}/2),2) + cos(${lat1}) * cos(${lat2}) * power(sin(${dlon}/2),2);;
}
measure: c {
type: number
sql: 2 * atan2( sqrt(${a}), sqrt(1-${a}) ) ;;
}
measure: d {
type: number
sql: 6373 * ${c};; # radius of the Earth 3961 miles or 6373 km
}
I hope this helps. I am also letting our product team know about your use case here so they know having the atan2() function would be helping.
If that was not the formula you wanted to apply please feel free to let us know so we can help out!
Thanks!
Hello Romain,
Thank you for your very thorough response, however my question was less about calculating distance and more regarding how to use the value entered into the filter to create a calculated field.
For instance, if I have the values
a) 1, ${table_lat1}, ${table_long1}
a) 2, ${table_lat2}, ${table_long2}
a) 3, ${table_lat3}, ${table_long3}
And someone enters in the filter: “10 miles from 36.97, -122.03”
How can I use the 36.97, -122.03 to return:
a) 1, ${table_lat1}, ${table_long1}, distance 5 miles, bearing 100 degrees
a) 2, ${table_lat2}, ${table_long2}, distance 1 miles, bearing 24 degrees
a) 3, ${table_lat3}, ${table_long3}, distance 7 miles, bearing 312 degrees
I hope this adds some clarity.
Regards,
Josh
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.