"""
-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())
- 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: 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__"):
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