Assignment 5

a. Add Airports to Flights (15 pts)

We wish to relate the start and end locations for each flight to the airports. Create a new dataframe that has icao, flight_id, start_dt, end_dt, origin_apt, and dest_apt columns. The start_dt and end_dt columns should contain the starting and ending timestamps for each flight, while the origin_apt and dest_apt columns should contain the nearest airport to the starting and ending positions of each flight, respectively. It will be easiest to first compute the raw data corresponding to the start and end of the flight before worrying about the spatial join. In other words, create a dataframe that identifies the start_dt, start_lat, start_lon, end_dt, end_lat, and end_lon for each flight, in addition to the icao and flight_id columns.

Then, create a geopandas GeoDataFrame with the starting and ending positions as points. Whenever you create a geopandas GeoDataFrame from latitude and longitude, make sure to set the coordinate reference system (crs) to the string “EPSG:4326”. Next, you will need to compute a spatial join to find the nearest airports. You will need to do two spatial joins, one for the origin and one for the destination. Note that you must first identify the starting and ending positions for each flight. Then, use the geopandas sjoin_nearest function to find the nearest airport to each of the positions.

b. Convert Flights to LineStrings (15 pts)

In order to analyze the paths of the flights, we need to convert the lat/lon sequences for each flight into a linestring geometry (a sequence of line segments). You can do this by grouping the data by icao and flight_id and then aggregating the lat and lon columns into a list of coordinates. Then, you can use shapely’s LineString constructor to convert the list of coordinates into a linestring. Create a new GeoDataFrame that has the icao, flight_id, and this new geometry. Join this with your dataframe from Part a so that you have a single GeoDataFrame with the icao, flight_id, start_dt, end_dt, origin_apt, dest_apt, and geometry columns.

c. ATC Traffic (10 pts)

Now perform a spatial join between the flight paths and the ARTCC boundaries to determine which traffic control areas each flight passes through. You can use geopandas’ sjoin function with the 'intersects' operation to compute this. After performing the spatial join, create a new dataframe that has the icao, flight_id, and a list of the unique name values for the ATC areas that each flight passes through. The following table shows information for ten random flights. Note that the artcc_name column contains a list of ATC area names; it is displayed as a list for succinctness, not because this is the format your dataframe should be in.

a. Visualize Airports (5 pts)

Use geopandas to plot the locations of the airports. You can use the plot command on a GeoDataFrame for this.

b. Visualize Traffic (15 pts)

Now, use the atc traffic data from Part 2c to generate a plot showing the number of flights passing through each ATC area. This requires creating a new data frame by aggregating the flights by ATC area. Then, use the plot command to generate a choropleth map from the GeoDataFrame. This is done by choosing a column to serve as the value in the map and setting a colormap (cmap).

a. Bordering Areas (10 pts)

First, we need to determine a graph of ATC areas where an edge indicates that the ATC areas border each other. To do this, we will compute the spatial join of ATC areas with themselves. There are a number of operations that can be used for a spatial join, but to make sure that areas with common borders are joined, we will use 'intersects'. Make sure to get rid of pairs of the same area and deduplicate those that are listed twice.

b. Graph Database Creation (20 pts)

To begin, we will create a new graph database and add a Local DBMS to neo4j. Do this in the neo4j desktop application by creating a new project and creating a Local DBMS associated with that project. You may wish to stop other databases from running, and then start the new DBMS you just created. If you click on the newly created DBMS, you will see the IP address and Bolt port; copy the bolt url. Back in Jupyter, we now wish to connect to this DBMS. Use the following code (potentially with a different url as copied) to connect with the password you created:

from neo4j import GraphDatabase
driver = GraphDatabase.driver("bolt://localhost:7687", auth=("neo4j", "password"))

Updated 2025-11-26 Since we have less than 100 pairs of bordering ATC areas, we can insert them all at once. Construct a Cypher query to insert the nodes and relationships into the database. The UNWIND clause will take a list of 2-tuples/lists and can create the nodes and relationships.

query = """
UNWIND $batch AS edge
// add origin node
// add destination node
// add borders relationship between them
"""

with driver.session() as session:
    session.run(query, batch=edges)

Once you have created this correctly, you can visualize the graph using the Bloom tool that comes with neo4j Desktop. You can do this using the “Explore” tool.

c. Compute Paths (20 pts)

Next, we wish to use the graph database to compute the potential handoffs from the flights data. We need to compute the unique starting and ending ATC areas over all of the flights, and then we can compute the shortest path between the areas. For each flight, we wish to find the shortest path between the starting and ending ATC areas. (Note that this path is not unique.) Make sure to exclude the cases where the starting and ending ATC areas are the same, as these do not require any handoffs. From the results of the query, create a dataframe with the counts of the number of times an ATC was part of a flight’s path. Add the count of the flights that started and ended in the same ATC to this dataframe. Finally, take the flights dataframe from Part 2c and combine it with this handoffs information to get the total number of flights that would pass through each ATC area using shortest paths.

d. Visualization (5 pts)

Generate a plot showing the number of flights that would pass through each ATC based on the paths computed in Part c. This plot should look similar to Part 2b. What has changed?

Hints

• Use polars cum_sum method to compute cumulative sums.
• It may be easier to not use a true resampling here, but rather to create a date_range with the desired frequency, and then do a join with the cumulative sum data using the join_asof method. Make sure to fill in any missing values with the previous value.