hongbo-miao/hongbomiao.com

select
    from_unixtime_nanos(_time) as "Time",
    st_distance(
        st_geometryfromtext('POINT(34.052235 -118.243683)'),
        st_geometryfromtext(_coordinate)
    ) as "Distance to Los Angeles, CA",
    _coordinate,
    desc,
    ownop,
    r,
    t,
    icao,
    noregdata,
    trace_on_ground,
    trace_altitude_ft,
    trace_altitude_type,
    trace_feeder_id,
    trace_geometric_altitude_ft,
    trace_geometric_vertical_rate_fpm,
    trace_ground_speed_kt,
    trace_indicated_airspeed_kt,
    trace_new_leg_start,
    trace_position_stale,
    trace_position_type,
    trace_roll_angle_deg,
    trace_track_deg,
    trace_vertical_rate_fpm,
    trace_vertical_rate_type,
    trace_aircraft_alert,
    trace_aircraft_alt_geom,
    trace_aircraft_baro_rate,
    trace_aircraft_category,
    trace_aircraft_emergency,
    trace_aircraft_flight,
    trace_aircraft_geom_rate,
    trace_aircraft_gva,
    trace_aircraft_ias,
    trace_aircraft_mach,
    trace_aircraft_mag_heading,
    trace_aircraft_nac_p,
    trace_aircraft_nac_v,
    trace_aircraft_nav_altitude_fms,
    trace_aircraft_nav_altitude_mcp,
    trace_aircraft_nav_heading,
    trace_aircraft_nav_qnh,
    trace_aircraft_nic,
    trace_aircraft_nic_baro,
    trace_aircraft_oat,
    trace_aircraft_rc,
    trace_aircraft_roll,
    trace_aircraft_sda,
    trace_aircraft_sil,
    trace_aircraft_sil_type,
    trace_aircraft_spi,
    trace_aircraft_squawk,
    trace_aircraft_tas,
    trace_aircraft_tat,
    trace_aircraft_track,
    trace_aircraft_track_rate,
    trace_aircraft_true_heading,
    trace_aircraft_type,
    trace_aircraft_version,
    trace_aircraft_wd,
    trace_aircraft_ws
from delta.adsb_db.adsb_2x_flight_trace_data
where cast(_date as date) between date '2023-08-01' and date '2023-08-03'
order by _time
limit 100;