Knowledge Management

## Closest GPS match in lookup table

Explorer

I have a list of GPS points in a lookup file which describes a race track, generated using this https://www.gpsvisualizer.com. This lookup file has maybe 100 points which describe the path of this track.

latitiude,longitude,Segment name

I then have some GPS traces of my car driving round this race track, where a GPS point is recorded roughly every second.

For each recorded car location point, I want to look in this lookup file and return the Segment name which is physically closest.

It's not quite a substring/pattern match (i.e. match_type WILDCARD), I would like to be able to return the segment name for the closest point i have in the lookup file.

I'm aware of the haversine app:
https://splunkbase.splunk.com/app/936/

And this is someone bundling the algorithm into a macro:

https://community.splunk.com/t5/Splunk-Search/Distance-between-two-Geocoordinates/td-p/422514

But I'm not sure how I could use that macro or the haversine function in a lookup ?

So far i'm just doing literal matches of the lat/long values in the car traces and I'm getting no matches at all (very disappointing)

Any suggestions/ideas ?

Labels (1)
• ### lookup

Tags (3)
Motivator

I've used various approaches. Very simple method is to store the GPS coords at a rounding level that will ensure you will always get at least on result. Given that rounding to 0 decimal places is approx distance of 111KM, and

1DP=11.1KM
2DP=1.1KM
3DP=111m
4DP=11.1m
5DP=1.1m

you could do lookups on which rounding approach is most appropriate and returning all hits, essentially acting as a primary filter, and then run the haversine formula on the results.

You would need to mvexpand the returned lat/long coords from the lookup and do the haversine calculation on those and then do stats min(distance) to get the result. You can't embed the haversine function as part of the lookup itself, so just use it on the results.

Explorer

I'm still wresting with this one, and I'm trying to use wildcard matching against my lookup data.

Say I have a GPS Point 51.234567,0.123456
and I then round that to 1 point, 2 points, 3 points, etc up to 5, i get these.

``````51.2,0.1
51.23,0.12
51.234,0.123
51.2345,0.1234
51.12345,0.12345``````

I can then try and lookup any of those values in my reference table with match_type=WILDCARD(latitude),WILDCARD(longitude)

I should get a match (as I have rows/GPS points in the lookup table which start with 51.2,0.1), but I don't get any matches.

I've also considered the geospatial approach, but I can't work out how to generate a KML which splunk will interpret. There are examples (US_States, countries, etc), but I don't know which tools i could use to create my own.

Can anyone recommend any tools that will generate geospatial lookup (kml/kmz) files that splunk will read ?

Motivator

I created the following lookup

``````| makeresults
| eval _raw="latitude,longitude,
51.2,0.1
51.23,0.12
51.234,0.123
51.2345,0.1234
51.12345,0.12345"
| eval row=1
| accum row
| table row,latitude,longitude
| outputlookup lat_test.csv``````

The following search picks up row 1

``````| makeresults
| eval latitude=51.2, longitude=0.1
| lookup lat_test.csv latitude longitude``````

There is no wildcard needed as this is an exact lookup

When using wildcards in lookups, the data in the lookup needs to have the * character as part of the data, so you would have

row,latitude,longitude
1,51.2*,0.1*

and not need the others.

As for KML generation, I have used one online tool, but forget what it was, but just google online kml ploygon creator, and you'll get a bunch of hits e.g.

https://www.doogal.co.uk/polylines.php

Explorer

I have a series of points in my lookup data, loosely tracing a racetrack.

I have a series of points in my data, the recording of the location of the car.

There are sadly very few (if any) exact matches for the cars locations in the lookup data.

So i need a way of looking for the closest match in the lookup table from the car location data.

I was approaching it by trying to round the vehicles location lat/lon, and then try to establish the substring matches in the lookup data, I couldn't get wildcard matching to work (at all, i've not used it before, I haven't experimented with inserting "*" characters at the end of the rounded vehicle location data.

I also thought the geolocation approach might be useful, the basic idea being it's possible to determine the country/US state based on the perimeter defined in the provided kml files from a GPS point.

I have a KML file of the same basic approach, a permimeter around each corner/straight on the track and I would like to establish which "perimeter" (or named track segment) is in for each vehicle GPS point.

I'll have a look at the tool you've linked.

Tags (1)
Explorer

I now have a kml file configured as a geospatial lookup. the track is split into 10 zones, each zone is defined by a polygon perimeter and is distinctly named.

This look okay in

``| inputlookup <lookup name>``

I don't know how I get responses from this lookup, I have latitude and longitude in my event data and I am using
| lookup <lookup name> latitude,longitude (as per the example here https://docs.splunk.com/Documentation/Splunk/8.0.4/Knowledge/Configuregeospatiallookups#Geospatial_l...) and I'm getting

`` "Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table."``

It strikes me that I don't have latitude,longitude fields in my geospatial lookup data, I see count, featureCollection, featureId and geom fields (which doesn't look like the co-ordinates list in the actual kml file, but at least appears to be valid in splunk terms).

How do these geospatial lookups determine if a point is within a polygon ?

Motivator

I believe you already have the latitude/longitude, so what you are after is giving those to the geom command which will do the geo calcs to determine what the produce for a choropleth map.

As for manually finding pairs of coordinates within polygons, I don't know the maths to do that ๐