Malloy Documentation
search

The plugin currently supports US maps. Segment maps take as input 4 columns: start latitude , start longitude, end latitude, and end longitude of the segment. The model and data styles for the subsequent examples are:

document
source: airports is duckdb.table('../data/airports.parquet') extend {
  dimension: name is concat(code, ' - ', full_name)
  measure: airport_count is count()
}

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: orig is airports on origin = orig.code
  join_one: dest is airports on destination = dest.code

  measure: flight_count is count()

  # segment_map
  view: routes_map is {
    group_by:
      orig.latitude
      orig.longitude
      latitude2 is dest.latitude
      longitude2 is dest.longitude
    aggregate: flight_count
  }
}

Run as a simple query

Departing from Chicago

document
run: flights -> routes_map + { where: dep_time = @2003-02 and origin = 'ORD' }
QUERY RESULTS
[
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 33.94,
    "longitude2": -118.4,
    "flight_count": 13
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 36.08,
    "longitude2": -115.15,
    "flight_count": 12
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 45.58,
    "longitude2": -122.59,
    "flight_count": 11
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.04,
    "longitude2": -84.66,
    "flight_count": 10
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 47.44,
    "longitude2": -122.3,
    "flight_count": 10
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 40.77,
    "longitude2": -73.87,
    "flight_count": 9
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 32.73,
    "longitude2": -117.18,
    "flight_count": 8
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 37.61,
    "longitude2": -122.37,
    "flight_count": 8
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 38.74,
    "longitude2": -90.35,
    "flight_count": 8
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.85,
    "longitude2": -104.66,
    "flight_count": 8
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.29,
    "longitude2": -94.71,
    "flight_count": 7
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 32.89,
    "longitude2": -97.03,
    "flight_count": 7
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.87,
    "longitude2": -75.24,
    "flight_count": 6
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 38.85,
    "longitude2": -77.03,
    "flight_count": 6
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 40.69,
    "longitude2": -74.16,
    "flight_count": 6
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 42.36,
    "longitude2": -71,
    "flight_count": 5
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 25.79,
    "longitude2": -80.29,
    "flight_count": 5
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 33.43,
    "longitude2": -112,
    "flight_count": 4
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 40.49,
    "longitude2": -80.23,
    "flight_count": 4
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 26.07,
    "longitude2": -80.15,
    "flight_count": 4
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 42.94,
    "longitude2": -78.73,
    "flight_count": 4
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 40.78,
    "longitude2": -111.97,
    "flight_count": 4
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 27.97,
    "longitude2": -82.53,
    "flight_count": 3
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.17,
    "longitude2": -76.66,
    "flight_count": 3
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 42.21,
    "longitude2": -83.34,
    "flight_count": 3
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 33.82,
    "longitude2": -116.5,
    "flight_count": 3
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 30.19,
    "longitude2": -97.66,
    "flight_count": 3
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 37.72,
    "longitude2": -122.22,
    "flight_count": 3
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 42.74,
    "longitude2": -73.8,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 29.98,
    "longitude2": -95.33,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 42.93,
    "longitude2": -71.43,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 41.93,
    "longitude2": -72.68,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 38.94,
    "longitude2": -77.45,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 26.53,
    "longitude2": -81.75,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 38.69,
    "longitude2": -121.59,
    "flight_count": 2
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 29.99,
    "longitude2": -90.25,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 35.87,
    "longitude2": -78.78,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 33.67,
    "longitude2": -117.86,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.99,
    "longitude2": -82.89,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 26.68,
    "longitude2": -80.09,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 41.72,
    "longitude2": -71.42,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 37.36,
    "longitude2": -121.92,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 18.43,
    "longitude2": -66,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 35.04,
    "longitude2": -106.6,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 33.64,
    "longitude2": -84.42,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 44.47,
    "longitude2": -73.15,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 39.71,
    "longitude2": -86.29,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 28.42,
    "longitude2": -81.31,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 32.11,
    "longitude2": -110.94,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 36.12,
    "longitude2": -86.67,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 31.8,
    "longitude2": -106.37,
    "flight_count": 1
  }
]
SELECT 
   orig_0."latitude" as "latitude",
   orig_0."longitude" as "longitude",
   dest_0."latitude" as "latitude2",
   dest_0."longitude" as "longitude2",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/airports.parquet' AS dest_0
  ON base."destination"=dest_0."code"
 LEFT JOIN '../data/airports.parquet' AS orig_0
  ON base."origin"=orig_0."code"
WHERE ((base."dep_time">=TIMESTAMP '2003-02-01 00:00:00') and (base."dep_time"<TIMESTAMP '2003-03-01 00:00:00')) and (base."origin"='ORD')
GROUP BY 1,2,3,4
ORDER BY 5 desc NULLS LAST

Run as a trellis

By calling the configured map as a nested query, a trellis is formed.

document
run: flights -> {
  where: dep_time = @2003-02 and origin = 'ORD'
  group_by: carrier
  aggregate: flight_count
  nest: routes_map
}
QUERY RESULTS
[
  {
    "carrier": "UA",
    "flight_count": 113,
    "routes_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 11
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 45.58,
        "longitude2": -122.59,
        "flight_count": 9
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.61,
        "longitude2": -122.37,
        "flight_count": 8
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.77,
        "longitude2": -73.87,
        "flight_count": 7
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.85,
        "longitude2": -77.03,
        "flight_count": 6
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.69,
        "longitude2": -74.16,
        "flight_count": 6
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.85,
        "longitude2": -104.66,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.87,
        "longitude2": -75.24,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.94,
        "longitude2": -78.73,
        "flight_count": 4
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.72,
        "longitude2": -122.22,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.43,
        "longitude2": -112,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.29,
        "longitude2": -94.71,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.49,
        "longitude2": -80.23,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.94,
        "longitude2": -77.45,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.08,
        "longitude2": -115.15,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.69,
        "longitude2": -121.59,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.93,
        "longitude2": -71.43,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.78,
        "longitude2": -111.97,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.74,
        "longitude2": -73.8,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 27.97,
        "longitude2": -82.53,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.99,
        "longitude2": -82.89,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.71,
        "longitude2": -86.29,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 28.42,
        "longitude2": -81.31,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.53,
        "longitude2": -81.75,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.67,
        "longitude2": -117.86,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.07,
        "longitude2": -80.15,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.99,
        "longitude2": -90.25,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 44.47,
        "longitude2": -73.15,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.36,
        "longitude2": -121.92,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 18.43,
        "longitude2": -66,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 1
      }
    ]
  },
  {
    "carrier": "AA",
    "flight_count": 77,
    "routes_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.08,
        "longitude2": -115.15,
        "flight_count": 10
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.74,
        "longitude2": -90.35,
        "flight_count": 8
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 6
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.29,
        "longitude2": -94.71,
        "flight_count": 4
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 4
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 30.19,
        "longitude2": -97.66,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.07,
        "longitude2": -80.15,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.82,
        "longitude2": -116.5,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.17,
        "longitude2": -76.66,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.85,
        "longitude2": -104.66,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.93,
        "longitude2": -72.68,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 45.58,
        "longitude2": -122.59,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.77,
        "longitude2": -73.87,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.78,
        "longitude2": -111.97,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.68,
        "longitude2": -80.09,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.11,
        "longitude2": -110.94,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.12,
        "longitude2": -86.67,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 31.8,
        "longitude2": -106.37,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 27.97,
        "longitude2": -82.53,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.43,
        "longitude2": -112,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.87,
        "longitude2": -78.78,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.53,
        "longitude2": -81.75,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.04,
        "longitude2": -106.6,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.72,
        "longitude2": -71.42,
        "flight_count": 1
      }
    ]
  },
  {
    "carrier": "DL",
    "flight_count": 11,
    "routes_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.04,
        "longitude2": -84.66,
        "flight_count": 10
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.64,
        "longitude2": -84.42,
        "flight_count": 1
      }
    ]
  },
  {
    "carrier": "CO",
    "flight_count": 2,
    "routes_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.98,
        "longitude2": -95.33,
        "flight_count": 2
      }
    ]
  },
  {
    "carrier": "NW",
    "flight_count": 2,
    "routes_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 2
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."carrier" as "carrier__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "flight_count__0",
    CASE WHEN group_set=1 THEN
      orig_0."latitude"
      END as "latitude__1",
    CASE WHEN group_set=1 THEN
      orig_0."longitude"
      END as "longitude__1",
    CASE WHEN group_set=1 THEN
      dest_0."latitude"
      END as "latitude2__1",
    CASE WHEN group_set=1 THEN
      dest_0."longitude"
      END as "longitude2__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "flight_count__1"
  FROM '../data/flights.parquet' as base
   LEFT JOIN '../data/airports.parquet' AS dest_0
    ON base."destination"=dest_0."code"
   LEFT JOIN '../data/airports.parquet' AS orig_0
    ON base."origin"=orig_0."code"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  WHERE ((base."dep_time">=TIMESTAMP '2003-02-01 00:00:00') and (base."dep_time"<TIMESTAMP '2003-03-01 00:00:00')) and (base."origin"='ORD')
  GROUP BY 1,2,4,5,6,7
)
SELECT
  "carrier__0" as "carrier",
  MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count",
  COALESCE(LIST({
    "latitude": "latitude__1", 
    "longitude": "longitude__1", 
    "latitude2": "latitude2__1", 
    "longitude2": "longitude2__1", 
    "flight_count": "flight_count__1"}  ORDER BY  "flight_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "routes_map"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Run as a trellis, repeated with different filters

document
run: flights -> {
  group_by: carrier
  aggregate: flight_count
  nest:
    ord_segment_map is routes_map + { where: origin = 'ORD' }
    sfo_segment_map is routes_map + { where: origin = 'SFO' }
    jfk_segment_map is routes_map + { where: origin = 'JFK' }
}
QUERY RESULTS
[
  {
    "carrier": "WN",
    "flight_count": 88751,
    "ord_segment_map": [],
    "sfo_segment_map": [
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 45
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.43,
        "longitude2": -112,
        "flight_count": 41
      }
    ],
    "jfk_segment_map": []
  },
  {
    "carrier": "US",
    "flight_count": 37683,
    "ord_segment_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.87,
        "longitude2": -75.24,
        "flight_count": 171
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.21,
        "longitude2": -80.94,
        "flight_count": 156
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.49,
        "longitude2": -80.23,
        "flight_count": 96
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.85,
        "longitude2": -77.03,
        "flight_count": 79
      }
    ],
    "sfo_segment_map": [
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 35.21,
        "longitude2": -80.94,
        "flight_count": 49
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 39.87,
        "longitude2": -75.24,
        "flight_count": 44
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 40.49,
        "longitude2": -80.23,
        "flight_count": 40
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 39.17,
        "longitude2": -76.66,
        "flight_count": 24
      }
    ],
    "jfk_segment_map": []
  },
  {
    "carrier": "AA",
    "flight_count": 34577,
    "ord_segment_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 462
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.77,
        "longitude2": -73.87,
        "flight_count": 374
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 213
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 202
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 179
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.69,
        "longitude2": -74.16,
        "flight_count": 176
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.87,
        "longitude2": -78.78,
        "flight_count": 173
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.85,
        "longitude2": -104.66,
        "flight_count": 172
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.74,
        "longitude2": -90.35,
        "flight_count": 164
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 30.19,
        "longitude2": -97.66,
        "flight_count": 160
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.87,
        "longitude2": -75.24,
        "flight_count": 153
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 147
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.43,
        "longitude2": -112,
        "flight_count": 143
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.29,
        "longitude2": -94.71,
        "flight_count": 134
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.93,
        "longitude2": -72.68,
        "flight_count": 126
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.72,
        "longitude2": -71.42,
        "flight_count": 122
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.11,
        "longitude2": -110.94,
        "flight_count": 100
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.08,
        "longitude2": -115.15,
        "flight_count": 99
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 95
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 45.58,
        "longitude2": -122.59,
        "flight_count": 86
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 28.42,
        "longitude2": -81.31,
        "flight_count": 86
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.17,
        "longitude2": -76.66,
        "flight_count": 85
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.07,
        "longitude2": -80.15,
        "flight_count": 83
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 80
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.99,
        "longitude2": -90.25,
        "flight_count": 79
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.61,
        "longitude2": -122.37,
        "flight_count": 78
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.64,
        "longitude2": -84.42,
        "flight_count": 75
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.04,
        "longitude2": -106.6,
        "flight_count": 74
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.53,
        "longitude2": -98.46,
        "flight_count": 74
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 27.97,
        "longitude2": -82.53,
        "flight_count": 73
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.19,
        "longitude2": -95.88,
        "flight_count": 73
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.78,
        "longitude2": -111.97,
        "flight_count": 67
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.85,
        "longitude2": -77.03,
        "flight_count": 64
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.82,
        "longitude2": -116.5,
        "flight_count": 62
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 31.8,
        "longitude2": -106.37,
        "flight_count": 60
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 43.11,
        "longitude2": -76.1,
        "flight_count": 52
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 44.88,
        "longitude2": -93.21,
        "flight_count": 51
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.36,
        "longitude2": -121.92,
        "flight_count": 43
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.71,
        "longitude2": -86.29,
        "flight_count": 41
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.94,
        "longitude2": -78.73,
        "flight_count": 41
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.12,
        "longitude2": -86.67,
        "flight_count": 37
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.74,
        "longitude2": -73.8,
        "flight_count": 36
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 21.31,
        "longitude2": -157.92,
        "flight_count": 34
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.98,
        "longitude2": -95.33,
        "flight_count": 33
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.81,
        "longitude2": -118.15,
        "flight_count": 32
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 43.11,
        "longitude2": -77.67,
        "flight_count": 28
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.53,
        "longitude2": -81.75,
        "flight_count": 25
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 20.89,
        "longitude2": -156.43,
        "flight_count": 23
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.68,
        "longitude2": -80.09,
        "flight_count": 20
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.41,
        "longitude2": -81.84,
        "flight_count": 13
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.49,
        "longitude2": -119.76,
        "flight_count": 12
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.28,
        "longitude2": -94.3,
        "flight_count": 6
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.99,
        "longitude2": -82.89,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.39,
        "longitude2": -97.6,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.5,
        "longitude2": -107.89,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.69,
        "longitude2": -121.59,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.67,
        "longitude2": -117.86,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.3,
        "longitude2": -95.89,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 18.43,
        "longitude2": -66,
        "flight_count": 1
      }
    ],
    "sfo_segment_map": [
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 252
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 40.63,
        "longitude2": -73.77,
        "flight_count": 148
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 93
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 41.97,
        "longitude2": -87.9,
        "flight_count": 80
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 49
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 32
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 38.74,
        "longitude2": -90.35,
        "flight_count": 17
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 21.31,
        "longitude2": -157.92,
        "flight_count": 11
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.67,
        "longitude2": -117.86,
        "flight_count": 6
      }
    ],
    "jfk_segment_map": [
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 471
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 144
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 37.61,
        "longitude2": -122.37,
        "flight_count": 142
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 60
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 42
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 18.43,
        "longitude2": -66,
        "flight_count": 30
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 38.74,
        "longitude2": -90.35,
        "flight_count": 28
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 37.36,
        "longitude2": -121.92,
        "flight_count": 13
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 28.42,
        "longitude2": -81.31,
        "flight_count": 7
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 5
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 1
      }
    ]
  },
  {
    "carrier": "NW",
    "flight_count": 33580,
    "ord_segment_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 153
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 44.88,
        "longitude2": -93.21,
        "flight_count": 105
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.04,
        "longitude2": -89.97,
        "flight_count": 39
      }
    ],
    "sfo_segment_map": [
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 44.88,
        "longitude2": -93.21,
        "flight_count": 281
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 134
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 35.04,
        "longitude2": -89.97,
        "flight_count": 81
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 21.31,
        "longitude2": -157.92,
        "flight_count": 26
      }
    ],
    "jfk_segment_map": [
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 6
      }
    ]
  },
  {
    "carrier": "UA",
    "flight_count": 32757,
    "ord_segment_map": [
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.85,
        "longitude2": -104.66,
        "flight_count": 416
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 408
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.85,
        "longitude2": -77.03,
        "flight_count": 408
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.61,
        "longitude2": -122.37,
        "flight_count": 315
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.08,
        "longitude2": -115.15,
        "flight_count": 306
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 28.42,
        "longitude2": -81.31,
        "flight_count": 257
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 250
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.94,
        "longitude2": -77.45,
        "flight_count": 247
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.77,
        "longitude2": -73.87,
        "flight_count": 233
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 233
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 27.97,
        "longitude2": -82.53,
        "flight_count": 220
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 45.58,
        "longitude2": -122.59,
        "flight_count": 214
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.43,
        "longitude2": -112,
        "flight_count": 214
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 191
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.36,
        "longitude2": -121.92,
        "flight_count": 177
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.17,
        "longitude2": -76.66,
        "flight_count": 176
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.87,
        "longitude2": -75.24,
        "flight_count": 160
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.67,
        "longitude2": -117.86,
        "flight_count": 159
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.69,
        "longitude2": -121.59,
        "flight_count": 139
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 18.43,
        "longitude2": -66,
        "flight_count": 129
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.64,
        "longitude2": -84.42,
        "flight_count": 122
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.93,
        "longitude2": -72.68,
        "flight_count": 120
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 115
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.69,
        "longitude2": -74.16,
        "flight_count": 111
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.07,
        "longitude2": -80.15,
        "flight_count": 108
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.72,
        "longitude2": -122.22,
        "flight_count": 95
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.93,
        "longitude2": -71.43,
        "flight_count": 91
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.21,
        "longitude2": -83.34,
        "flight_count": 74
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 74
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.72,
        "longitude2": -71.42,
        "flight_count": 70
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.41,
        "longitude2": -81.84,
        "flight_count": 69
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 44.88,
        "longitude2": -93.21,
        "flight_count": 63
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.29,
        "longitude2": -94.71,
        "flight_count": 61
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.71,
        "longitude2": -86.29,
        "flight_count": 59
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.99,
        "longitude2": -82.89,
        "flight_count": 55
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 37.5,
        "longitude2": -77.31,
        "flight_count": 54
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.21,
        "longitude2": -80.94,
        "flight_count": 47
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.53,
        "longitude2": -81.75,
        "flight_count": 45
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.49,
        "longitude2": -80.23,
        "flight_count": 43
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.74,
        "longitude2": -73.8,
        "flight_count": 42
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.99,
        "longitude2": -90.25,
        "flight_count": 40
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.98,
        "longitude2": -95.33,
        "flight_count": 38
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 43.11,
        "longitude2": -77.67,
        "flight_count": 32
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 18.33,
        "longitude2": -64.97,
        "flight_count": 29
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 26.68,
        "longitude2": -80.09,
        "flight_count": 29
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.78,
        "longitude2": -111.97,
        "flight_count": 28
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.3,
        "longitude2": -95.89,
        "flight_count": 24
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.94,
        "longitude2": -78.73,
        "flight_count": 23
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 44.47,
        "longitude2": -73.15,
        "flight_count": 20
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 41.53,
        "longitude2": -93.66,
        "flight_count": 17
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.04,
        "longitude2": -84.66,
        "flight_count": 15
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.8,
        "longitude2": -104.7,
        "flight_count": 15
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.88,
        "longitude2": -85.52,
        "flight_count": 14
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.19,
        "longitude2": -76.76,
        "flight_count": 13
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 29.53,
        "longitude2": -98.46,
        "flight_count": 13
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 39.9,
        "longitude2": -84.21,
        "flight_count": 12
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.09,
        "longitude2": -79.93,
        "flight_count": 11
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 43.56,
        "longitude2": -116.22,
        "flight_count": 10
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 38.74,
        "longitude2": -90.35,
        "flight_count": 7
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 47.61,
        "longitude2": -117.53,
        "flight_count": 6
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 35.87,
        "longitude2": -78.78,
        "flight_count": 6
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 30.19,
        "longitude2": -97.66,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 43.11,
        "longitude2": -76.1,
        "flight_count": 5
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 32.11,
        "longitude2": -110.94,
        "flight_count": 4
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.12,
        "longitude2": -86.67,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 36.89,
        "longitude2": -76.2,
        "flight_count": 3
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 34.05,
        "longitude2": -117.6,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.85,
        "longitude2": -96.75,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 61.17,
        "longitude2": -149.99,
        "flight_count": 2
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 21.31,
        "longitude2": -157.92,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 40.63,
        "longitude2": -73.77,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 33.82,
        "longitude2": -116.5,
        "flight_count": 1
      },
      {
        "latitude": 41.97,
        "longitude": -87.9,
        "latitude2": 42.94,
        "longitude2": -87.89,
        "flight_count": 1
      }
    ],
    "sfo_segment_map": [
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 36.08,
        "longitude2": -115.15,
        "flight_count": 299
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 41.97,
        "longitude2": -87.9,
        "flight_count": 282
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 38.94,
        "longitude2": -77.45,
        "flight_count": 235
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 39.85,
        "longitude2": -104.66,
        "flight_count": 194
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 182
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.67,
        "longitude2": -117.86,
        "flight_count": 150
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.43,
        "longitude2": -112,
        "flight_count": 140
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 39.87,
        "longitude2": -75.24,
        "flight_count": 123
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 32.73,
        "longitude2": -117.18,
        "flight_count": 118
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 40.69,
        "longitude2": -74.16,
        "flight_count": 105
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 45.58,
        "longitude2": -122.59,
        "flight_count": 96
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 95
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 83
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 32.89,
        "longitude2": -97.03,
        "flight_count": 50
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 39.17,
        "longitude2": -76.66,
        "flight_count": 50
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 33.64,
        "longitude2": -84.42,
        "flight_count": 44
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 29.98,
        "longitude2": -95.33,
        "flight_count": 43
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 29.99,
        "longitude2": -90.25,
        "flight_count": 36
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 28.42,
        "longitude2": -81.31,
        "flight_count": 28
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 39.49,
        "longitude2": -119.76,
        "flight_count": 23
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 34.05,
        "longitude2": -117.6,
        "flight_count": 16
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 41.93,
        "longitude2": -72.68,
        "flight_count": 12
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 40.78,
        "longitude2": -111.97,
        "flight_count": 12
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 30.19,
        "longitude2": -97.66,
        "flight_count": 10
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 34.2,
        "longitude2": -118.35,
        "flight_count": 10
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 25.79,
        "longitude2": -80.29,
        "flight_count": 7
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 61.17,
        "longitude2": -149.99,
        "flight_count": 6
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 40.63,
        "longitude2": -73.77,
        "flight_count": 6
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 44.12,
        "longitude2": -123.21,
        "flight_count": 4
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 36.58,
        "longitude2": -121.84,
        "flight_count": 3
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 21.31,
        "longitude2": -157.92,
        "flight_count": 1
      },
      {
        "latitude": 37.61,
        "longitude": -122.37,
        "latitude2": 42.37,
        "longitude2": -122.87,
        "flight_count": 1
      }
    ],
    "jfk_segment_map": [
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 47.44,
        "longitude2": -122.3,
        "flight_count": 15
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 37.61,
        "longitude2": -122.37,
        "flight_count": 6
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 42.36,
        "longitude2": -71,
        "flight_count": 6
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 33.94,
        "longitude2": -118.4,
        "flight_count": 3
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 18.43,
        "longitude2": -66,
        "flight_count": 2
      },
      {
        "latitude": 40.63,
        "longitude": -73.77,
        "latitude2": 41.97,
        "longitude2": -87.9,
        "flight_count": 1
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."carrier" as "carrier__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "flight_count__0",
    CASE WHEN group_set=1 THEN
      orig_0."latitude"
      END as "latitude__1",
    CASE WHEN group_set=1 THEN
      orig_0."longitude"
      END as "longitude__1",
    CASE WHEN group_set=1 THEN
      dest_0."latitude"
      END as "latitude2__1",
    CASE WHEN group_set=1 THEN
      dest_0."longitude"
      END as "longitude2__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "flight_count__1",
    CASE WHEN group_set=2 THEN
      orig_0."latitude"
      END as "latitude__2",
    CASE WHEN group_set=2 THEN
      orig_0."longitude"
      END as "longitude__2",
    CASE WHEN group_set=2 THEN
      dest_0."latitude"
      END as "latitude2__2",
    CASE WHEN group_set=2 THEN
      dest_0."longitude"
      END as "longitude2__2",
    CASE WHEN group_set=2 THEN
      COUNT(1)
      END as "flight_count__2",
    CASE WHEN group_set=3 THEN
      orig_0."latitude"
      END as "latitude__3",
    CASE WHEN group_set=3 THEN
      orig_0."longitude"
      END as "longitude__3",
    CASE WHEN group_set=3 THEN
      dest_0."latitude"
      END as "latitude2__3",
    CASE WHEN group_set=3 THEN
      dest_0."longitude"
      END as "longitude2__3",
    CASE WHEN group_set=3 THEN
      COUNT(1)
      END as "flight_count__3"
  FROM '../data/flights.parquet' as base
   LEFT JOIN '../data/airports.parquet' AS dest_0
    ON base."destination"=dest_0."code"
   LEFT JOIN '../data/airports.parquet' AS orig_0
    ON base."origin"=orig_0."code"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,3,1)) as group_set  ) as group_set
  WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND base."origin"='ORD')))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND base."origin"='SFO')))
  AND ((group_set NOT IN (3) OR (group_set IN (3) AND base."origin"='JFK')))
  GROUP BY 1,2,4,5,6,7,9,10,11,12,14,15,16,17
)
SELECT
  "carrier__0" as "carrier",
  MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count",
  COALESCE(LIST({
    "latitude": "latitude__1", 
    "longitude": "longitude__1", 
    "latitude2": "latitude2__1", 
    "longitude2": "longitude2__1", 
    "flight_count": "flight_count__1"}  ORDER BY  "flight_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "ord_segment_map",
  COALESCE(LIST({
    "latitude": "latitude__2", 
    "longitude": "longitude__2", 
    "latitude2": "latitude2__2", 
    "longitude2": "longitude2__2", 
    "flight_count": "flight_count__2"}  ORDER BY  "flight_count__2" desc NULLS LAST) FILTER (WHERE group_set=2),[]) as "sfo_segment_map",
  COALESCE(LIST({
    "latitude": "latitude__3", 
    "longitude": "longitude__3", 
    "latitude2": "latitude2__3", 
    "longitude2": "longitude2__3", 
    "flight_count": "flight_count__3"}  ORDER BY  "flight_count__3" desc NULLS LAST) FILTER (WHERE group_set=3),[]) as "jfk_segment_map"
FROM __stage0
GROUP BY 1
ORDER BY 2 desc NULLS LAST