X-Git-Url: http://www.average.org/gitweb/?a=blobdiff_plain;f=gps303%2Fopencellid.py;h=1d60bfee1671defc9f8ed6fe9e994e787408ac0f;hb=e84104c8d7e93efc4ab2f543e7dfef4cc0208187;hp=dc0daaee4bde2e201651dba2b3028484350208d0;hpb=45e5cd8ce6f931b3bfb291394336cf8d94d895c3;p=loctrkd.git

diff --git a/gps303/opencellid.py b/gps303/opencellid.py
index dc0daae..1d60bfe 100644
--- a/gps303/opencellid.py
+++ b/gps303/opencellid.py
@@ -1,73 +1,70 @@
 """
-Download csv for your carrier and your area from https://opencellid.org/
-$ sqlite3 <cell-database-file>
-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 <downloaded-file.csv> 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
+from typing import Any, Dict, List, Tuple
 
+__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())
-        sumsig = sum([sig for _, _, sig in data])
-        nsigs = [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: Dict[str, Any]) -> None:
+    global ldb
+    ldb = connect(conf["opencellid"]["dbfn"])
+
+
+def lookup(
+    mcc: int, mnc: int, gsm_cells: List[Tuple[int, int, int]], __: Any
+) -> Tuple[float, float]:
+    assert ldb is not None
+    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 0.0, 0.0
+    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__"):
     from datetime import datetime, timezone
     import sys
-    from .GT06mod import *
+    from .gps303proto import *
 
     db = connect(sys.argv[1])
     c = db.cursor()
     c.execute(
-        """select timestamp, imei, clntaddr, length, proto, payload from events
+        """select tstamp, packet from events
             where proto in (?, ?)""",
         (WIFI_POSITIONING.PROTO, WIFI_OFFLINE_POSITIONING.PROTO),
     )
-    for timestamp, imei, clntaddr, length, proto, payload in c:
-        obj = make_object(length, proto, payload)
-        avlat, avlon = qry_cell(sys.argv[2], obj.mcc, obj.gsm_cells)
+    init({"opencellid": {"dbfn": sys.argv[2]}})
+    for timestamp, packet in c:
+        obj = parse_message(packet)
+        avlat, avlon = lookup(obj.mcc, obj.mnc, obj.gsm_cells, obj.wifi_aps)
         print(
             "{} {:+#010.8g},{:+#010.8g}".format(
                 datetime.fromtimestamp(timestamp), avlat, avlon