opencellid: use temp table rather than memory db

[loctrkd.git] / loctrkd / opencellid.py

diff --git a/loctrkd/opencellid.py b/loctrkd/opencellid.py
index ff193fd27a5f511787c9b3b671a22ce217f06a58..e77ba0caff35ba21497202958d522f631d21aa27 100644 (file)
--- a/loctrkd/opencellid.py
+++ b/loctrkd/opencellid.py
@@ -14,6 +14,7 @@ ldb = None
 def init(conf: ConfigParser) -> None:
     global ldb
     ldb = connect(conf["opencellid"]["dbfn"])
+    ldb.execute("create temp table seen (locac int, cellid int, signal int)")
 
 
 def shut() -> None:
@@ -23,28 +24,27 @@ def shut() -> None:
 
 def lookup(
     mcc: int, mnc: int, gsm_cells: List[Tuple[int, int, int]], __: Any
-) -> Tuple[float, float]:
+) -> Tuple[float, 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 (?, ?, ?)""",
+        "insert into 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
+                  from cells c, seen s
                   where c.mcc = ?
+                  and c.net = ?
                   and c.area = s.locac
                   and c.cell = s.cellid""",
-        (mcc,),
+        (mcc, mnc),
     )
     data = list(lc.fetchall())
-    # lc.execute("drop table mem.seen")
-    lc.execute("""detach database mem""")
+    # This should be faster than dropping and recreating the temp table
+    # https://www.sqlite.org/lang_delete.html#the_truncate_optimization
+    lc.execute("delete from seen")
     lc.close()
     if not data:
         raise ValueError("No location data found in opencellid")
@@ -52,4 +52,4 @@ def lookup(
     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)])
-    return avlat, avlon
+    return avlat, avlon, 99.9  # TODO estimate accuracy for real