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 Engager

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!

Tags (4)  SplunkTrust

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 Communicator

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
``````  SplunkTrust

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

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 