forked from evandiewald/helium-topography
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql_queries.py
110 lines (91 loc) · 3.34 KB
/
sql_queries.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
from sqlalchemy.engine import Engine
import h3
def get_hotspot_dict_sql(engine: Engine, hotspot_address: str):
res = engine.execute(f"select location, name from gateway_inventory where address = '{hotspot_address}';").one()
(lat, lon) = h3.h3_to_geo(res[0])
return {"address": hotspot_address, "longitude": lon, "latitude": lat, "name": res[1]}
def get_witnesses_for_hotspot_sql(engine: Engine, witness_address: str, limit: int = 1000):
sql = f"""with a as
(select
distinct on (transmitter_address)
witness_address,
transmitter_address,
distance_km * 1000 as distance_m,
tx_power,
witness_signal as rssi,
witness_snr as snr
from challenge_receipts_parsed
where witness_address = '{witness_address}'
limit {limit})
select
transmitter_address,
witness_address,
distance_m,
b.location as location_beacon,
w.location as location_witness,
w.elevation as elevation_witness,
w.gain as gain_witness,
b.elevation as elevation_beacon,
b.gain as gain_beacon,
tx_power,
rssi,
snr,
w.owner as witness_owner
from a join gateway_inventory b on b.address = a.transmitter_address
join gateway_inventory w on w.address = a.witness_address;"""
res = engine.execute(sql).all()
return [{"_from": r[0], "_to": r[1],
"distance_m": r[2],
"coords_beacon": h3.h3_to_geo(r[3]),
"coords_witness": h3.h3_to_geo(r[4]),
"elevation_witness": r[5],
"gain_witness": r[6],
"elevation_beacon": r[7],
"gain_beacon": r[8],
"tx_power": r[9],
"rssi": r[10],
"snr": r[11],
"witness_owner": r[12]} for r in res]
def get_witnesses_of_hotspot_sql(engine: Engine, transmitter_address: str, limit: int = 1000):
# note the bizarre swap of transmitter_address and witness_address labels. this is
sql = f"""with a as
(select
distinct on (witness_address)
witness_address,
transmitter_address,
distance_km * 1000 as distance_m,
tx_power,
witness_signal as rssi,
witness_snr as snr
from challenge_receipts_parsed
where transmitter_address = '{transmitter_address}'
limit {limit})
select
transmitter_address as witness_address,
witness_address as transmitter_address,
distance_m,
b.location as location_beacon,
w.location as location_witness,
w.elevation as elevation_witness,
w.gain as gain_witness,
b.elevation as elevation_beacon,
b.gain as gain_beacon,
tx_power,
rssi,
snr,
w.owner as witness_owner
from a join gateway_inventory b on b.address = a.witness_address
join gateway_inventory w on w.address = a.transmitter_address;"""
res = engine.execute(sql).all()
return [{"_from": r[0], "_to": r[1],
"distance_m": r[2],
"coords_beacon": h3.h3_to_geo(r[3]),
"coords_witness": h3.h3_to_geo(r[4]),
"elevation_witness": r[5],
"gain_witness": r[6],
"elevation_beacon": r[7],
"gain_beacon": r[8],
"tx_power": r[9],
"rssi": r[10],
"snr": r[11],
"witness_owner": r[12]} for r in res]