2 Lookaside backend to query local opencellid database
5 from sqlite3 import connect
7 __all__ = "init", "lookup"
14 ldb = connect(conf["opencellid"]["dbfn"])
17 def lookup(mcc, mnc, gsm_cells, __):
19 lc.execute("""attach database ":memory:" as mem""")
20 lc.execute("create table mem.seen (locac int, cellid int, signal int)")
22 """insert into mem.seen (locac, cellid, signal)
28 """select c.lat, c.lon, s.signal
29 from main.cells c, mem.seen s
32 and c.cell = s.cellid""",
35 data = list(lc.fetchall())
38 sumsig = sum([1 / sig for _, _, sig in data])
39 nsigs = [1 / sig / sumsig for _, _, sig in data]
40 avlat = sum([lat * nsig for (lat, _, _), nsig in zip(data, nsigs)])
41 avlon = sum([lon * nsig for (_, lon, _), nsig in zip(data, nsigs)])
42 # lc.execute("drop table mem.seen")
43 lc.execute("""detach database mem""")
48 if __name__.endswith("__main__"):
49 from datetime import datetime, timezone
51 from .gps303proto import *
53 db = connect(sys.argv[1])
56 """select tstamp, packet from events
57 where proto in (?, ?)""",
58 (WIFI_POSITIONING.PROTO, WIFI_OFFLINE_POSITIONING.PROTO),
60 init({"opencellid": {"dbfn": sys.argv[2]}})
61 for timestamp, packet in c:
62 obj = parse_message(packet)
63 avlat, avlon = lookup(obj.mcc, obj.mnc, obj.gsm_cells, obj.wifi_aps)
65 "{} {:+#010.8g},{:+#010.8g}".format(
66 datetime.fromtimestamp(timestamp), avlat, avlon