topic Re: Calculating distances between points with GEOIP using latitude and longitude, can I use Trigonometric functions directly in a search in Splunk 6.1.5 in Splunk Search

Calculating distances between points with GEOIP using latitude and longitude, can I use Trigonometric functions directly in a search in Splunk 6.1.5

gonzalovasquez — Tue, 13 Oct 2015 13:05:39 GMT

I need tocalculate distances between points with GEOIP using latitude and longitude directly in a search with trigonometric functions, Can I use these functions in Splunk 6.1.5?

Example:

| eval cal_a = (sen_lati * sen_lati) + cos(GEO_1_LAT_NUM) * cos(GEO_2_LAT_NUM) * (sen_long* sen_long)
| eval cal_c = 2 * (atan2(sqrt(cal_a),sqrt(1-(cal_a)))) 
| eval distancia = round(cal_c * r_tierra)

Greetings!

Re: Calculating distances between points with GEOIP using latitude and longitude, can I use Trigonometric functions directly in a search in Splunk 6.1.5

somesoni2 — Tue, 13 Oct 2015 16:06:02 GMT

You can use following app to calculate distance between two points

https://splunkbase.splunk.com/app/936/#/documentation

For an alternate solution, see the answers from @Sideview from this post
https://answers.splunk.com/answers/90694/find-the-distance-between-two-or-more-geolocation-coordinates.html

Re: Calculating distances between points with GEOIP using latitude and longitude, can I use Trigonometric functions directly in a search in Splunk 6.1.5

MuS — Mon, 04 Sep 2017 21:14:48 GMT

Hi there,

fast forward into the future, we can do the great circle formula in Splunk now.
This example will provide the expected result:

| makeresults 
| eval lat1=1, lon1=1, lat2=2, lon2=2 
| eval rlat1 = pi()*lat1/180, rlat2=pi()*lat2/180, rlat = pi()*(lat2-lat1)/180, rlon= pi()*(lon2-lon1)/180
| eval a = sin(rlat/2) * sin(rlat/2) + cos(rlat1) * cos(rlat2) * sin(rlon/2) * sin(rlon/2) 
| eval c = 2 * atan2(sqrt(a), sqrt(1-a)) 
| eval distance = 6371 * c
| table lat1 lon1 lat2 lon2 distance

distance will be the distance in km.

Hope this helps ...

cheers, MuS

Re: Calculating distances between points with GEOIP using latitude and longitude, can I use Trigonometric functions directly in a search in Splunk 6.1.5

xpac — Wed, 09 May 2018 20:38:53 GMT

Thanks a lot for this, it worked fine for me!

Re: Calculating distances between points with GEOIP using latitude and longitude, can I use Trigonometric functions directly in a search in Splunk 6.1.5

jspears — Fri, 28 Jun 2019 20:37:01 GMT

Here's a handy macro I just created base on this answer. I used the multiplier for miles instead of km. Define as haversine(4), takes args lat1,lon1,lat2,lon2 and creates a "miles" field.

eval rlat1 = pi()*$lat1$/180, rlat2=pi()*$lat2$/180, rlat = pi()*($lat2$-$lat1$)/180, rlon = pi()*($lon2$-$lon1$)/180 
| eval a = sin(rlat/2) * sin(rlat/2) + cos(rlat1) * cos(rlat2) * sin(rlon/2) * sin(rlon/2) 
| eval c = 2 * atan2(sqrt(a), sqrt(1-a)) 
| eval miles = 3963 * c

Here it is included in the use case that brought me here.

sourcetype=oktaim2:log
| streamstats global=f window=2 current=t earliest(client.geographicalContext.geolocation.lon) AS lon1 latest(client.geographicalContext.geolocation.lon) AS lon2 earliest(client.geographicalContext.geolocation.lat) AS lat1 latest(client.geographicalContext.geolocation.lat) AS lat2 earliest(client.geographicalContext.city) AS src_city latest(client.geographicalContext.city) AS dest_city earliest(client.geographicalContext.state) AS src_state latest(client.geographicalContext.state) AS dest_state earliest(_time) AS departed_time latest(_time) AS arrived_time BY user 
| where lat1!=lat2 AND lon1!=lon2 
| `haversine(lat1,lon1,lat2,lon2)` 
| eval hours=(arrived_time-departed_time)/60/60 
| eval avg_mph=miles/hours 
| where avg_mph>500 AND miles>100 
| eval src_locale=src_city . ", " . src_state, dest_locale=dest_city . ", " . dest_state 
| table _time lat1 lon1 lat2 lon2 src_locale dest_locale miles hours avg_mph user 
| sort _time desc