Splunk Search
Highlighted

Lookup based range of latitude and longitude

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 wlng = longitude - (500 / 30.8184*0.000277778) | eval wlat = latitude - (500 / 25.24500.000277778) | eval e_lng = longitude + (500 / 30.81840.000277778) | eval elat = latitude + (500 / 25.2450*0.000277778) | table shopname address longitude latitude wlng wlat elng elat | eval search = "(longitude >= " . wlng ." AND latitude >= " . wlat . ") AND (longitude <= " . elng ." AND latitude <= " . elat . ")" | fields search]

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

Any sample search would be really appreciated..

0 Karma
Highlighted

Re: Lookup based range of latitude and longitude

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
Highlighted

Re: Lookup based range of latitude and longitude

Explorer

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

0 Karma