What Is Malloy
Shape Maps
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:
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
run: flights -> routes_map + { where: dep_time = @2003-02 and origin = 'ORD' }[ { "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": 32.89, "longitude2": -97.03, "flight_count": 7 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.29, "longitude2": -94.71, "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.87, "longitude2": -75.24, "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": 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": 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": 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": 27.97, "longitude2": -82.53, "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": 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": 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": 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 }, { "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 } ]
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 flights LEFT JOIN '../data/airports.parquet' AS dest_0 ON flights."destination"=dest_0."code" LEFT JOIN '../data/airports.parquet' AS orig_0 ON flights."origin"=orig_0."code" WHERE ((flights."dep_time">=TIMESTAMP '2003-02-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2003-03-01 00:00:00'))and(flights."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.
run: flights -> { where: dep_time = @2003-02 and origin = 'ORD' group_by: carrier aggregate: flight_count nest: routes_map }
| carrier | flight_​count | routes_​map |
|---|---|---|
| UA | 113 | |
| AA | 77 | |
| DL | 11 | |
| NW | 2 | |
| CO | 2 |
[ { "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": 40.49, "longitude2": -80.23, "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": 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": 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": 40.78, "longitude2": -111.97, "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": 25.79, "longitude2": -80.29, "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 } ] }, { "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": 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": 33.94, "longitude2": -118.4, "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": 35.04, "longitude2": -106.6, "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": 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 } ] }, { "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": "NW", "flight_count": 2, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 2 } ] }, { "carrier": "CO", "flight_count": 2, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 2 } ] } ]
WITH __stage0 AS ( SELECT group_set, flights."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 flights LEFT JOIN '../data/airports.parquet' AS dest_0 ON flights."destination"=dest_0."code" LEFT JOIN '../data/airports.parquet' AS orig_0 ON flights."origin"=orig_0."code" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set WHERE ((flights."dep_time">=TIMESTAMP '2003-02-01 00:00:00')and(flights."dep_time"<TIMESTAMP '2003-03-01 00:00:00'))and(flights."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
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' } }
| carrier | flight_​count | ord | sfo | jfk |
|---|---|---|---|---|
| WN | 88,751 | |||
| US | 37,683 | |||
| AA | 34,577 | |||
| NW | 33,580 | |||
| UA | 32,757 |
[ { "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": 42.94, "longitude2": -78.73, "flight_count": 41 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "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": 38.5, "longitude2": -107.89, "flight_count": 5 }, { "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.69, "longitude2": -121.59, "flight_count": 3 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.3, "longitude2": -95.89, "flight_count": 2 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.67, "longitude2": -117.86, "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": 38.85, "longitude2": -77.03, "flight_count": 408 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.94, "longitude2": -118.4, "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": 38.8, "longitude2": -104.7, "flight_count": 15 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.04, "longitude2": -84.66, "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": 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": 34.05, "longitude2": -117.6, "flight_count": 2 }, { "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 }, { "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 } ], "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": 39.17, "longitude2": -76.66, "flight_count": 50 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.89, "longitude2": -97.03, "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": 42.36, "longitude2": -71, "flight_count": 6 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 37.61, "longitude2": -122.37, "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, flights."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 flights LEFT JOIN '../data/airports.parquet' AS dest_0 ON flights."destination"=dest_0."code" LEFT JOIN '../data/airports.parquet' AS orig_0 ON flights."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 flights."origin"='ORD'))) AND ((group_set NOT IN (2) OR (group_set IN (2) AND flights."origin"='SFO'))) AND ((group_set NOT IN (3) OR (group_set IN (3) AND flights."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