From: Eugene Crosser <crosser@average.org>
Date: Fri, 18 Mar 2022 23:31:17 +0000 (+0100)
Subject: add lookaside module and opencellid lookup
X-Git-Tag: 0.01~61
X-Git-Url: http://www.average.org/gitweb/?p=loctrkd.git;a=commitdiff_plain;h=45e5cd8ce6f931b3bfb291394336cf8d94d895c3

add lookaside module and opencellid lookup
---

diff --git a/gps303.conf b/gps303.conf
index 97c9959..3a90e3b 100644
--- a/gps303.conf
+++ b/gps303.conf
@@ -2,6 +2,9 @@
 port = 4303
 dbfn = gps303.sqlite
 
+[opencellid]
+dbfn = opencellid.sqlite
+
 [device]
 uploadIntervalSeconds = 0x0300
 binarySwitch = 0b00110001
diff --git a/gps303/GT06mod.py b/gps303/GT06mod.py
index 86cc8aa..ded38f2 100755
--- a/gps303/GT06mod.py
+++ b/gps303/GT06mod.py
@@ -307,8 +307,13 @@ class RESTORE_PASSWORD(_GT06pkt):
 class WIFI_POSITIONING(_WIFI_POSITIONING):
     PROTO = 0x69
 
-    def response(self):
-        payload = b""  # TODO fill payload
+    def response(self, lat=None, lon=None):
+        if lat is None or lon is None:
+            payload = b""
+        else:
+            payload = "{:+#010.8g},{:+#010.8g}".format(lat, lon).encode(
+                "ascii"
+            )
         return super().response(payload)
 
 
@@ -397,8 +402,8 @@ def handle_packet(packet, addr, when):
             return make_object(length, proto, payload)
 
 
-def make_response(msg):
-    return msg.response()
+def make_response(msg, **kwargs):
+    return msg.response(**kwargs)
 
 
 def set_config(config):  # Note that we are setting _class_ attribute
diff --git a/gps303/__main__.py b/gps303/__main__.py
index ddad2b7..e3fd84a 100755
--- a/gps303/__main__.py
+++ b/gps303/__main__.py
@@ -9,6 +9,7 @@ from time import time
 from .config import readconfig
 from .GT06mod import handle_packet, make_response, LOGIN, set_config
 from .evstore import initdb, stow
+from .lookaside import prepare_response
 
 CONF = "/etc/gps303.conf"
 
@@ -81,7 +82,8 @@ if __name__.endswith("__main__"):
                             msg.proto,
                             msg.payload,
                         )
-                        response = make_response(msg)
+                        kwargs = prepare_response(conf, msg)
+                        response = make_response(msg, **kwargs)
                         if response:
                             try:
                                 # Ignore possibility of blocking
diff --git a/gps303/lookaside.py b/gps303/lookaside.py
new file mode 100644
index 0000000..3c3ee32
--- /dev/null
+++ b/gps303/lookaside.py
@@ -0,0 +1,13 @@
+"""
+For when responding to the terminal is not trivial
+"""
+
+from .GT06mod import *
+from .opencellid import qry_cell
+
+def prepare_response(conf, msg):
+    if isinstance(msg, WIFI_POSITIONING):
+        lat, lon = qry_cell(conf["opencellid"]["dbfn"],
+                msg.mcc, msg.gsm_cells)
+        return {"lat": lat, "lon": lon}
+    return {}
diff --git a/gps303/opencellid.py b/gps303/opencellid.py
index 5527a19..dc0daae 100644
--- 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
+            )
+        )