Lookup based range of latitude and longitude

[Shisa]

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..

[DalJeanis]

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

[Shisa]

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