Splunk Search

Lookup based range of latitude and longitude

Shisa
Explorer

Hi
I'm looking for a sample search that calculates count of events which match within 500m radius of lat/long on lookup table.

Sample events:
2017/02/02 10:00:01 event_id="1" latitude="34.49293" longitude="132.399270"

Lookup sample "MASTER" for location (CSV):
shop,address,latitude,longitude
AAA,563 2nd St,34.492109,132.399582
BBB,201 3rd St,34.395424,132.488734

Expected output table:
shop,address,latitude,longitude,event match count
AAA,563 2nd St,34.492109,132.399582,1
BBB,201 3rd St,34.395424,132.488734,0

I tried and could create the following search that find events within 500m radius of lat/long on lookup table.

sourcetype=hoge [| inputlookup MASTER.csv | eval w_lng = longitude - (500 / 30.8184*0.000277778) | eval w_lat = latitude - (500 / 25.2450*0.000277778) | eval e_lng = longitude + (500 / 30.8184*0.000277778) | eval e_lat = latitude + (500 / 25.2450*0.000277778) | table shop_name address longitude latitude w_lng w_lat e_lng e_lat | eval search = "(longitude >= " . w_lng ." AND latitude >= " . w_lat . ") AND (longitude <= " . e_lng ." AND latitude <= " . e_lat . ")" | fields search]

But I'm not sure how to create the expected output table.

Any sample search would be really appreciated..

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

How dense is the data? That is, are the events likely to be within 500 miles of a high percentage of the locations, or just a few of them?

How many locations are on your lookup table? Presumably, the list of locations is much smaller than the list of event locations.

So, one possible solution would be to think in terms of "regions". Let's say that your latitude and longitude for the event, for lookup purposes, will be rounded to the nearest 1 degree, or 3 degree, or 5 degree point.

At the 5-degree level, latitude="34.49293" and longitude="132.399270" would go to lookupLatLong="35,130"

Now, on your lookup location table, you create a multivalue field for each location that has all the lookupLatLong values that might have a point within 500 miles of the location.

This way, a single lookup returns all candidate locations that MIGHT be within 500 miles. Then you use an accurate calculation to estimate the distance more closely, for example the spherical law of cosines -

=ACOS( SIN(lat1)*SIN(lat2) + COS(lat1)*COS(lat2)*COS(lon2-lon1) ) * 3962

0 Karma

Shisa
Explorer

Sorry for misleading sentence. I mean that 500m is 500 meters (0.3 miles)

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...