- Posts: 13
- Thank you received: 1
select
distinct states.icao24, callsign, (time - (time % 3600)) as timestamp_hour, opstat.version
from (
select
*
from
state_vectors_data4
where
hour = 1525651200
and onground = false
-- Bounding Box for "Europe"
and (
((lon >= -31.87 and lon < 53.05)
and (lat >= 20.23 and lat < 72.57))
)
) as states
join (
select
*
from operational_status_data4
where
-- 2018-05-07 till 2018-05-14 (excluded)
hour = 1525651200
and msgcount > 5
) as opstat
on
floor(opstat.mintime) = states.time
and opstat.icao24 = states.icao24
order by icao24;
select
distinct position.icao24, position.hour, 0
from (
select
*
from
position_data4
where
-- 2018-05-07 till 2018-05-14 (excluded)
hour = 1525651200
and surface = false
-- Bounding Box for "Europe"
and (
((lon >= -31.87 and lon < 53.05)
and (lat >= 20.23 and lat < 72.57))
and msgcount > 5
)
) as position
join (
select
*
from operational_status_data4
where
hour = 1525651200
and msgcount > 5
) as opstat
on
floor(opstat.mintime) >= floor(position.mintime)
and floor(opstat.mintime) < ceil(position.maxtime)
and opstat.icao24 != position.icao24
order by icao24;
Please Log in or Create an account to join the conversation.
-- ADS-B MOPS 1 or 2 unique vals 2018-05-07 till 2018-05-14 (excluded)
-- using state vector
-- state vector used to scope geographically
select
distinct state.icao24, opstat.version
from (
select
icao24
from
state_vectors_data4
where
-- week of 2018-05-07 (1525651200 -- 1526256000)
(hour >= 1525651200
and hour < 1526256000)
and onground = false
-- Bounding Box for "Europe"
and (
((lon >= -31.87 and lon < 53.05)
and (lat >= 20.23 and lat < 72.57))
)
) as state
inner join (
select
icao24, version
from operational_status_data4
where
(hour >= 1525651200
and hour < 1526256000)
-- get enough redundancy as to garantee quality of the message
and msgcount > 5
) as opstat
on
-- state and opsst msg in same minute
-- floor(opstat.mintime) = state.time
-- and
opstat.icao24 = state.icao24;
-- ADS-B MOPS 0 (filter out the ones that are 1 or 2) unique vals on week 2018-05-08
-- position_data4 used to scope geographically and on msgcount > ..., i.e. no noisy messages
select
position.icao24, 0 as version
from (
select
distinct icao24
from
position_data4
where
-- week of 2018-05-07 (1525651200 -- 1526256000)
-- week of 2018-05-07 (1525651200 -- 1526256000)
(hour >= 1525651200
and hour < 1526256000)
and surface = false
-- Bounding Box for "Europe"
and (
((lon >= -31.87 and lon < 53.05)
and (lat >= 20.23 and lat < 72.57))
and msgcount > 5
)
) as position
left join (
select
distinct icao24
from operational_status_data4
where
-- week of 2018-05-07 (1525651200 -- 1526256000)
-- week of 2018-05-07 (1525651200 -- 1526256000)
(hour >= 1525651200
and hour < 1526256000)
and msgcount > 5
) as opstat
on
position.icao24 = opstat.icao24
where opstat.icao24 is NULL;
Please Log in or Create an account to join the conversation.