grafana - Number of events within a geo-radius

Question

I have a huge list of events which are only denoted by their time, longitude and latitude. I already managed to see the results in grafana, depicting each event as a single dot in the "Worldmap Plugin" in grafana.

For the current geographic corridor, I'd like to evenly arrange some points, eg. 0.25km apart. Each event, whenever it happened, which is in the range of 5km should be counted and this measurement shown for this specific point.

I've already read about geo-distancing, as the distance are not too big the shape of the earth is not relevant here and this should suffice (python3 code):

lat = (lat1 + lat2) / 2 * 0.01745
dx = 111.3 * math.cos(lat) * (lon1 - lon2)
dy = 111.3 * (lat1 - lat2)
distance = math.sqrt(dx * dx + dy * dy)

This would result in n*(n-1)/2 calculations, which could easily break the time I would like to invest. So it could be ok for me for the first step to only use a "corridor" which is 5km wide, although the distance especially in the corners would be far to big.

Should I create a new database (e.g. sqlite) with all those new "points"? Can this be achieved within influx or grafana? I'm unsure what's the best approach here.

Is there an elegant method (e.g. in python3) to "load" all those points and make the calculations within memory? Querying the influxdb every time sounds like not the right thing to do here.

标签： grafanainfluxdb

Answer 1

After giving it some more thought, I finally came up with a solution that "somehow works". I'm not too satisfied with it as it still takes a lot of time and heavily depends on not too many events in the database.

The events are loaded into memory, therefore accessing the database only once. The distance calculation is done for every point to any point, whether it might be within range or not, this will need some more tweeking, but I'll manage that.

If any of you have another thought on how to improve this, please inform me here.

#!/usr/bin/python3
import math
import sqlite3
from influxdb import InfluxDBClient
# Connection to the InfluxDB
client = InfluxDBClient(host='localhost', port=8086)
client.get_list_database()
client.switch_database('geopoints')
# Which geo corridor should be searched
startinglat = 5.4
stoplat = 5.7
startinglon = 33.2
stoplon = 33.7
# Select the events in this range from influxdb
results = client.query(f'SELECT * FROM "events" WHERE lat>={startinglat} AND lat<={stoplat} AND lon>={startinglon} AND lon<={stoplon}')
# Connect to the sqlite3-DB, this is our output
con = sqlite3.connect('/opt/sqlite3dbs/part.db')
cur = con.cursor()
# Create table in sqlite3
cur.execute('''DROP TABLE geopoints''')
cur.execute('''CREATE TABLE geopoints (lon real, lat real, count int)''')
# How many steps from lat-to-lat and lon-to-lon
step = 100
# Events in this radius should be counted
reqdist = 3
for lat1 in [round(x * (1/step),3) for x in range(int(startinglat * step), int(stoplat * step))]:
  print (f"Lat: {lat1}   ", end='\r')
  for lon1 in [round(x * (1/step),3) for x in range(int(startinglon * step), int(stoplon * step))]:
    count1 = 0
    for m2 in results.get_points():
      lon2 = m2['lon']
      lat2 = m2['lat']
      # Distance calculation
      lat = (lat1 + lat2) / 2 * 0.01745
      dx = 111.3 * math.cos(lat) * (lon1 - lon2)
      dy = 111.3 * (lat1 - lat2)
      distance = math.sqrt(dx * dx + dy * dy)
      if (distance < reqdist):
        count1+=1
    # Insert the number of events within the required distance to this point in sqlite3
    cur.execute(f"INSERT INTO geopoints VALUES ({lon1}, {lat1}, {count1})")
con.commit()
con.close()