Introduction

This analysis will answer the question: “Do public schools near major, congested roadways have higher student-to-teacher ratios?”

The following spatial datasets are provided to be used for analysis:

  1. Top 100 Congested Roadways in Texas: A polyline dataset representing the most congested corridors in the state, based on traffic volume and delay metrics. (https://www.arcgis.com/home/item.html?id=7f23449889f94a539a24ce4f0ac143a8)

  2. Public School Characteristics (2022–23): A point dataset of public schools in the U.S., including total student enrollment and full-time equivalent (FTE) teacher counts. (https://data-nces.opendata.arcgis.com/datasets/nces::public-school-characteristics- 2022-23/explore?location=30.672857%2C-96.687206%2C9.00)

  3. Texas County Boundary: (https://gis-txdot.opendata.arcgis.com/datasets/9b2eb7d232584572ad53bad41c76b04d_0/explore?location=30.866735%2C-100.049436%2C6.32)

The analysis will cover four tasks:

  1. Spatial Data Integration: Load and clean the datasets, then calculate the student-to-teacher ratio for each school. For each school, compute a congestion exposure metric, using one or more of the following:

    • Distance from the school to the nearest congested roadway

    • Whether the school falls within a buffer zone (e.g., 500 meters) of a congested road segment

  2. Correlation and Statistical Analysis: Analyze whether congestion exposure is associated with student-to-teacher ratios by doing one of the following:

    • Calculating correlation coefficients between exposure metrics and the student-to- teacher ratio.

    • Running a regression model to test for statistically significant relationships.

  3. Interpret the results: Do schools closer to congested corridors tend to have higher student loads per teacher? Discuss possible explanations or limitations (e.g., urban/rural context, socioeconomic patterns, data limitations).

  4. Visualization: Create an interactive Leaflet map or static map output that includes:

    • The Top 100 congested roads (as lines)

    • School locations, symbolized by student-to-teacher ratio

Analysis

Packages and globals

When coding in R, I prefer to reference packages explicitly due to the large amount of functions appearing in multiple packages. The one exception is the magrittr pipe, which I export directly for convenience.

`%>%` = magrittr::`%>%`

We’ll also set the analysis CRS as a global variable for convenience. We’ll use EPSG:3081, NAD83 / Texas State Mapping System (https://spatialreference.org/ref/epsg/3081/).

CRS = 3081

1. Spatial data integration

Data will be loaded directly from URLs at the provided links for convenience. Formatting will drop all non-relevant columns for efficiency, and will be done on read via query. All will be transformed to the analysis CRS.

# Top 100 congested roadways in Texas
congested_roadways = sf::st_read(
  dsn = paste0(
    "https://services.arcgis.com/KTcxiTD9dsQw4r7Z/arcgis/rest/services/",
    "TxDOT_Top_100_Congested_Roadways/FeatureServer/0/",
    "query?outFields=SEG_ID&where=1%3D1&f=geojson"
  ),
  quiet = TRUE
) %>%
  sf::st_transform(
    crs = CRS
  )

# Public school characteristics
schools = sf::st_read(
  dsn = paste0(
    "https://nces.ed.gov/opengis/rest/services/",
    "K12_School_Locations/EDGE_ADMINDATA_PUBLICSCH_2223/MapServer/0/",
    "query?outFields=NCESSCH,LSTATE,SCH_NAME,TOTAL,FTE,STUTERATIO&where=1%3D1&f=geojson"
  ),
  quiet = TRUE
) %>%
  sf::st_transform(
    crs = CRS
  )

# Texas county boundary
texas_counties = sf::st_read(
  dsn = paste0(
    "https://services.arcgis.com/KTcxiTD9dsQw4r7Z/arcgis/rest/services/",
    "Texas_County_Boundaries/FeatureServer/0/",
    "query?outFields=CNTY_NM&where=1%3D1&f=geojson"
  ),
  quiet = TRUE
) %>%
  sf::st_transform(
    crs = CRS
  )

For the association analysis, we only care about a subset of schools because our analysis is specific to the top 100 congested roadways in Texas. However, which subset to use is a bit of an open question. The roadways dataset is defined at the state level, so we could include all schools in the state. However, the most congested roadways are likely only within a few cities, so we could include only schools within a county that also has a top 100 most congested roadway. Alternatively, this could be generalized to schools within some fixed distance radius of the most congested roadways – e.g., 100 miles – in an effort to define the subset without the bias of existing geography.

In the absence of clear direction, we will keep all schools within Texas. I make this decision based on the Texas-wide definition of the roadways dataset; i.e., in a “null hypothesis” setting, we assume that any school in Texas could be near one of the 100 most congested segments, even though intuition might indicate where we think these roadways actually are, so we should retain all to comprehensively judge association.

So, we will drop all non-Texas schools from the data. This can be done two ways: by spatial location (using the provided Texas counties) or by attribute (using the “LSTATE” attribute of the schools data providing school location state). I’ll check both.

# Spatial
is_texas_spatial = sf::st_intersects(
  x = schools,
  y = texas_counties
) %>%
  lengths()

# Attribute
is_texas_attribute = as.integer(
  x = schools$LSTATE == "TX"
)

# Are they the same?
# all(is_texas_spatial == is_texas_attribute)
# TRUE
# They are!

# Because either method works, we'll use the attribute to filter schools, since 
# it's already in the dataset
# Note that, now armed with the information that the LSTATE attribute matches a
# spatial test, we could simply provide "LSTATE='TX'" as a WHERE clause in our
# query on read. But we've already got the data in, so a filter is fine.
texas_schools = schools %>%
  dplyr::filter(
    LSTATE == "TX"
  )

To calculate a student-to-teacher ratio, note that, like with state, the data already contains this attribute. We’ll calculate ourselves just to be sure. It also looks like the student-to-teacher ratio in the dataset is rounded to 2 decimal places, so we may prefer our own calculation anyway.

# Calculate the ratio
sttr = texas_schools$TOTAL / texas_schools$FTE

# Compare to existing data -- note that we'll drop any cases where students is
# NA, FTE is NA, or FTE == 0
# include_idx = which(
#   !is.na(texas_schools$TOTAL) &
#     !is.na(texas_schools$FTE) &
#     texas_schools$FTE != 0
# )
# w_off = which(
#   abs(round(sttr[include_idx], 2) - texas_schools$STUTERATIO[include_idx]) > 0
# )
# data.table::data.table(
#   our_calc = sttr[include_idx][w_off],
#   data_calc = texas_schools$STUTERATIO[include_idx][w_off]
# )
#    our_calc data_calc
#       <num>     <num>
# 1:   18.625     18.63
# 2:   14.125     14.13
# 3:    6.125      6.13
# 4:   17.125     17.13
# 5:   13.125     13.13
# 6:    3.125      3.13
# 7:   14.125     14.13
# 8:   15.625     15.63
# 9:   14.625     14.63
# Every case where our calculation is off is a case of midpoint rounding, which
# is likely just a difference in how unique rounders deal with 5s. So we can
# confidently say our calculation matches that already in the data

# Even though our calculation matches what is already in the data, we prefer
# our own because (1) it leaves NAs explicit rather than filling them with a
# "missing" value, and (2) it doesn't round unnecessarily. So we'll add ours
# to the data
texas_schools = texas_schools %>%
  dplyr::mutate(
    sttr = sttr
  )

To calculate a congestion exposure metric, it is suggested to either (1) measure a distance to the nearest congested feature or (2) identify if the school lies within a fixed-radius buffer of a congested feature. I prefer the fixed-radius buffer approach here because “distance” may give us misleading results in this context. If we care about the distance itself, we’d probably prefer to use a walking distance from the school to the feature, which we cannot calculate with this data (not a complete network). If we care about distance only insofar as asking if the school fronts a congested street, we also have insufficient data to make a judgement (we don’t have all features, so we cannot be sure a school fronts a particular road, even if they are very close). Given the data limitations, it is probably best to generalize to the buffer approach. This allows us to broadly assess “is the school in a congested area”, rather than tying us to the specificity of a distance that may or may not be meaningful.

For our buffer, we’ll assume that a “congested area” is anywhere within 1/4 mile of a top 100 congested segment (this radius should cover either (1) a walkable area immediately around the school, or (2) a maximum possible setback from the road for the school when taking parking into account). In addition to this radius, we’ll also add a fixed factor to account for street right-of-way, since the roadways are delivered as centerlines. Congested segments tend to be larger roads, so we’ll assume two lanes in each direction, which, with standard 12-foot lanes, corresponds to a 48-foot right-of-way. This brings our total buffer radius to 1320 + 48 = 1368 feet, or ~417 meters.

Thus, congestion exposure for schools will be defined as a binary: 1 if within 417 meters of any of the top 100 congested roadways in Texas, 0 if not.

# Calculate binary congestion exposure metric
is_congested = sf::st_intersects(
  x = texas_schools,
  y = sf::st_buffer(
    x = congested_roadways,
    dist = 417
  )
) %>%
  lengths() %>%
  magrittr::is_weakly_greater_than(1) %>%
  as.integer()

# Add back to data
texas_schools = texas_schools %>%
  dplyr::mutate(
    congested = is_congested
  )

2. Correlation and statistical analysis

We are exploring the relationship between a continuous student-to-teacher ratio and a binary congestion exposure metric. To do this, we’ll look at both a correlation coefficient and a simple linear regression.

Note that the methods explored in this section are specific to linear relationships. In other words, we’re really only looking at if student-to-teacher ratios are higher in congestion-exposed areas, or if they are lower in congestion-exposed areas. We are not exploring cases where, e.g., both the highest and lowest student-to-teacher ratios are observed in congestion exposed areas, with middling ratios occurring in low-congestion-exposure areas.

For both the calculation of a correlation coefficient and fitting of a regression model, we’ll want to drop any cases (1) where the student to teacher ratio is NULL or (2) the school location is not defined, as these constitute incomplete student-to-teacher ratio and congestion exposure values, respectively.

Because of the skewness present in our data, we’ll assess association using the natural log of student-to-teacher ratio – this will help some of the assumptions of correlation tests and simple linear regression perform better. Because we’re using log, we need to decide what to do with cases of student-to-teacher ratio = 0. We’ll drop them: it is likely anyway that “0” students is either a data error or a closed school.

# Drop relevant records
analysis_schools = texas_schools %>%
  dplyr::filter(
    !is.na(sttr) & !sf::st_is_empty(geometry) & sttr > 0
  ) %>%
  dplyr::select(
    sttr, congested
  ) %>%
  sf::st_drop_geometry()

For the correlation coefficient, we’ll use point-biserial correlation, which measures association between continuous and binary variables. Conveniently, this correlation is mathematically equivalent to the common Pearson’s correlation coefficient. Strong correlations will have a coefficient near -1 or 1; weak correlations will have a coefficient near 0.

# Point biserial, a.k.a. Pearson's correlation coefficient
# This will run a statistical test as well as give us the point estimate for
# the correlation coefficient
R = cor.test(
  x = log(analysis_schools$sttr),
  y = analysis_schools$congested
)
R
## 
##  Pearson's product-moment correlation
## 
## data:  log(analysis_schools$sttr) and analysis_schools$congested
## t = 2.6023, df = 8823, p-value = 0.009276
## alternative hypothesis: true correlation is not equal to 0
## 95 percent confidence interval:
##  0.00683344 0.04852983
## sample estimates:
##        cor 
## 0.02769368

For the regression approach, we’ll convert congestion exposure to a factor variable, and fit a simple linear regression using congestion exposure to predict student-to-teacher ratio. The hypothesis test on the parameter for congestion=1 tests if the parameter is significantly different from 0; if it is, this indicates a meaningful linear relationship.

# Fit the model
fit = lm(
  formula = log(sttr) ~ factor(congested),
  data = analysis_schools
)
summary(fit)
## 
## Call:
## lm(formula = log(sttr) ~ factor(congested), data = analysis_schools)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -5.2556 -0.0866  0.0512  0.1617  3.4194 
## 
## Coefficients:
##                    Estimate Std. Error t value Pr(>|t|)    
## (Intercept)        2.616561   0.003682 710.540  < 2e-16 ***
## factor(congested)1 0.062571   0.024044   2.602  0.00928 ** 
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.3419 on 8823 degrees of freedom
## Multiple R-squared:  0.0007669,  Adjusted R-squared:  0.0006537 
## F-statistic: 6.772 on 1 and 8823 DF,  p-value: 0.009276

To provide additional clarity, we’ll also produce a quick scatterplot:

ggplot2::ggplot(
  data = analysis_schools
) +
  ggplot2::geom_point(
    mapping = ggplot2::aes(
      x = factor(congested),
      y = log(sttr)
    )
  ) +
  ggplot2::scale_x_discrete(
    name = "Congestion exposure",
    labels = c("Not exposted (0)", "Exposed (1)")
  ) +
  ggplot2::scale_y_continuous(
    name = "Natural log of student-to-teacher ratio"
  )

3. Interpret the results

I conclude that schools closer to congested corridors do not have meaningfully different student loads per teacher than those not near such corridors. However, I should be particular about detailing this conclusion, because the statistical tests indicate some amount of “significance”. I argue that, despite this statistical significance, the practical association is effectively meaningless.

  1. The correlation coefficient of 0.03 suggests effectively no linear correlation between student-to-teacher ratio and congestion exposure. Though the statistical test for correlation indicates that correlation is “significantly different from 0”, the observed correlation has no practical significance; it is too low. The “significant” result of the test is more a function of the sample size than a meaningful correlation.

  2. The regression indicates that we should expect the natural log of student-to-teacher ratio in congestion-exposed areas to be greater than that in non-congestion-exposed areas by 0.06. Like with the correlation coefficient, the hypothesis test on the parameter is “statistically significant” – suggesting a non-zero effect – but the value itself bears little practical significance. Evaluating the regression model, we’d predict constant ratios of 13.69 in non-congestion-exposed areas and 14.57 in congestion-exposed areas (after accounting for the log transform); practically, this <1 difference carries little meaning. It is also worth pointing out that the \(R^2\) of the model is 0.00077, indicating that less than 0.01% of the variability in student-to-teacher ratio is accounted for by congestion exposure. This suggests that, despite its “non-zero” impact, congestion exposure itself is a rather poor indicator of student-to-teacher ratio.

It is worth discussing elements that could be muddying the relationship here, the most notable of which stem from our incomplete definition of “congestion exposure”. Proximity to a high-congestion feature is defined by an as-the-crow-flies distance, which is not optimal for this scenario; we’d much prefer a proper network distance that reflects true connectivity to these features to define our search radius (e.g., a school may be near a congested limited access highway, but because the school is not on the highway, it may not feel “congested” around the school). Furthermore, we are only considering a school to be “congestion exposed” if it is near to one of the top 100 most congested roadways in Texas. 100 is an arbitrary cutoff, and this does not account for varying levels of congestion, even within the top 100 group. This leads to a lack of precision in defining the association between congestion and student-to-teacher ratio.

An ideal, complete analysis would likely seek to measure congestion not by relative location (as done here) or by distance to a congested feature (as suggested in the problem statement), but instead by a known congestion metric that reflects traffic volumes, e.g., volume to capacity ratio, level of travel time reliability, or hours of vehicle delay. Given a complete set of roadways, this could be calculated and summarized for all roads bordering school property (i.e., those surrounding the point). In a regression context, the relationship between such an expression of congestion and student-to-teacher ratio could be modeled while accounting for potential lurking variables, e.g. local population size, urbanity, or median household income. This would yield more certain inference on the relationship.

4. Visualization

Per the task description, the Leaflet map below shows the top 100 most congested roadways in Texas (as lines) and school locations (as points, symbolized by student-to-teacher ratio). By hovering over school points, you can see school name, student body size, teacher FTE, and student-to-teacher ratio.

Note that I account for skewness here by capping the color ramp at 30, which is ~99.5th percentile of the data. So, all student-to-teacher ratios >30 are visualized with the same color (still, the hover labels allow viewers to see the exact value). This is done instead of using log (as we did to combat skewness in the statistical analysis) to make the map more imminently legible and interpretable.

Though the map defaults to the state of Texas, it is best viewed at the level of metro areas, as this gives the cleanest picture of school/congested roadway proximity.

# Build congested roadways dataset for mapping
map_congested_roadways = congested_roadways %>%
  # Leaflet requires lat/lon
  sf::st_transform(
    crs = 4326
  )

# Build schools dataset for mapping
map_texas_schools = texas_schools %>%
  # Create label with school information
  dplyr::mutate(
    label = paste0(
      "<b>School name:</b> ", SCH_NAME,
      "<br>",
      "<b>Students:</b> ", round(TOTAL, 2),
      "<br>",
      "<b>Teachers (FTE):</b> ", round(FTE, 2),
      "<br>",
      "<b>Student/teacher ratio:</b> ", round(sttr, 2)
    )
  ) %>%
  # Student-to-teacher ratios are very skewed, so create a "mapping" variable
  # that truncates at 30 (only 0.5% of records are > 30); this will ensure
  # that the color scale is actually legible, and anything > 30 will get the 
  # same color
  dplyr::mutate(
    sttr_map = dplyr::case_when(
      sttr > 30 ~ 30,
      TRUE ~ sttr
    )
  ) %>%
  # Select relevant columns
  dplyr::select(
    sttr_map, label
  ) %>%
  # Leaflet requires lat/lon
  sf::st_transform(
    crs = 4326
  )

# Build continuous color palette for student-to-teacher ratio
pal_sttr = leaflet::colorNumeric(
  palette = "viridis",
  domain = map_texas_schools$sttr_map
)

# Leaflet
leaflet::leaflet() %>%
  leaflet::addProviderTiles(
    provider = "Esri.WorldStreetMap",
    options = leaflet::providerTileOptions(
      opacity = 0.5
    )
  ) %>%
  leaflet::addCircleMarkers(
    data = map_texas_schools,
    radius = 3,
    stroke = FALSE,
    opacity = 1,
    color = ~pal_sttr(sttr_map),
    fillOpacity = 1,
    fill = ~pal_sttr(sttr_map),
    label = ~lapply(label, htmltools::HTML)
  ) %>%
  leaflet::addPolylines(
    data = map_congested_roadways,
    color = "red",
    weight = 6,
    opacity = 1
  ) %>%
  leaflet::addControl(
    html = "<b>Congestion and student-to-</b> <br> <b>teacher ratios in Texas</b>",
    position = "topright"
  ) %>%
  leaflet::addLegend(
    position = "topright",
    colors = "red",
    labels = "",
    title = "Top 100 most <br> congested <br> roadways in <br> Texas"
  ) %>%
  leaflet::addLegend(
    position = "topright",
    pal = pal_sttr,
    values = map_texas_schools$sttr_map,
    labFormat = function(type, cuts){
      if (type == "numeric"){
        b = as.character(cuts)
        b[b == "30"] = ">30"
        return(b)
      }
    },
    title = "Student to <br> teacher <br> ratio"
  )

Conclusion

For any questions about this analysis or my thought process, please contact me via email. Thanks for reading!