ADS-B MOPS version

espin
Topic Author
Offline
New Member

6 years 6 months ago #468 by espin

ADS-B MOPS version was created by espin

I am researching the status of MOPS version for the various aircraft flying in Europe; later on I will match with airlines so to have a breakdown about compliance with EU regulation 1207/2011...something similar to what is done in the first pie chart of adsb24.com but also with a per airline view.

From Impala's operational_status_data4 table I can get the list of version 1 and 2 with something like (in a simplified way, just for one hour):

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;

I am now thinking to get MOPS version zero by sort of negating the second join but querying on position_data4 rather than state_vectors_data4 because I see many garbled callsigns, so I prefer to be sure and have a strong msgcount as a safety net for reliability of the message:

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;

Any thoughts?
Am I too conservative? (note that I want to record these icao24 for long-ish periods so as to gather evidence of the right values)

Any suggestions is welcome
Thanks a lot in advance

espin
Topic Author
Offline
New Member

6 years 6 months ago #480 by espin

Replied by espin on topic ADS-B MOPS version

I resorted to much simpler queries because I only need the icao24 and version.

Below is what I used.
Any feedback is welcome.

ADS-B MOPS 1 and 2

-- 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

-- 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;