2 Download csv for your carrier and your area from https://opencellid.org/
3 $ sqlite3 <cell-database-file>
4 sqlite> create table if not exists cells (
21 sqlite> .import <downloaded-file.csv> cells
22 sqlite> create index if not exists cell_idx on cells (mcc, area, cell);
25 from sqlite3 import connect
28 def qry_cell(dbname, mcc, gsm_cells):
29 with connect(dbname) as ldb:
31 lc.execute("""attach database ":memory:" as mem""")
32 lc.execute("create table mem.seen (locac int, cellid int, signal int)")
34 """insert into mem.seen (locac, cellid, signal)
39 """select c.lat, c.lon, s.signal
40 from main.cells c, mem.seen s
43 and c.cell = s.cellid""",
46 data = list(lc.fetchall())
47 sumsig = sum([sig for _, _, sig in data])
48 nsigs = [sig / sumsig for _, _, sig in data]
49 avlat = sum([lat * nsig for (lat, _, _), nsig in zip(data, nsigs)])
50 avlon = sum([lon * nsig for (_, lon, _), nsig in zip(data, nsigs)])
51 # lc.execute("drop table mem.seen")
56 if __name__.endswith("__main__"):
57 from datetime import datetime, timezone
59 from .GT06mod import *
61 db = connect(sys.argv[1])
64 """select timestamp, imei, clntaddr, length, proto, payload from events
65 where proto in (?, ?)""",
66 (WIFI_POSITIONING.PROTO, WIFI_OFFLINE_POSITIONING.PROTO),
68 for timestamp, imei, clntaddr, length, proto, payload in c:
69 obj = make_object(length, proto, payload)
70 avlat, avlon = qry_cell(sys.argv[2], obj.mcc, obj.gsm_cells)
72 "{} {:+#010.8g},{:+#010.8g}".format(
73 datetime.fromtimestamp(timestamp), avlat, avlon