Segment Maps

VSCode

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

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": 39.85,
    "longitude2": -104.66,
    "flight_count": 8
  },
  {
    "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": 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": 42.94,
    "longitude2": -78.73,
    "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": 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": 40.78,
    "longitude2": -111.97,
    "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": 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": 37.72,
    "longitude2": -122.22,
    "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": 27.97,
    "longitude2": -82.53,
    "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": 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": 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": 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": 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": 36.12,
    "longitude2": -86.67,
    "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.99,
    "longitude2": -82.89,
    "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": 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": 29.99,
    "longitude2": -90.25,
    "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": 35.87,
    "longitude2": -78.78,
    "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": 33.67,
    "longitude2": -117.86,
    "flight_count": 1
  },
  {
    "latitude": 41.97,
    "longitude": -87.9,
    "latitude2": 32.11,
    "longitude2": -110.94,
    "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
}

QUERY RESULTS

carrier	flight_count	routes_map
UA	113
AA	77
DL	11
NW	2