From: Eugene Crosser Date: Thu, 26 May 2022 13:34:45 +0000 (+0200) Subject: Prepare to have dynamically chosen lookup backend X-Git-Tag: 0.94~2 X-Git-Url: http://www.average.org/gitweb/?p=loctrkd.git;a=commitdiff_plain;h=5e1ae25333928e02eed519652256bee2e8e06671 Prepare to have dynamically chosen lookup backend --- diff --git a/gps303/lookaside.py b/gps303/lookaside.py index b42ce06..6924757 100644 --- a/gps303/lookaside.py +++ b/gps303/lookaside.py @@ -1,6 +1,7 @@ """ Estimate coordinates from WIFI_POSITIONING and send back """ from datetime import datetime, timezone +from importlib import import_module from logging import getLogger from os import umask from struct import pack @@ -8,17 +9,19 @@ import zmq from . import common from .gps303proto import parse_message, WIFI_POSITIONING -from .opencellid import qry_cell from .zmsg import Bcast, Resp, topic log = getLogger("gps303/lookaside") def runserver(conf): - if conf.get("lookaside", "backend") != "opencellid": + if conf.get("lookaside", "backend") == "opencellid": + qry = import_module(".opencellid", __package__) + else: raise NotImplementedError( "Lookaside only implements opencellid backend" ) + qry.init(conf) zctx = zmq.Context() zsub = zctx.socket(zmq.SUB) zsub.connect(conf.get("collector", "publishurl")) @@ -37,9 +40,7 @@ def runserver(conf): datetime.fromtimestamp(zmsg.when).astimezone(tz=timezone.utc), msg, ) - lat, lon = qry_cell( - conf["opencellid"]["dbfn"], msg.mcc, msg.gsm_cells - ) + lat, lon = qry.lookup(msg.mcc, msg.gsm_cells, msg.wifi_aps) resp = Resp( imei=zmsg.imei, when=zmsg.when, # not the current time, but the original! diff --git a/gps303/mkgpx.py b/gps303/mkgpx.py index f694b7e..061b54a 100644 --- a/gps303/mkgpx.py +++ b/gps303/mkgpx.py @@ -3,8 +3,9 @@ from sqlite3 import connect import sys from .gps303proto import * -from .opencellid import qry_cell +from . import opencellid as ocid +ocid.init({"opencellid": {"dbfn": sys.argv[2]}}) db = connect(sys.argv[1]) c = db.cursor() c.execute( @@ -38,7 +39,7 @@ xmlns="http://www.topografix.com/GPX/1/1"> for tstamp, packet in c: msg = parse_message(packet) if isinstance(msg, (WIFI_POSITIONING, WIFI_OFFLINE_POSITIONING)): - lat, lon = qry_cell(sys.argv[2], msg.mcc, msg.gsm_cells) + lat, lon = ocid.lookup(msg.mcc, msg.gsm_cells, msg.wifi_aps) if lat is None or lon is None: continue elif isinstance(msg, (GPS_POSITIONING, GPS_OFFLINE_POSITIONING)): diff --git a/gps303/opencellid.py b/gps303/opencellid.py index 436d559..0131dc0 100644 --- a/gps303/opencellid.py +++ b/gps303/opencellid.py @@ -1,58 +1,48 @@ """ -Download csv for your carrier and your area from https://opencellid.org/ -$ sqlite3 -sqlite> create table if not exists cells ( - "radio" text, - "mcc" int, - "net" int, - "area" int, - "cell" int, - "unit" int, - "lon" int, - "lat" int, - "range" int, - "samples" int, - "changeable" int, - "created" int, - "updated" int, - "averageSignal" int -); -sqlite> .mode csv -sqlite> .import cells -sqlite> create index if not exists cell_idx on cells (mcc, area, cell); +Lookaside backend to query local opencellid database """ from sqlite3 import connect +__all__ = "init", "lookup" -def qry_cell(dbname, mcc, gsm_cells): - with connect(dbname) as ldb: - lc = ldb.cursor() - lc.execute("""attach database ":memory:" as mem""") - lc.execute("create table mem.seen (locac int, cellid int, signal int)") - lc.executemany( - """insert into mem.seen (locac, cellid, signal) - values (?, ?, ?)""", - gsm_cells, - ) - lc.execute( - """select c.lat, c.lon, s.signal - from main.cells c, mem.seen s - where c.mcc = ? - and c.area = s.locac - and c.cell = s.cellid""", - (mcc,), - ) - data = list(lc.fetchall()) - if not data: - return None, None - sumsig = sum([1 / sig for _, _, sig in data]) - nsigs = [1 / sig / sumsig for _, _, sig in data] - avlat = sum([lat * nsig for (lat, _, _), nsig in zip(data, nsigs)]) - avlon = sum([lon * nsig for (_, lon, _), nsig in zip(data, nsigs)]) - # lc.execute("drop table mem.seen") - lc.close() - return avlat, avlon +ldb = None + + +def init(conf): + global ldb + ldb = connect(conf["opencellid"]["dbfn"]) + + +def lookup(mcc, gsm_cells, _): + lc = ldb.cursor() + lc.execute("""attach database ":memory:" as mem""") + lc.execute("create table mem.seen (locac int, cellid int, signal int)") + lc.executemany( + """insert into mem.seen (locac, cellid, signal) + values (?, ?, ?)""", + gsm_cells, + ) + ldb.commit() + lc.execute( + """select c.lat, c.lon, s.signal + from main.cells c, mem.seen s + where c.mcc = ? + and c.area = s.locac + and c.cell = s.cellid""", + (mcc,), + ) + data = list(lc.fetchall()) + if not data: + return None, None + sumsig = sum([1 / sig for _, _, sig in data]) + nsigs = [1 / sig / sumsig for _, _, sig in data] + avlat = sum([lat * nsig for (lat, _, _), nsig in zip(data, nsigs)]) + avlon = sum([lon * nsig for (_, lon, _), nsig in zip(data, nsigs)]) + # lc.execute("drop table mem.seen") + lc.execute("""detach database mem""") + lc.close() + return avlat, avlon if __name__.endswith("__main__"): @@ -63,13 +53,14 @@ if __name__.endswith("__main__"): db = connect(sys.argv[1]) c = db.cursor() c.execute( - """select select tstamp, packet from events + """select tstamp, packet from events where proto in (?, ?)""", (WIFI_POSITIONING.PROTO, WIFI_OFFLINE_POSITIONING.PROTO), ) - for timestamp, imei, clntaddr, proto, payload in c: + init({"opencellid": {"dbfn": sys.argv[2]}}) + for timestamp, packet in c: obj = parse_message(packet) - avlat, avlon = qry_cell(sys.argv[2], obj.mcc, obj.gsm_cells) + avlat, avlon = lookup(obj.mcc, obj.gsm_cells, obj.wifi_aps) print( "{} {:+#010.8g},{:+#010.8g}".format( datetime.fromtimestamp(timestamp), avlat, avlon