add lookaside module and opencellid lookup

[loctrkd.git] / gps303 / opencellid.py

diff --git a/gps303/opencellid.py b/gps303/opencellid.py
index 5527a1912bf38f69013210b19d824e452ea72463..dc0daaee4bde2e201651dba2b3028484350208d0 100644 (file)
--- a/gps303/opencellid.py
+++ b/gps303/opencellid.py
@@ -22,35 +22,54 @@ sqlite> .import <downloaded-file.csv> cells
 sqlite> create index if not exists cell_idx on cells (mcc, area, cell);
 """
 
-from datetime import datetime, timezone
-from pprint import pprint
 from sqlite3 import connect
-import sys
 
-from .GT06mod import *
 
-db = connect(sys.argv[1])
-ldb = connect(sys.argv[2])
-lc = ldb.cursor()
-c = db.cursor()
-c.execute(
-    """select timestamp, imei, clntaddr, length, proto, payload 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)
-    qry = """select lat, lon from cells
-             where mcc = {} and (area, cell) in ({})""".format(
-        obj.mcc,
-        ", ".join(
-            [
-                "({}, {})".format(locac, cellid)
-                for locac, cellid, _ in obj.gsm_cells
-            ]
-        ),
+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
+
+
+if __name__.endswith("__main__"):
+    from datetime import datetime, timezone
+    import sys
+    from .GT06mod import *
+
+    db = connect(sys.argv[1])
+    c = db.cursor()
+    c.execute(
+        """select timestamp, imei, clntaddr, length, proto, payload from events
+            where proto in (?, ?)""",
+        (WIFI_POSITIONING.PROTO, WIFI_OFFLINE_POSITIONING.PROTO),
     )
-    print(qry)
-    lc.execute(qry)
-    for lat, lon in lc:
-        print(lat, lon)
+    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)
+        print(
+            "{} {:+#010.8g},{:+#010.8g}".format(
+                datetime.fromtimestamp(timestamp), avlat, avlon
+            )
+        )