Distance vs. RSSI query for DeWi ETL as of 7-4-2022 (poc_receipts_v2)

distance-vs-rssi.sql

with hashes as 

(select transaction_hash, actor from transaction_actors where 
actor = {{address}} 
and actor_role = 'witness' 
and block > (select max(height) - {{n_blocks}} from blocks)),

target_transactions as 
(select fields, hash from transactions where 
(type = 'poc_receipts_v2' or type = 'poc_receipts_v1') 
and transactions.hash in (select transaction_hash from hashes)),

metadata as 
(select 
actor as witness,
fields->'path'->0->>'challengee' as transmitter,
fields->'path'->0->'witnesses' as w, 
fields->'path'->0->>'challengee_location' as location_tx from hashes 
left join target_transactions on hashes.transaction_hash = target_transactions.hash),

pairs as
(select 

witness,
transmitter,
location_tx,

(select t -> 'signal' from jsonb_array_elements(w) as x(t) where t->>'gateway' = witness)::int as rssi,
(select t ->> 'location' from jsonb_array_elements(w) as x(t) where t->>'gateway' = witness) as location_rx

from metadata),

results as
(select

witness,
transmitter,
rssi,

ST_DistanceSphere(ST_Centroid(tx.geometry), ST_Centroid(rx.geometry)) as distance_m

from pairs 

join locations tx on tx.location = location_tx
join locations rx on rx.location = location_rx)

select * from results where distance_m < 100e3;