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())
49 sumsig = sum([1 / sig for _, _, sig in data])
50 nsigs = [1 / sig / sumsig for _, _, sig in data]
51 avlat = sum([lat * nsig for (lat, _, _), nsig in zip(data, nsigs)])
52 avlon = sum([lon * nsig for (_, lon, _), nsig in zip(data, nsigs)])
53 # lc.execute("drop table mem.seen")
58 if __name__.endswith("__main__"):
59 from datetime import datetime, timezone
61 from .GT06mod import *
63 db = connect(sys.argv[1])
66 """select timestamp, imei, clntaddr, length, proto, payload from events
67 where proto in (?, ?)""",
68 (WIFI_POSITIONING.PROTO, WIFI_OFFLINE_POSITIONING.PROTO),
70 for timestamp, imei, clntaddr, length, proto, payload in c:
71 obj = make_object(length, proto, payload)
72 avlat, avlon = qry_cell(sys.argv[2], obj.mcc, obj.gsm_cells)
74 "{} {:+#010.8g},{:+#010.8g}".format(
75 datetime.fromtimestamp(timestamp), avlat, avlon