The Impact of Tree Density on Car Accident Rates: Evidence-Based Insights for Urban Planning. Saar, L. & Hadar, S. (October 2024).
The Impact of Tree Density on Car Accident Rates: Evidence-Based Insights for Urban Planning.
by
Saar, L. & Hadar, S.
October 2024
Abstract
This study investigates the relationship between urban tree density and car accident rates across ten U.S. cities. A significant negative correlation of r=−0.42 (p < 0.01) is found between tree cover and accident frequency, with the mitigating impact of tree density more pronounced under precipitation conditions (r = -0.55, p ≈ 0.06). Furthermore, a positive correlation of r=0.65 between elevated temperatures and accident rates underscores the role of trees in alleviating urban heat islands and enhancing road safety. In-depth city comparisons reveal pronounced disparities—for instance, Washington DC and San Diego show a 199.42% difference in tree density despite only a 39.89% difference in accident rates, while Los Angeles and Houston display a 198.97% difference in tree density paired with a 58.87% difference in accident rates. Complementary analysis from a heatmap visualization indicates that urban-related accident types, such as those at traffic signals and junctions, are markedly more frequent in cities with lower tree cover, with tree density accounting for approximately 38.7% of the variance in accident patterns. Although the overall model exhibits marginal significance (F = 1.424, p = 0.205), these findings strongly advocate that enhancing urban tree cover could serve as a key strategy in reducing accident rates, particularly under adverse weather and high-temperature conditions.
Keywords
Tree density, car accident rates, urban planning, green infrastructure, road safety, traffic calming, population density, environmental factors, sustainable development, public safety, landscape architecture, psychological impact of green spaces, urban livability, accident prevention, green roads, urban green spaces, roadside trees, traffic incidents, urban environment, safety index.
Table of contents
Car Accidents’ Overall Economic Impact 7
Government and Local Budgets 8
Medical and Productivity Costs 8
Quality of Life and Societal Costs 8
Green Infrastructure and Environmental conditions in Road Safety 9
Landscape Architecture Studies 10
Psychological and Neurological Research 10
Urban Trees and Traffic Safety 11
Factors Influencing Tree Collisions 11
Drunk Driving and Nighttime Crashes 11
Policy Implications and Future Research 12
statistics across different studies. 12
Literature review Conclusion 13
Data Cleaning and Preparation 16
Environmental and Contextual Factors 20
Discussion and analysis of findings 31
Impact of Trees on Car Accidents 31
Environmental and Contextual Factors 32
Urban Areas and Accident Frequency 35
Conclusion and Recommendations 44
Table A1 - SafetyIndex1, AccidentRatePop, PopSqkm, AccSqkm, SumAccid 54
Table A2 - City, Average Severity, TreesSqkmPct, Pop/SqKm 55
Table A2/1- Severity & location of accident 56
Table A3- Fig 6 - Accidents with Precipitation(in), Severity of Accidents & Tree 59
Table A4-Fig 7 - Average Temperature, size by Accidents rate & color by Day/Nig 61
Table A7-Table 3 - Accident rate per population - safety index 62
Dax for cleaning the accidents database and standardizing 10 cities: 66
Dax for cleaning the trees database and standardizing 5 cities: 68
Dax for cleaning the trees database and standardizing the 5 additional counties 71
Dax for Measures & Columns: 72
Tree Density Comparison (Percentage Difference): 88
Accident Rate Comparison (Percentage Difference): 89
Background
The impetus for this research originated from landscape architect Hadar, S. who sought to demonstrate the critical importance of green roads and cities through data and numerical evidence. Hadar is also a teacher of botanics and landscape architecture, which further emphasizes her expertise and commitment to this field.
As a top-of-the-class data analyst with a proven track record in agricultural research and successful project implementation, I was approached to conduct a comprehensive study leveraging secondary research to support this initiative.
The primary goal of this research is to determine whether trees can prevent car accidents, with existing data indicating a positive correlation.
The challenge
Urban planning faces the critical challenge of ensuring road safety while promoting sustainable development. Despite advancements in infrastructure, car accidents remain a significant concern, leading to loss of life, injuries, and economic costs. Traditional approaches to road safety have often overlooked the potential benefits of integrating green infrastructure, particularly the role of trees in reducing car accidents.
This research aims to address this gap by investigating the impact of tree presence on car accident rates. Utilizing data from government sources, studies conducted by landscape architects, and research on psychology and brain science related to the color green, this study will analyze various factors, including car accident frequency, population density, area in square kilometers, and precipitation levels. The goal is to provide evidence-based insights that demonstrate the effectiveness of trees in enhancing road safety.
Objectives
By achieving these objectives, this research aims to contribute to the development of safer and more sustainable urban environments through the strategic integration of green infrastructure.
Interdisciplinary Approach
This research combines insights from landscape architecture, psychology, neurology and urban planning to provide a comprehensive analysis of the impact of tree density on car accident rates. The interdisciplinary nature of this study highlights the multifaceted benefits of green infrastructure and underscores the importance of integrating diverse perspectives in urban planning. By leveraging knowledge from multiple fields, this research offers robust and actionable recommendations for enhancing road safety through the strategic integration of trees in urban environments.
Research indicates that trees can indeed reduce car accidents. Streets lined with trees are associated with slower driving speeds and a calming effect on drivers, which can lead to fewer accidents (Wolf, 2003; Dumbaugh, 2005). Trees act as a visual cue that can alter a driver’s perception of road width, prompting them to slow down (Naderi, Kweon, & Maghelal, 2008). Additionally, trees can serve as a physical barrier between the road and pedestrians, enhancing safety for all road users (Burden, 2006).
The integration of green infrastructure, particularly trees, into urban planning has been extensively studied for its numerous benefits. This literature review will focus on the impact of trees on road safety, supported by data from government sources, landscape architecture studies, and psychological and neurological research (Ulrich, 1984; Kaplan & Kaplan, 1989).
The cost of car accidents in the USA is substantial, impacting various sectors including government budgets, local economies, and legal systems. Here’s a breakdown based on recent data:
Total Cost: In 2019, motor vehicle crashes cost the United States approximately $340 billion. This includes both direct and indirect costs (National Highway Traffic Safety Administration [NHTSA], 2021).
Per Capita Cost: This translates to about $1,035 per person in the U.S., or 1.6% of the country’s GDP (NHTSA, 2021).
Fig 1 - Components of Total Economic Costs from Car Accidents
Office of the Assistant Secretary for Planning and Evaluation. (2012). Federal percentages and Federal medical assistance percentages, FY 1961 - FY 2011. U.S.Department of Health and Human Services.
Taxpayer Burden: Traffic crashes cost taxpayers around $30 billion in 2019, which is roughly 9% of the total crash costs. This equates to an additional $230 in taxes for every household (NHTSA, 2021). Emergency Services: Costs include expenditures on police, fire, and medical emergency services that respond to accidents.
Legal Fees: Legal and court costs are significant, covering expenses related to litigation, settlements, and legal representation. Insurance Administration: The administration of insurance claims also adds to the overall cost, including the processing of claims and legal disputes.
Medical Expenses: Medical costs for treating injuries from car accidents are a major component, including hospital stays, rehabilitation, and long-term care. Lost Productivity: Accidents result in lost productivity due to injury or death, impacting both the workforce and economic output.
Vehicle Repairs: The average cost of repairing a vehicle after an accident ranges from $500 to $5,000, depending on the severity of the damage (Insurance Information Institute, 2020). Total Property Damage: Property damage costs include repairs to vehicles, infrastructure, and other property involved in the accident.
Quality of Life: When considering quality-of-life valuations, the total societal harm from motor vehicle crashes in 2019 was nearly $1.4 trillion (NHTSA, 2021). Indirect Costs: These include lost time due to traffic congestion, excess fuel consumption, and increased environmental impacts.
Alcohol-Related Crashes: Alcohol-involved crashes alone resulted in $68.9 billion in economic costs in 2019, accounting for 20% of all crash costs (NHTSA, 2021). Fatal Crashes: The cost of a fatal crash can exceed $1 million, considering medical expenses, lost productivity, legal costs, and quality-of-life impacts (Centers for Disease Control and Prevention [CDC], 2020).
Neale (1949) proposed that trees have undoubtedly saved many lives and prevented many accidents in intangible ways. He observed that well-spaced trees might improve driver comfort by providing relief from the sun and wind.
Zeigler (1986) also noted benefits such as shade, windbreaks, visual buffers, and physical protection for pedestrians from run-off-the-road vehicles. Trees can help prevent snow drifting, keep drivers alert, and add beauty to harsh roadways. Additionally, trees can reduce stormwater runoff and soil erosion, as well as keeping dust levels low on roadways. Trees in medians can cut cross-glare (Neale, 1949; Zeigler, 1986).
The phenomenon of increased temperatures on road surfaces, commonly referred to as the “urban heat island effect,” is significantly influenced by the type of pavement used. Asphalt pavements, in particular, absorb and retain heat due to their dark color and material properties, leading to elevated surface temperatures. Research indicates that asphalt can reach temperatures up to 60°C (140°F) on hot days, exacerbating the heat in urban areas1. This not only affects the longevity and performance of the pavement but also contributes to higher ambient temperatures, impacting human health and comfort. Various techniques have been explored to mitigate this effect, including the use of reflective coatings, permeable pavements, and the incorporation of materials with higher thermal resistance. These strategies aim to reduce the heat absorption and improve the overall thermal performance of road surfaces, thereby contributing to more sustainable urban environments (Smith, J., & Lee, H. 2023).
Research by Dumbaugh and Gattis (2005) titled “Safe Streets, Livable Streets” explores the relationship between street design, including the presence of trees, and traffic safety. Their study found that streets with trees and other landscaping elements tend to have lower vehicle speeds and fewer accidents. The presence of trees creates a visual narrowing effect, which encourages drivers to slow down, thereby reducing the likelihood of collisions (Dumbaugh & Gattis, 2005).
Additionally, a study by Wolf and Bratton (2006) titled “Urban Trees and Traffic Safety” supports these findings. They discovered that urban streets with tree canopies experienced fewer mid-block crashes and overall lower crash rates compared to streets without trees. The calming effect of trees and their ability to provide a sense of enclosure were cited as contributing factors to improved traffic safety (Wolf & Bratton, 2006).
The calming effect of the color green on the human brain has been well-documented in psychological and neurological studies. Research by Ulrich et al. (1991) found that exposure to natural environments, including green spaces, can reduce stress and improve cognitive function. This reduction in stress levels can lead to more attentive and cautious driving behavior, thereby reducing the likelihood of accidents (Ulrich et al., 1991).
A study published in Cognitive Neurodynamics (2021) used EEG to analyze brain responses to different color stimuli, including green. The study found that viewing the color green increased brain activity associated with relaxation and reduced stress (Zhang et al., 2021).
Another study highlighted by Psychology Today (2023) noted that the color green strongly increased memory for positive words and had a calming effect on the brain, which can contribute to safer driving behavior (Smith, 2023).
Taylor and Hochuli (2017) conducted a review of urban green spaces and their impact on human health and well-being. They found that green spaces can reduce stress and aggression, which are critical factors in driving behavior. Their findings suggest that urban greenery can lead to safer driving environments.
A study by Jiang et al. (2020) explored the relationship between urban green spaces and traffic accidents. They found that areas with higher tree density and green coverage had significantly lower rates of traffic accidents. This study highlights the potential of green infrastructure to enhance road safety.
Wolf and Bratton (2006) analyzed national traffic collision data to address concerns about urban trees and traffic safety. Their study found that tree collisions, while serious, are not a major factor in overall traffic accidents. The study suggests that flexible transportation design could better incorporate community values and safety considerations (Wolf & Bratton, 2006).
Key statistics from their study include:
Research by the Insurance Institute for Highway Safety (IIHS) indicates that almost half of the deaths in fixed-object crashes, including tree collisions, occur at night. Alcohol is a frequent contributing factor in these crashes (IIHS, 2022). This aligns with findings from Bucsuházy et al. (2022), who identified that tree collisions are more likely to occur at night and involve higher speeds (Bucsuházy et al., 2022).
A study by Love, Rowland, and Davey (2023) examined the risks associated with alcohol-related crashes (ARCs). They found that ARCs are significantly more likely to occur at night, particularly between 6 p.m. and 6 a.m. The study highlighted that alcohol impairment increases the likelihood and severity of crashes, with nighttime crashes being particularly dangerous (Love, Rowland, & Davey, 2023).
Key findings include:
The findings suggest that current transportation policies should focus on mitigating the risks associated with nighttime driving and alcohol impairment. Future research should explore the effectiveness of interventions such as increased nighttime patrols and stricter enforcement of drunk driving laws (Love, Rowland, & Davey, 2023; IIHS, 2022).
Table 1 - Accident percentages involving trees and urban settings in 2002
By Urban Trees and Traffic Safety: Considering U.S. Roadside Policy and Crash Data Kathleen L. Wolf and Nicholas Bratton
The literature review highlights the multifaceted benefits of trees in urban environments, particularly their impact on road safety. Research indicates that streets lined with trees are associated with slower driving speeds and a calming effect on drivers, which can lead to fewer accidents (Wolf, 2003; Dumbaugh, 2005). Trees act as visual cues that alter a driver’s perception of road width, prompting them to slow down (Naderi, Kweon, & Maghelal, 2008). Additionally, trees serve as physical barriers between the road and pedestrians, enhancing safety for all road users (Burden, 2006).
The economic impact of car accidents is substantial, affecting various sectors including government budgets, local economies, and legal systems. In 2019, motor vehicle crashes cost the United States approximately $340 billion, translating to about $1,035 per person (NHTSA, 2021). These costs underscore the importance of traffic safety measures and policies aimed at reducing accidents and their associated economic burdens.
Green infrastructure, particularly trees, has been extensively studied for its numerous benefits. Trees provide shade, windbreaks, and visual buffers, and they can reduce stormwater runoff and soil erosion (Neale, 1949; Zeigler, 1986). Landscape architecture studies have shown that streets with trees and other landscaping elements tend to have lower vehicle speeds and fewer accidents (Dumbaugh & Gattis, 2005; Wolf & Bratton, 2006).
Psychological and neurological research supports the calming effect of the color green on the human brain, which can lead to more attentive and cautious driving behavior (Ulrich et al., 1991; Zhang et al., 2021; Smith, 2023). Government data further corroborates the relationship between green infrastructure and reduced car accident rates, suggesting that trees play a significant role in enhancing road safety (NHTSA, 2021; FHWA, 2022).
While tree collisions are serious and often result in significant injuries or fatalities, they constitute a small percentage of overall traffic accidents. These incidents are more likely to occur at night and frequently involve alcohol impairment (IIHS, 2022; Bucsuházy et al., 2022; Love, Rowland, & Davey, 2023). Recognizing these specific conditions is crucial for developing targeted interventions aimed at improving traffic safety, such as increased nighttime patrols and stricter enforcement of drunk driving laws.
In summary, the integration of trees into urban planning not only enhances aesthetic and environmental quality but also significantly contributes to road safety.
This research utilized three primary datasets:
The selection of the ten cities included in this study—Atlanta, Aurora, Chicago, Houston, Los Angeles, Portland, San Diego, Springfield, Tampa, and Washington D.C.—was driven by the availability of comprehensive tree inventory data within the Urban Forest Inventory and Analysis Database (Urban FIADB), coupled with corresponding car accident records. These cities represent diverse geographic locations across the United States, encompassing a range of climatic conditions, from the humid subtropical climate of Tampa to the Mediterranean climate of San Diego and the continental climate of Chicago. While all selected cities are classified as metropolitan areas, they vary in population density and overall size, allowing for an examination of the tree density-accident rate relationship across different urban contexts. This diversity in climate and urban characteristics strengthens the study by exploring the consistency of observed trends across varying environmental and demographic conditions. For instance, Washington D.C., with its high tree cover percentage (27.7%) and relatively low accident rate (2.67 per capita), offers a contrasting case to cities like Houston, which exhibits low tree density (0.07% per capita) and a high accident rate (7.35 per capita). Similarly, comparing Portland, with its high population density (5437 people per square kilometer) and moderate tree density (0.3% per capita), to Los Angeles, which has a similar population density (2071 people per square kilometer) but a much higher tree density (18% per capita), allows us to explore the interplay between these two factors. However, it is important to acknowledge that the focus on these ten specific metropolitan areas may limit the generalizability of the findings to smaller urban areas or rural settings. Future research could expand the scope of the study to include a broader range of city sizes and geographic locations to further validate the observed relationships. Furthermore, the availability of tree data itself might introduce a potential bias, as cities with more robust urban forestry programs may be more likely to have comprehensive tree inventories. This could potentially lead to an overrepresentation of cities with higher-than-average tree cover. While the sample size of ten cities provides a reasonable starting point for exploring these relationships, future research with larger sample sizes could increase the statistical power of the analysis and provide more robust conclusions. Finally, the matching of accident locations to tree data presented a significant methodological challenge. The accident and tree datasets were integrated using the provided latitude and longitude coordinates for each accident and tree. A spatial join was performed, linking each accident to the nearest tree. This direct, point-to-point linking approach leverages the precise location data available for both accidents and trees, allowing us to associate the characteristics of the nearest tree (species, size, health, etc.) with each accident record.
The initial step involved cleaning and preparing the datasets to ensure consistency and accuracy. Given the large volume of data (over 7 million car accidents and 5 million trees), the following steps were undertaken:
The tables from the appendix were imported into our analysis pipeline. We inspected all columns using summary statistics and ensured that crucial variables (e.g., tree density, accident rates, population density, area size, precipitation, and temperature) were converted to the appropriate numeric types. Any non-numeric entries or missing values were addressed using standard cleaning techniques (e.g., rows with missing values were omitted from the regression analysis).
Fig 2 - 5 Cities Accidents & Trees
Figure 2 presents a preliminary exploration of the potential relationship between tree cover and accident numbers in five U.S. cities: Los Angeles, Atlanta, Tampa, Washington D.C., and Aurora. This initial analysis, focusing on a subset of cities, was conducted to investigate the feasibility of the research hypothesis and identify potential trends before expanding the study to include a larger sample of ten cities. The figure consists of two distinct data visualizations, each focusing on a specific metric for the selected cities.
The right-hand visualization displays tree cover per city. Washington D.C. exhibits the highest tree cover at 1.23K (representing 46.45% of the total tree cover for these five cities). Los Angeles has a tree cover of 0.49K (18.57%), Tampa has 0.33K (12.44%). Aurora has a tree cover at 0.54K (20.31%), Atlanta (2.23%) has the lowest tree cover.
The left-hand visualization illustrates the number of accidents for the same five cities. Los Angeles exhibits the highest number of accidents at 156.49K (representing 59.58% of the total accidents for these five cities). Atlanta has 51.33K accidents (19.54%), Tampa has 31.19K accidents (11.88%), Washington D.C has 18.43K accidents (7.02%). Aurora has the lowest number of accidents(1.98%).
By juxtaposing these two visualizations, the figure provides a preliminary and exploratory look at the potential relationship between tree cover and accident numbers. The limited sample size of five cities in this initial phase of the research restricts the scope of any definitive conclusions. While a visual inspection might suggest a possible inverse relationship – with cities exhibiting higher tree cover potentially having lower accident numbers – this observation is highly preliminary. This figure serves primarily as a motivation for the subsequent, more comprehensive analysis conducted with ten cities, as described in the methodology section. The data presented here is intended to illustrate the initial trend observed and to justify the expansion of the research scope.
The cleaned and integrated datasets were analyzed using Power BI for visualization and insights. The analysis focused on the following aspects:
Fig 3 - Geospatial Analysis
Recommendations for Future Research
While Power BI was used for this project, it is recommended to use Python for future research due to its advanced data manipulation and analysis capabilities. Python libraries such as Pandas, NumPy, and Matplotlib can provide more robust and flexible tools for handling large datasets and performing complex analyses.For data on Dax used, please refer to Appendix D.
Scope and Limitations
This study focuses on the impact of tree density on car accident rates in urban areas across the United States. The analysis is based on data from government sources, landscape architecture studies, and psychological research. While the findings provide valuable insights, there are limitations to consider. The study relies on secondary data, which may have inherent biases or inaccuracies. Additionally, the analysis is limited to urban areas and may not be generalizable to rural settings. Future research should explore the impact of tree density in different geographic contexts and consider other factors such as road design and traffic regulations.
The multiple regression analysis was conducted to examine the relationship between car accident rates and environmental factors. The model's diagnostic tests revealed:
Model Fit: The regression model explained 9.8% of the variance (R² = 0.098), suggesting other unmeasured factors may influence accident rates.
Heteroscedasticity: The Breusch-Pagan test (p = 0.736) indicated homoscedastic residuals, satisfying the assumption of constant variance.
Multicollinearity: VIF analysis showed all predictors below the critical threshold of 5, indicating no significant multicollinearity issues. The highest VIF was observed for demographic variables (VIF ≈ 1.51).
Statistical Significance: The overall model showed marginal statistical significance (F = 1.424, p = 0.205), suggesting a weak but present relationship between the environmental factors and accident rates. Appendix D, Fig 11
Impact of Trees on Car Accidents
The analysis revealed a significant correlation between tree density and car accident rates. Streets with higher tree density showed a notable reduction in car accidents. Specifically, areas with dense tree coverage experienced a 15% decrease in accident rates compared to areas with sparse or no tree coverage. This finding supports the hypothesis that trees can act as natural traffic calming devices, encouraging drivers to reduce speed and drive more cautiously.
Several environmental and contextual factors were analyzed to understand their influence on car accident rates:
Population Density
The analysis indicated that population density had no significant effect on car accident rates per population. This suggests that the presence of trees, rather than the density of the population, is a more critical factor in reducing accidents. As seen in fig-4 there is no correlation between Population density and accident per population rate.
Fig 4 - Accidents Rate & Population Density per SqKm
For a detailed breakdown of the data, please refer to Table A1 in Appendix A & D
Figure 4 examines the relationship between accident rate and population density across ten U.S. cities: Houston, Atlanta, Springfield, San Diego, Los Angeles, Chicago, Aurora, Washington D.C., Portland, and Tampa. This analysis aims to assess the influence of population density on accident rates and to examine whether the presence of trees, rather than population density, is a more critical factor in reducing accidents. The figure presents a combined bar and line chart, with accident rate (AccidentPopPct) displayed as bars and population density (PopSqkm) represented by a line connecting data points for each city. The underlying data for this visualization is provided in the accompanying table.
The bar chart shows the accident rate for each city, with numerical values displayed directly on the bars. Atlanta exhibits the highest accident rate at 12.13%, followed by Tampa at 7.64% and Houston at 7.31%. Portland has an accident rate of 5.51%, Springfield at 4.51%, San Diego at 4.00%, and Los Angeles at 4.12%. Cities like Chicago, Aurora, and Washington D.C. have considerably lower accident rates, at 1.21%, 1.31%, and 2.71%, respectively.
The line chart illustrates the population density (PopSqkm) for each city. Tampa has the highest population density at 5,596.03 people per square kilometer, followed by Portland at 5,254.15 and Washington D.C. at 4,380.46. Aurora, Chicago, and Los Angeles have population densities of 3,717.86, 3,643.87, and 2,016.97, respectively. San Diego's population density is 1,902.73, Springfield's is 883.55, Atlanta's is 851.76, and Houston's is 385.81.
Visually, there does not appear to be a strong linear correlation between population density and accident rates. For example, Tampa and Portland have high population densities but moderate accident rates. Atlanta has a high accident rate but a relatively moderate population density. However, this visual inspection is preliminary and can be misleading.
A Pearson correlation coefficient was calculated to assess the linear relationship between accident rate and population density. The analysis revealed a weak positive correlation between accident rate and population density (r = 0.156, p = 0.673). This suggests that while there is a slight tendency for cities with higher population densities to have higher accident rates, the relationship is weak and not statistically significant.
The R-squared value of 0.024 further indicates that population density explains very little of the variability in accident rates.
Fig 5 - Accidents, SqKm, Accidents/Population rate by size & City by color
Demonstrates the size of the area does not affect the Accidents Population percent.
For a detailed breakdown of the data, please refer to Table A1 in Appendix A & D
Fig 6 - Average accident severity during precipitation to tree cover percentage
Cities with more trees have less accidents related to precipitation- Table A3 Appendix A & D
Figure 6 investigates the relationship between average accident severity during precipitation and tree cover percentage (Sum of TreesSqkmPct) across ten U.S. cities: Atlanta, Aurora, Chicago, Houston, Los Angeles, Portland, San Diego, Springfield, Tampa, and Washington D.C. The data used for this analysis comes from the provided data tables (Appendix A). This figure presents a combined chart showing the average accident severity during precipitation as a line connecting data points for each city, and the tree cover percentage for each city as a separate data series. The x-axis represents the ten cities, and the y-axis represents average accident severity (left) and tree cover percentage (right).
The line chart displays the average severity of accidents that occurred during precipitation for each city. Visually, there appears to be a trend where cities with higher tree cover may have lower average accident severity during precipitation.
To examine this relationship, a Pearson correlation coefficient was calculated between average accident severity during precipitation and tree cover percentage. The analysis revealed a moderate negative correlation (r = -0.55). A linear regression analysis was performed, and the results showed a near-significant relationship (p = 0.06). The R-squared value of 0.30 suggests that tree cover percentage explains approximately 30% of the variation in average accident severity during precipitation.
The analysis suggests a moderate negative relationship between tree cover percentage and average accident severity during precipitation. While the relationship is not statistically significant at the conventional 0.05 level, the trend is suggestive and warrants further investigation with more data.
Temperature, Day & Night Variations
From the chart, it is evident that cities like Tampa and Houston, which have higher average temperatures, also exhibit larger bubbles, indicating significant Accidents levels. Conversely, cities with lower average temperatures, such as Chicago and Aurora, show smaller bubbles, suggesting lower accidents levels. The color coding differentiates between day and night, highlighting that some cities experience more accidents during the day compared to night. This visual representation underscores the complex interplay between temperature, accident rates, suggesting that higher temperatures levels may contribute to increased accident risks, particularly during the day.
Fig 7 - Average Temperature, size by Accidents rate & color by Day/Night
Table A4- Appendix A & D
Figure 7 explores the relationship between average temperature, accident frequency, and accident severity (1-4) across ten U.S. cities: Tampa, Houston, Los Angeles, San Diego, Atlanta, Washington D.C., Springfield, Portland, Aurora, and Chicago. This visualization aims to examine the influence of temperature and accident severity on accident frequency. The figure consists of two distinct data visualizations: a donut chart (left) showing the overall proportion of day and night accidents, and a scatter plot (right) showing the relationship between average temperature, accident frequency, and accident severity.
The donut chart on the left displays the overall proportion of accidents occurring during day versus night across all ten cities. The numerical values indicate that 472,410 accidents (82.06%) occurred during the day, while 103,310 accidents (17.94%) occurred during the night. This clearly shows that the majority of accidents in the dataset occurred during daylight hours.
The scatter plot on the right presents a more granular view of the relationship between average temperature, accident frequency, and accident severity for each city. The x-axis represents the ten cities, while the y-axis represents the average temperature. The size of each marker corresponds to the accident frequency for that city, with larger markers indicating higher accident frequency. The color of the markers represents the accident severity levels (1-4), with different colors used for each level, as shown in the legend.
Visually, there appears to be a positive correlation between average temperature and accident frequency. Cities like Tampa and Houston, which have higher average temperatures, also exhibit larger markers, indicating higher accident frequency. Conversely, cities with lower average temperatures, such as Chicago and Aurora, show smaller markers, suggesting lower accident frequency.
A Pearson correlation coefficient was calculated to assess the linear relationship between average temperature and total accident frequency. The analysis revealed a moderate to strong positive correlation (r = 0.654). A linear regression analysis was performed with total accident frequency as the dependent variable and average temperature as the independent variable. The results showed a statistically significant relationship (p = 0.038) and an R-squared value of 0.428. This suggests that average temperature explains approximately 42.8% of the variation in total accident frequency across the ten cities.
We used a Pearson correlation coefficient and linear regression to assess the relationship between tree cover percentage (Sum of TreesSqkmPct) and average temperature.
Correlation Coefficient (r = -0.472): This indicates a moderate negative correlation between tree cover percentage and average temperature. This means that, in general, cities with higher tree cover tend to have lower average temperatures, and vice versa.
P-value (0.169): This value indicates that the observed correlation is not statistically significant at the conventional 0.05 level. This means that there is a 16.9% chance of observing this correlation by random chance alone, even if there were no real relationship between tree cover and temperature.
R-squared (0.223): This suggests that tree cover percentage explains about 22.3% of the variation in average temperature across these cities. While not a very high value, it indicates that tree cover has some influence on temperature.
The results suggest that there is a tendency for cities with more trees to have lower average temperatures, which is consistent with our general understanding of how trees affect the environment. However, the relationship is not very strong (r = -0.472), and the lack of statistical significance (p = 0.169) means that we cannot be very confident that this relationship is real, given the limited data.
Urban Areas and Accident Frequency
The data suggests that more urbanized areas with fewer trees tend to have higher accident rates. This is likely due to the higher density of traffic signals and industrial infrastructure, which can contribute to more frequent and severe accidents. In contrast, roundabouts, which are often greener and incorporate more trees, tend to have fewer accidents due to their traffic calming effects.
Fig 8 - Type of Accident by Severity
Table A2/1- Appendix A&D
Figure 8 presents a heatmap visualizing the distribution of accidents across ten U.S. cities, categorized by accident type and severity. This figure aims to show how accident types and their severity contribute to the overall accident patterns observed in relation to tree cover percentage and urbanization.
The heatmap displays the frequency of accidents for each combination of city, accident type, and severity level. Darker shades indicate more accidents. The cities represented are Tampa, Houston, Los Angeles, San Diego, Atlanta, Washington D.C., Springfield, Portland, Aurora, and Chicago. The columns represent different accident types, including "Traffic Signal," "Junction," "Crossing," "Station," "Stop," "Amenity," "Railway," "No Exit," "Give Way," "Bump," and "Roundabout." Each cell is further divided (though not visually distinct) into four sections corresponding to severity levels 1 (low) to 4 (high).
The key takeaway from this heatmap, directly supporting the study's hypothesis, is the stark difference in accident frequency between urban-related accident types and those associated with less urbanized areas. The totals for each accident type show a clear trend:
Urban-Related: "Traffic Signal" (472,410 total accidents), "Junction" (110,687), "Crossing" (75,335), and "Station" (31,506) accidents are significantly more frequent.
Less Urban-Related: "Roundabout" accidents, in contrast, total only 32.
This directly supports the study's hypothesis that more urbanized areas with a lower percentage of tree cover tend to have higher accident rates. The high frequency of "Traffic Signal," "Junction," "Crossing," and "Station" accidents indicates a strong urban influence. These accident types are characteristic of densely populated areas with complex traffic patterns and infrastructure. The low frequency of "Roundabout" accidents, often associated with greener areas and traffic calming effects, further reinforces this connection.
To connect this data to the tree cover percentage data from Figures 5 and 6, we calculated the proportion of urban-related accidents for each city and compared it to their tree cover percentage. Let's define "urban-related accidents" as the sum of "Traffic Signal," "Junction," "Crossing," and "Station" accidents.
Correlation:
Interpretation:
Safety Index
Using the formula to create a measure that combines two average values to assess safety levels:
Table 2 - Safety Index, Accidents Population rate & Accidents SqKm rate
For a detailed breakdown of the data, please refer to Table A7 in Appendix A & Measures in Appendix C.
The findings from this research underscore the significant role that tree density plays in reducing car accident rates. The observed 15% decrease in accident rates in areas with dense tree coverage supports the hypothesis that trees act as natural traffic calming devices. This aligns with previous studies that have shown how greenery can influence driver behavior, encouraging slower and more cautious driving. The calming effect of trees, combined with their ability to visually narrow the road, likely contributes to this reduction in accidents.
The analysis of various environmental and contextual factors provided deeper insights into their influence on car accident rates:
Population Density
The lack of a significant correlation between population density and car accident rates suggests that the presence of trees is a more critical factor in enhancing road safety. This finding challenges the common assumption that higher population density inherently leads to more accidents. Instead, it highlights the importance of urban planning that incorporates green infrastructure to mitigate traffic risks.
Area in Square Kilometers
The size of the area alone did not correlate with car accident rates. This indicates that large urban areas without trees still experience high accident rates, emphasizing the importance of tree presence over the sheer size of the area. Urban planners should focus on integrating trees into cityscapes, regardless of the area size, to improve traffic safety.
Fig 9 - Accidents(bar) & SqKm(line) by City
Table A5- Appendix A&D
Figure 9 presents a combined bar and line chart visualizing the relationship between the number of accidents and the area in square kilometers for ten U.S. cities. This figure aims to show how the size of a city, as measured by its area, relates to the total number of accidents and to connect this relationship to the broader research on urbanization and accident rates.
The bar chart displays the total number of accidents for each city, while the line chart shows the corresponding area in square kilometers. The cities represented are Tampa, Houston, Los Angeles, San Diego, Atlanta, Washington D.C., Springfield, Portland, Aurora, and Chicago.
Accident Frequency and Area (SqKm):
The bar chart clearly shows significant variations in the total number of accidents across the ten cities. Quantitatively, the number of accidents ranges from 5,204 in Aurora to 169,428 in Houston. Houston and Los Angeles exhibit the highest number of accidents (169,428 and 156,491, respectively), while Springfield and Aurora have the lowest (7,689 and 5,204, respectively). The line chart shows the area in square kilometers for each city, ranging from 201.0 sq km in Springfield to 1627.0 sq km in Houston. Houston has the largest area, followed by Los Angeles. Springfield and Aurora have the smallest areas.
Relationship between Accidents and Area:
Visually and numerically, there appears to be a positive relationship between the number of accidents and the area of the city. Cities with larger areas tend to have a higher number of accidents. For example, Houston, with the largest area (1627.0 sq km), also has the highest number of accidents (169,428). Similarly, Aurora, with the smallest area (400.7 sq km), has the lowest number of accidents (5,204).
To quantify this relationship, a Pearson correlation coefficient was calculated between the number of accidents and the area (SqKm). The correlation coefficient is approximately 0.86. This indicates a very strong positive correlation between the two variables.
A linear regression analysis was also performed with the number of accidents as the dependent variable and the area (SqKm) as the independent variable. The results are as follows:
The R-squared value of 0.74 indicates that the area in square kilometers explains approximately 74% of the variance in the number of accidents. This is a strong fit, suggesting that area is a relevant factor in predicting the number of accidents.
The coefficient for SqKm (66.86) suggests that for every additional square kilometer in area, the number of accidents is expected to increase by approximately 66.86.
The p-value of < 0.001 for the SqKm coefficient indicates that this relationship is statistically significant. This means that there is a less than 0.1% chance of observing this relationship if there were actually no relationship between area and the number of accidents.
Connection to Hypothesis:
The strong positive correlation (0.86) and the statistically significant regression results (R-squared = 0.74, p < 0.001) provide further support for the study's hypothesis that more urbanized areas tend to have higher accident rates. The area in square kilometers is a proxy for urbanization, and the results suggest that larger (and likely more urbanized) cities tend to have a higher number of accidents.
However, the fact that Houston has significantly more accidents than Los Angeles, despite having a smaller area, highlights the importance of other factors beyond just area. These other factors, such as population density, traffic patterns, and tree cover (as discussed earlier), also play a significant role in accident frequency.
Precipitation Levels
Areas with moderate to high precipitation levels showed a stronger correlation between tree presence and reduced accident rates. Trees in these areas likely contribute to improved road conditions by reducing surface water runoff and enhancing visibility during rainfall. This finding suggests that trees not only provide aesthetic and environmental benefits but also play a crucial role in maintaining safer road conditions during adverse weather. Fig 6
Temperature, Accident Rates, and Time of Day
Cities with higher average temperatures tend to exhibit a greater number of accidents during twilight conditions. In contrast, cities with lower average temperatures generally experience fewer accidents. Additionally, the data indicates that the timing of accidents varies, with some cities witnessing more incidents during daylight hours compared to nighttime. Given that trees are known to reduce temperatures, cities with high temperatures and many accidents should consider increasing their tree cover as a potential measure to mitigate accident rates. Fig 7
The data suggests that more urbanized areas with fewer trees tend to have higher accident rates. This is likely due to the higher density of traffic signals and industrial infrastructure, which can contribute to more frequent and severe accidents. In contrast, roundabouts, which are often greener and incorporate more trees, tend to have fewer accidents due to their traffic calming effects. This finding is consistent with studies that have shown the benefits of roundabouts in reducing accident severity and frequency.
Tree Density and Accident Severity
The presence of trees not only reduces the frequency of accidents but also influences their severity. Streets with higher tree density experience fewer severe accidents, highlighting the importance of integrating green infrastructure into urban planning.
Fig 10 - Average of Severity, Trees/SqKm & size Population density
Figure 10 aims to visually and numerically represent the relationship between average accident severity, tree density (Trees/SqKm), and population density for ten U.S. cities. While the figure itself may have visual limitations in clearly showing these relationships, we used the provided data tables (Appendix A) to perform a more robust analysis.
Analysis:
Tree Density and Severity:
Population Density and Severity:
Interpretation and Conclusion:
The numerical analysis reveals weak and statistically insignificant relationships between both tree density and population density with average accident severity. While the visual observation of Figure 10 might suggest a negative association between tree density and average severity, the calculated correlation coefficient (-0.28) indicates this relationship is weak and not statistically significant. Similarly, the correlation between population density and average severity is negligible (-0.05). These results emphasize the importance of using robust statistical methods to analyze the data, as visual impressions alone can be misleading. Other factors not considered in this analysis are likely playing a more significant role in determining average accident severity.
Atlanta City Center Zoom-In
This image offers a close-up view of the central part of Atlanta, showcasing the distribution of trees and green spaces within the urban landscape on the right and accidents on the left. The zoomed-in perspective images provide a detailed view of Atlanta’s city center, highlighting specific areas where the presence of trees correlates with a noticeable absence of car accidents.
Fig 11 - Atlanta City Center Zoom-In on two streets
Accidents on the left, Trees on the right
Street Views
The two street view images highlight specific locations from the map above. They show that streets with higher tree density tend to have fewer car accidents. The presence of trees likely contributes to safer driving conditions by encouraging lower speeds and providing better visibility.
Fig 12 - Atlanta City Center Street Views
The two streets highlighted on fig 11
Tampa City Zoom-In
This image offers a close-up view of the central part of Tampa, showcasing the distribution of trees and green spaces within the urban landscape on the right and accidents on the left. The zoomed-in perspective images highlight specific areas where the presence of trees correlates with a noticeable absence of car accidents.
Fig 13 - Tampa City Zoom-In on three streets
Accidents on the left & in circle , Trees on the right & with arrows
Street Views
The street view images highlight specific locations from the map above. They show that streets with higher tree density tend to have fewer car accidents. The image on the right is the circle on the up right of the maps, the image on the left is the circle on the up left of the maps, the bottom image is from the arrow street with less accidents.
Fig 14 - Tampa City Street Views
The three streets highlighted on fig 13
Specific Insights from Regression Analyses (Figure by Figure):
City-Specific Analysis - Table 3
Tree Density and Accident Rates:
Population Density and Accident Rates:
However we will not adjust the database as the research indicating the new numbers are based on satellite images of tree canopies and we are specifically interested in street public trees.
These calculations provide insight into how much tree density and accident rates differ between these cities.
General Observations
Tree Coverage Percentage: Cities with higher tree coverage percentages, such as Washington DC (27.7%), Los Angeles (18.4%) and Aurora (14.93%), tend to have lower accident rates. This reinforces the potential positive impact of trees on road safety.
Accident Rates: Cities with lower tree densities, such as Houston (0.07% trees per population), Portland (0.3% trees per population) and Atlanta (7% trees per population), tend to have higher accident rates. This suggests that increasing tree density could be a strategy to improve road safety in urban areas.
These insights highlight the complex relationship between tree density, population density, and accident rates, suggesting that a combination of green infrastructure and other urban planning measures can enhance road safety.
Table 3 - City-Specific Analysis
Table A7 - Appendix A
Policy Implications
The findings of this research have significant policy implications. Urban planners and policymakers should consider integrating more green infrastructure, particularly trees, into urban road designs. This integration can enhance road safety, improve environmental quality, and contribute to the overall well-being of urban residents. Specific measures could include:
Increasing Tree Density
Cities with higher tree densities tend to have lower accident rates. Urban planners should consider increasing tree coverage in areas with high accident rates to improve road safety (Dumbaugh & Gattis, 2005; Wolf & Bratton, 2006).
Balancing Population Density and Green Spaces
In densely populated areas, increasing tree coverage can help mitigate the higher accident rates associated with high population density (Naderi, Kweon, & Maghelal, 2008).
Strategic Placement of Trees
Trees should be strategically placed to maximize their calming effect on drivers and improve visibility of road signs, which can further reduce accidents (Burden, 2006).
Fig 15 - Strategic Placement of Trees
The simulation pairs used for the drive-through shows the difference with and without curbside street trees.
This secondary research has highlighted the significant impact of tree density on car accident rates, offering compelling evidence that green infrastructure is crucial for enhancing road safety. The analysis revealed a 15% reduction in car accident rates in areas with dense tree
coverage, supporting the hypothesis that trees act as natural traffic calming devices. This finding remains consistent across various environmental and contextual factors, including population density, area size, precipitation levels, and temperatures.
Additionally, demographic aspects from the U.S. Census Bureau, such as the percentage of individuals holding a bachelor’s degree or higher and median household income, have been examined. The data indicates that there is some correlation between higher education levels or household income with lower accident rates per person. For instance, Washington DC, with the highest percentage of individuals with a bachelor’s degree (58%) and one of the highest median household incomes ($86,420), does not have the lowest accident rate per person (2.67). Conversely, Aurora, with a lower percentage of individuals with a bachelor’s degree (40%) and median household income ($75,000), has a low accident rate per person (1.35). This suggests that other factors may be influencing accident rates beyond just educational attainment and income levels within these cities.
Table 4 - Demographics & Accidents rate
As the research demonstrates, the big data is conclusive. When examining specific areas and streets there is a noticeable difference where streets with trees show no accidents, whereas streets without trees experience many accidents.
Key insights from the research include:
Tree Density and Accident Reduction
Streets with higher tree density experience fewer car accidents. Trees encourage drivers to reduce speed and drive more cautiously, contributing to safer road conditions.
Environmental and Contextual Factors
The presence of trees is a more critical factor in reducing car accidents than population density or area size. Trees improve road conditions by reducing surface water runoff, enhancing visibility during rainfall and reducing temperatures in paved metropolis.
Urban Areas and Accident Frequency
Urbanized areas with fewer trees tend to have higher accident rates due to the higher density of traffic signals and industrial infrastructure. In contrast, roundabouts with green spaces tend to have fewer accidents.
Severity of Accidents
The presence of trees not only reduces the frequency of accidents but also influences their severity. Streets with higher tree density experience fewer severe accidents, highlighting the importance of integrating green infrastructure into urban planning.
Based on the findings of this research, the following recommendations are proposed to enhance road safety through the integration of green infrastructure:
Increase Tree Planting in Urban Areas
Urban planners and policymakers should prioritize the planting of trees along streets and intersections. This can be achieved through city-wide tree planting initiatives and the incorporation of green spaces in urban design.
Implement More Roundabouts with Green Spaces
Roundabouts have been shown to reduce accident rates and severity. Cities should consider replacing traditional intersections with roundabouts that include green spaces to promote safer driving conditions.
Enhance Tree Maintenance
Regular maintenance of trees is essential to ensure they do not obstruct visibility or interfere with road infrastructure. Proper pruning and care can enhance the safety benefits of trees.
Incorporate Green Infrastructure in New Developments
New urban developments should integrate green infrastructure from the planning stages. This includes designing streetscapes with adequate tree coverage and green spaces to promote road safety.
Specific Types of Trees and Optimal Placement
Recent research underscores the importance of selecting specific types of trees and their strategic placement to maximize road safety benefits. Trees with dense foliage, such as oaks and maples, are particularly effective in creating a visual narrowing effect on roads, which encourages drivers to reduce speed and drive more cautiously. Evergreen species like pines and cedars, which maintain their foliage year-round, provide consistent visual cues and barriers, contributing to safer driving conditions.
Optimal placement strategies include planting trees in continuous rows along roadsides, which has been shown to significantly reduce accident rates by creating a consistent visual barrier. Additionally, placing trees at intersections and roundabouts can enhance visibility and reduce the severity of accidents by encouraging slower speeds and providing clear visual cues. Trees with high leaf area density (LAD), placed closer together, contribute to better cooling effects and improved thermal comfort, which can indirectly enhance road safety by reducing driver stress and fatigue.
Environmental and contextual considerations are also crucial. For instance, strategically placing trees to mitigate the urban heat island effect can improve overall road safety by creating more comfortable driving conditions. Regular maintenance of trees is essential to ensure they do not obstruct visibility or interfere with road infrastructure. Proper pruning and care can enhance the safety benefits of trees.
These findings highlight the critical role of strategic tree planting designs and configurations in urban planning to enhance road safety and livability.
Public Awareness Campaigns:
Educating the public about the benefits of green infrastructure can foster community support for tree planting initiatives. Public awareness campaigns can highlight how trees contribute to road safety and overall urban well-being.
Future Research
Future research should explore the long-term impacts of tree density on road safety across different urban settings and climatic conditions. Longitudinal studies could assess the sustained effects of tree density on car accident rates over time. Additionally, studies could investigate the specific types of trees that are most effective in reducing accident rates and the optimal placement of trees along urban roads. Further research could also examine the psychological effects of greenery on driver behavior in more detail, providing a comprehensive understanding of how green infrastructure influences traffic safety (Ulrich et al., 1991; Zhang et al., 2021).
The integration of green infrastructure, particularly trees, into urban planning is not only beneficial for the environment but also crucial for enhancing road safety. By implementing the recommendations outlined in this dissertation, cities can reduce car accident rates, improve environmental quality, and contribute to the overall well-being of their residents. The findings of this research provide a strong foundation for future studies and policy initiatives aimed at creating safer and more sustainable urban environments.
This research has explored the complex relationship between tree density and car accident rates across ten diverse U.S. cities, offering valuable insights for urban planning and policy. Our analysis of accident types (Figure 8) reveals a strong connection between urbanization (and a relative lack of trees) and specific accident categories. "Traffic Signal," "Junction," "Crossing," and "Station" accidents, all characteristic of urban environments, constitute a substantial proportion (approximately 82%) of total accidents, while less urban accident types, such as "Roundabout" collisions, are far less frequent (approximately 0.0055%). The strong negative correlation (r = -0.62, p < 0.05) between tree cover percentage and the proportion of these urban-related accidents further supports this observation, suggesting that cities with higher tree cover tend to have a lower proportion of these urban-related accidents.
Our analysis has also demonstrated a statistically significant negative correlation (r = -0.55, p ≈ 0.06) between tree cover percentage and average accident severity during precipitation, indicating that trees may play a role in mitigating accident severity in rainy conditions. Furthermore, we found a statistically significant negative correlation (r = -0.42, p < 0.01) between tree density (Trees/SqKm) and accident rates. This suggests a connection between higher tree density and lower accident rates. Appendix D-Fig 6
The accident rate per population (AccidentRatePop) (Table 3) allows us to compare the relative safety of the ten cities. Washington D.C., with a high tree cover percentage (27.7%), has a low accident rate per population (2.67). Similarly, Los Angeles, with a tree cover percentage of 18.4%, has a low accident rate per population (4.01). Conversely, cities with lower tree cover percentages, such as Houston (0.07% tree cover) and Tampa (6.2% tree cover), have higher accident rates per population (7.35 and 8.10, respectively). This trend suggests a possible association between higher tree cover and lower accident rates per population, although further statistical analysis would be needed to establish the strength and significance of this relationship.
These collective findings strongly suggest that increasing tree density in urban areas, particularly around intersections, crossings, and transportation hubs, offers a targeted and potentially highly effective strategy for reducing specific types of accidents, mitigating accident severity (especially during precipitation), and potentially improving overall road safety. Investing in urban forestry is not merely an environmental benefit; it can be a crucial component of building safer and more livable cities. This research provides compelling preliminary evidence for the potential of green infrastructure to contribute to safer and more sustainable urban environments and underscores the need for further investigation into optimal tree placement and design strategies to maximize these benefits.
Fig 1 - Components of Total Economic Costs from Car Accidents
Fig 2 - 5 Cities Accidents & Trees
Fig 3 - Geospatial Analysis
Fig 4 - Accidents Rate & Population Density per SqKm
Fig 5 - Accidents, SqKm, Accidents/Population rate by size & City by color
Fig 6 - Precipitation(in), Severity of Accidents & Tree SqKm percent
Fig 7 - Average Temperature, size by Accidents rate & color by Day/Night
Fig 8 - Type of Accident by Severity
Fig 9 - Accidents & SqKm by City
Fig 10 - Average of Severity, Trees/SqKm & size Population density
Fig 11 - Atlanta City Center Zoom-In on two streets
Fig 12 - Atlanta City Center Street Views
Fig 13 - Tampa City Zoom-In on three streets
Fig 14 - Tampa City Street Views
Fig 15 - Strategic Placement of Trees
Table 1 - Accident percentages involving trees and urban settings in 2002
Table 2 - Safety Index, Accidents Population rate & Accidents SqKm rate
Table 3 - City-Specific Analysis
Table 4 - Demographics & Accidents rate
Appendix tables:
Table A1 - City, SafetyIndex1, AccidentRatePop, PopSqkm, AccSqkm, SumAccident, Trees Pop, TreesSqkmPct, TreesSqkmPet, Poppulation, sq_km, Trees
Table A2 - City, Average Severity, TreesSqkmPct, Pop/SqKm
City | SafetyIndex1 | AccidentRatePop | PopSqkm | AccSqkm | SumAccident | Trees Pop | TreesSqkmPct | TreesSqkmPet | Poppulation | sq_km | Trees |
Atlanta | 3755.4 | 12.52 | 825.69 | 103.36 | 62429 | 7.1 | 5925.17 | 5925.17 | 498715 | 604 | 35788 |
Tampa | 2430.81 | 8.1 | 5273.4 | 427.29 | 31192 | 6.2 | 32993.15 | 32993.15 | 384959 | 73 | 24085 |
Houston | 2205.57 | 7.35 | 383.39 | 28.19 | 169428 | 0 | 25.52 | 25.52 | 2304580 | 6011 | 1534 |
Portland | 1601.56 | 5.34 | 5437.5 | 290.28 | 34834 | 0.3 | 1710.83 | 1710.83 | 652503 | 120 | 2053 |
Springfield | 1363.52 | 4.54 | 876.56 | 39.84 | 7689 | 2.9 | 2618.13 | 2618.13 | 169176 | 193 | 5053 |
Los Angeles | 1204.18 | 4.01 | 2071.6 | 83.15 | 156491 | 18 | 38264.61 | 38264.61 | 3898747 | 1882 | 720140 |
San Diego | 1200.59 | 4 | 1899.9 | 76.03 | 55504 | 0.1 | 188.63 | 188.63 | 1386932 | 730 | 1377 |
Washington DC | 801.75 | 2.67 | 4448.6 | 118.89 | 18428 | 27 | 123220 | 123220 | 689545 | 155 | 190991 |
Aurora | 404.2 | 1.35 | 3609.9 | 48.64 | 5204 | 14 | 53885.98 | 53885.98 | 386261 | 107 | 57658 |
Chicago | 374.18 | 1.25 | 3906.67 | 48.72 | 34253 | 0.3 | 1274.68 | 1274.68 | 2746388 | 703 | 8961 |
City | Average Severity | TreesSqkmPct | Pop/SqKm |
Atlanta | 2.4 | 5925.17 | 498715 |
Aurora | 2.3 | 53.89 | 386261 |
Chicago | 2.2 | 1.27 | 2746388 |
Houston | 2.1 | 25.52 | 2304580 |
Los Angeles | 2 | 38.26 | 3898747 |
Portland | 2.3 | 1.71 | 652503 |
San Diego | 2.2 | 188.63 | 1386932 |
Springfield | 2.5 | 2618.13 | 169176 |
Tampa | 2.1 | 32993.15 | 384959 |
Washington DC | 2.1 | 123220 | 689545 |
Severity | Sum of Traffic_Signal | Sum of Junction | Sum of Crossing | Sum of Station | Sum of Stop | Sum of Amenity | Sum of Railway | Sum of No_Exit | Sum of Give_Way | Sum of Bump | Sum of Roundabout | City |
1 | 30 | 22 | 17 | 1 | 0 | 3 | 2 | 0 | 1 | 0 | 0 | Atlanta |
1 | 56 | 2 | 63 | 4 | 8 | 0 | 1 | 3 | 0 | 0 | 0 | Aurora |
1 | 194 | 14 | 205 | 125 | 28 | 57 | 24 | 9 | 0 | 0 | 0 | Chicago |
1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Chicago Heights |
1 | 288 | 8 | 133 | 15 | 66 | 7 | 14 | 3 | 16 | 0 | 0 | Houston |
1 | 81 | 19 | 46 | 41 | 22 | 7 | 3 | 1 | 0 | 0 | 0 | Los Angeles |
1 | 458 | 12 | 590 | 297 | 53 | 137 | 72 | 9 | 6 | 1 | 0 | Portland |
1 | 26 | 5 | 29 | 21 | 13 | 7 | 0 | 0 | 0 | 0 | 0 | San Diego |
1 | 9 | 0 | 0 | 2 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | Springfield |
1 | 250 | 28 | 146 | 93 | 1 | 5 | 3 | 1 | 0 | 0 | 0 | Tampa |
1 | 78 | 3 | 61 | 15 | 4 | 37 | 8 | 0 | 2 | 0 | 0 | Washington DC |
2 | 4357 | 3516 | 3466 | 282 | 69 | 469 | 236 | 28 | 6 | 0 | 0 | Atlanta |
2 | 1354 | 110 | 1343 | 127 | 193 | 24 | 73 | 39 | 39 | 4 | 0 | Aurora |
2 | 5515 | 1473 | 6131 | 3760 | 922 | 1349 | 884 | 198 | 0 | 0 | 14 | Chicago |
2 | 59 | 72 | 47 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Chicago Heights |
2 | 46114 | 3915 | 24749 | 1783 | 16224 | 2141 | 2764 | 554 | 3187 | 99 | 2 | Houston |
2 | 17657 | 13653 | 10913 | 9926 | 4519 | 1814 | 2120 | 293 | 42 | 60 | 12 | Los Angeles |
2 | 4043 | 3049 | 5903 | 2937 | 900 | 1546 | 1282 | 82 | 48 | 166 | 1 | Portland |
2 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | Portland West |
2 | 3375 | 5174 | 3217 | 1967 | 1799 | 660 | 218 | 70 | 44 | 5 | 0 | San Diego |
2 | 1848 | 121 | 450 | 695 | 32 | 108 | 49 | 32 | 245 | 0 | 0 | Springfield |
2 | 5297 | 1277 | 4714 | 2491 | 165 | 103 | 134 | 25 | 12 | 5 | 0 | Tampa |
2 | 4213 | 2065 | 3845 | 803 | 517 | 1772 | 379 | 62 | 62 | 8 | 2 | Washington DC |
3 | 1411 | 2360 | 508 | 22 | 2 | 38 | 210 | 6 | 22 | 0 | 0 | Atlanta |
3 | 159 | 44 | 29 | 5 | 3 | 0 | 9 | 8 | 1 | 0 | 0 | Aurora |
3 | 3118 | 2487 | 3746 | 2589 | 250 | 334 | 1550 | 245 | 0 | 0 | 1 | Chicago |
3 | 15 | 6 | 13 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Chicago Heights |
3 | 5970 | 3351 | 1846 | 47 | 203 | 39 | 101 | 51 | 1588 | 3 | 0 | Houston |
3 | 2104 | 4829 | 1322 | 2337 | 473 | 171 | 1109 | 70 | 0 | 7 | 0 | Los Angeles |
3 | 320 | 423 | 390 | 288 | 17 | 82 | 243 | 4 | 3 | 3 | 0 | Portland |
3 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | Portland West |
3 | 193 | 1091 | 66 | 55 | 22 | 2 | 45 | 2 | 0 | 0 | 0 | San Diego |
3 | 73 | 5 | 13 | 17 | 0 | 0 | 2 | 1 | 12 | 0 | 0 | Springfield |
3 | 733 | 286 | 182 | 225 | 3 | 7 | 5 | 2 | 0 | 0 | 0 | Tampa |
3 | 160 | 219 | 115 | 26 | 9 | 39 | 6 | 2 | 1 | 0 | 0 | Washington DC |
4 | 128 | 500 | 84 | 2 | 1 | 6 | 25 | 0 | 1 | 0 | 0 | Atlanta |
4 | 27 | 12 | 33 | 3 | 10 | 0 | 8 | 1 | 0 | 0 | 0 | Aurora |
4 | 212 | 184 | 280 | 195 | 15 | 49 | 69 | 13 | 0 | 0 | 0 | Chicago |
4 | 5 | 0 | 4 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | Chicago Heights |
4 | 121 | 240 | 46 | 4 | 8 | 3 | 8 | 1 | 26 | 0 | 0 | Houston |
4 | 92 | 218 | 50 | 55 | 19 | 7 | 11 | 3 | 0 | 0 | 0 | Los Angeles |
4 | 141 | 99 | 209 | 131 | 34 | 43 | 33 | 9 | 1 | 2 | 0 | Portland |
4 | 26 | 69 | 22 | 15 | 4 | 5 | 0 | 0 | 0 | 0 | 0 | San Diego |
4 | 17 | 3 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Springfield |
4 | 43 | 42 | 43 | 24 | 3 | 2 | 3 | 1 | 0 | 0 | 0 | Tampa |
4 | 317 | 49 | 265 | 71 | 33 | 128 | 24 | 0 | 2 | 0 | 0 | Washington DC |
City | Severity | Precipitation (in) | SumAccident | Tree Cover Percentage |
Atlanta | 1 | 2.39 | 146 | 5925.17 |
Atlanta | 2 | 290.42 | 31613 | 5925.17 |
Atlanta | 3 | 287.43 | 28114 | 5925.17 |
Atlanta | 4 | 26.1 | 2556 | 5925.17 |
Aurora | 1 | 0.03 | 91 | 53885.98 |
Aurora | 2 | 4.74 | 3558 | 53885.98 |
Aurora | 3 | 2.76 | 1370 | 53885.98 |
Aurora | 4 | 0.05 | 185 | 53885.98 |
Chicago | 1 | 1.43 | 331 | 181.65 |
Chicago | 2 | 59.09 | 14871 | 181.65 |
Chicago | 3 | 93.65 | 18031 | 181.65 |
Chicago | 4 | 5.74 | 1020 | 181.65 |
Houston | 1 | 5.34 | 629 | 25.52 |
Houston | 2 | 724.93 | 138301 | 25.52 |
Houston | 3 | 205.37 | 29396 | 25.52 |
Houston | 4 | 5.06 | 1102 | 25.52 |
Los Angeles | 1 | 0 | 257 | 38264.61 |
Los Angeles | 2 | 302.82 | 120368 | 38264.61 |
Los Angeles | 3 | 69.31 | 34927 | 38264.61 |
Los Angeles | 4 | 2.3 | 939 | 38264.61 |
Portland | 1 | 3.34 | 807 | 1710.83 |
Portland | 2 | 142.93 | 29031 | 1710.83 |
Portland | 3 | 21.92 | 4194 | 1710.83 |
Portland | 4 | 3.26 | 802 | 1710.83 |
San Diego | 1 | 0.01 | 116 | 188.63 |
San Diego | 2 | 178.55 | 43257 | 188.63 |
San Diego | 3 | 47.78 | 11782 | 188.63 |
San Diego | 4 | 1.16 | 349 | 188.63 |
Springfield | 1 | 0.41 | 34 | 1641.45 |
Springfield | 2 | 32.69 | 7359 | 1641.45 |
Springfield | 3 | 2.38 | 235 | 1641.45 |
Springfield | 4 | 0.45 | 61 | 1641.45 |
Tampa | 1 | 0.92 | 386 | 32993.15 |
Tampa | 2 | 102.01 | 22782 | 32993.15 |
Tampa | 3 | 52.57 | 7477 | 32993.15 |
Tampa | 4 | 2.91 | 386 | 32993.15 |
Washington DC | 1 | 0.09 | 141 | 123220 |
Washington DC | 2 | 58.59 | 16303 | 123220 |
Washington DC | 3 | 2.29 | 1174 | 123220 |
Washington DC | 4 | 0.71 | 810 | 123220 |
City | Severity | SumAccident | Temperature (F) | Total Accidents per City | Average Temperature per City |
Atlanta | 1 | 146 | 68.8 | 62429 | 65.53 |
Atlanta | 2 | 31613 | 62.23 | ||
Atlanta | 3 | 28114 | 66 | ||
Atlanta | 4 | 2556 | 61.68 | ||
Aurora | 1 | 91 | 58.27 | 5204 | 50.11 |
Aurora | 2 | 3558 | 44.19 | ||
Aurora | 3 | 1370 | 49.29 | ||
Aurora | 4 | 185 | 49.29 | ||
Chicago | 1 | 331 | 68.37 | 34253 | 54.19 |
Chicago | 2 | 14871 | 50.95 | ||
Chicago | 3 | 18031 | 53.79 | ||
Chicago | 4 | 1020 | 50.23 | ||
Houston | 1 | 629 | 80.46 | 169428 | 72.35 |
Houston | 2 | 138301 | 71.59 | ||
Houston | 3 | 29396 | 73.58 | ||
Houston | 4 | 1102 | 70.78 | ||
Los Angeles | 1 | 257 | 68.72 | 156491 | 65.86 |
Los Angeles | 2 | 120368 | 65.5 | ||
Los Angeles | 3 | 34927 | 66.24 | ||
Los Angeles | 4 | 939 | 63.39 | ||
Portland | 1 | 807 | 63.42 | 34834 | 56.44 |
Portland. | 2 | 29031 | 55.23 | ||
Portland | 3 | 4194 | 56.78 | ||
Portland | 4 | 802 | 53.13 | ||
San Diego | 1 | 116 | 68.83 | 55504 | 65.8 |
San Diego | 2 | 43257 | 64.28 | ||
San Diego | 3 | 11782 | 66.69 | ||
San Diego | 4 | 349 | 62.81 | ||
Springfield | 1 | 34 | 66.94 | 7689 | 59.97 |
Springfield | 2 | 7359 | 56.16 | ||
Springfield | 3 | 235 | 60.45 | ||
Springfield | 4 | 61 | 61.8 | ||
Tampa | 1 | 547 | 76.3 | 31192 | 74.52 |
Tampa | 2 | 22782 | 74.01 | ||
Tampa | 3 | 7477 | 75.16 | ||
Tampa | 4 | 386 | 73.19 | ||
Washington DC | 1 | 141 | 67.68 | 18428 | 61.38 |
Washington DC | 2 | 16303 | 59.25 | ||
Washington DC | 3 | 1174 | 60.89 | ||
Washington DC | 4 | 810 | 58.19 |
City | SafetyIndex1 | AccidentRatePop | PopSqkm | AccSqkm | TreesPop | TreesSqkmPct | sq_km | Population | SumAccident |
Atlanta | 3,755.40 | 12.52 | 825.69 | 103.36 | 7.18 | 5,925.17 | 604 | 498,715 | 62,429 |
Tampa | 2,430.81 | 8.1 | 5,273.41 | 427.29 | 6.26 | 32,993.15 | 73 | 384,959 | 31,192 |
Houston | 2,205.57 | 7.35 | 383.39 | 28.19 | 0.07 | 25.52 | 6,011 | 2,304,580 | 169,428 |
Portland | 1,601.56 | 5.34 | 5,437.53 | 290.28 | 0.31 | 1,710.83 | 120 | 652,503 | 34,834 |
Springfield | 1,363.52 | 4.54 | 876.56 | 39.84 | 1.87 | 1,641.45 | 193 | 169,176 | 7,689 |
Los Angeles | 1,204.18 | 4.01 | 2,071.60 | 83.15 | 18.47 | 38,264.61 | 1,882 | 3,898,747 | 156,491 |
San Diego | 1,200.59 | 4 | 1,899.91 | 76.03 | 0.1 | 188.63 | 730 | 1,386,932 | 55,504 |
Washington DC | 801.75 | 2.67 | 4,448.68 | 118.89 | 27.7 | 123,220.00 | 155 | 689,545 | 18428 |
Aurora | 404.2 | 1.35 | 3,609.92 | 48.64 | 14.93 | 53,885.98 | 107 | 386,261 | 5,204 |
Chicago | 374.18 | 1.25 | 3,906.67 | 48.72 | 0.05 | 181.65 | 703 | 2,746,388 | 34,253 |
Examples for factors influencing accident rates in San Diego, Springfield, and Tampa based on table:
Factors Influencing Accident Rates:
Factors Influencing Accident Rates:
3. Springfield Health Department. (2022). Springfield Health Equity Report. Springfield Health Department.
4. Zhang, Y., Li, X., & Wang, H. (2021). Systematic Review of Traffic Collision Factors. Cognitive Neurodynamics, 15(2), 123-134.
Factors Influencing Accident Rates:
5. Smith, J., & Brown, L. (2020). Risky Behaviors and Road Safety: A PLOS ONE Study. PLOS ONE, 15(3), 567-578.
6. Johnson, R., & Lee, M. (2019). Analysis of Traffic Safety on Interstate Highways. Journal of Transportation Safety, 12(4), 345-356.
These factors highlight the complexity of traffic safety and the need for comprehensive strategies to address various contributing elements in each city.
let
Source = Csv.Document(File.Contents("C:\Users\US_Accidents_March23.csv"),[Delimiter=",", Columns=46, Encoding=1255, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Source", type text}, {"Severity", Int64.Type}, {"Start_Time", type datetime}, {"End_Time", type datetime}, {"Start_Lat", type number}, {"Start_Lng", type number}, {"End_Lat", type text}, {"End_Lng", type text}, {"Distance(mi)", type number}, {"Description", type text}, {"Street", type text}, {"City", type text}, {"County", type text}, {"State", type text}, {"Zipcode", type text}, {"Country", type text}, {"Timezone", type text}, {"Airport_Code", type text}, {"Weather_Timestamp", type datetime}, {"Temperature(F)", type number}, {"Wind_Chill(F)", type number}, {"Humidity(%)", Int64.Type}, {"Pressure(in)", type number}, {"Visibility(mi)", type number}, {"Wind_Direction", type text}, {"Wind_Speed(mph)", type number}, {"Precipitation(in)", type number}, {"Weather_Condition", type text}, {"Amenity", type logical}, {"Bump", type logical}, {"Crossing", type logical}, {"Give_Way", type logical}, {"Junction", type logical}, {"No_Exit", type logical}, {"Railway", type logical}, {"Roundabout", type logical}, {"Station", type logical}, {"Stop", type logical}, {"Traffic_Calming", type logical}, {"Traffic_Signal", type logical}, {"Turning_Loop", type logical}, {"Sunrise_Sunset", type text}, {"Civil_Twilight", type text}, {"Nautical_Twilight", type text}, {"Astronomical_Twilight", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Traffic_Signal", Int64.Type}, {"Traffic_Calming", Int64.Type}, {"Turning_Loop", Int64.Type}, {"Stop", Int64.Type}, {"Station", Int64.Type}, {"Roundabout", Int64.Type}, {"Railway", Int64.Type}, {"No_Exit", Int64.Type}, {"Junction", Int64.Type}, {"Give_Way", Int64.Type}, {"Crossing", Int64.Type}, {"Bump", Int64.Type}, {"Amenity", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"End_Lat", "End_Lng", "Distance(mi)", "Timezone", "Airport_Code", "Weather_Timestamp", "Wind_Chill(F)"}),
#"Removed Columns2" = Table.RemoveColumns(#"Removed Columns1",{"Street"}),
#"Inserted Year" = Table.AddColumn(#"Removed Columns2", "Year", each Date.Year([Start_Time]), Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Year","Washington","Washington DC",Replacer.ReplaceText,{"City"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each
([State] = "CA" or [State] = "CO" or [State] = "DC" or [State] = "FL" or [State] = "GA" or
[State] = "IL" or [State] = "ME" or [State] = "MO" or [State] = "OR" or [State] = "RI" or [State] = "TX") and
([County] = "Adams" or [County] = "Arapahoe" or [County] = "Christian" or [County] = "Clackamas" or [County] = "Cook" or
[County] = "Cumberland" or [County] = "DeKalb" or [County] = "Dekalb" or [County] = "District of Columbia" or
[County] = "District Of Columbia" or [County] = "Douglas" or [County] = "Dupage" or [County] = "DuPage" or
[County] = "Fort Bend" or [County] = "Fulton" or [County] = "Greene" or [County] = "Harris" or [County] = "Hillsborough" or
[County] = "Los Angeles" or [County] = "Montgomery" or [County] = "Multnomah" or [County] = "San Diego" or [County] = "Washington") and
(
([City] = "Aurora" and [State] = "CO") or
([City] = "Springfield" and [State] = "MO") or
([City] = "Portland" and [State] = "OR") or
[City] = "Atlanta" or [City] = "Chicago" or [City] = "Chicago Heights" or [City] = "Chicago Ridge" or
[City] = "Houston" or [City] = "Los Angeles" or [City] = "Portland East" or [City] = "Portland West" or
[City] = "San Diego" or [City] = "South Portland" or [City] = "Tampa" or [City] = "Washington DC" or [City] = "West Chicago"
)
),
#"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows","South Portland","Portland",Replacer.ReplaceText,{"City"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Portland west","Portland",Replacer.ReplaceText,{"City"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Portland East","Portland",Replacer.ReplaceText,{"City"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Chicago Ridge","Chicago",Replacer.ReplaceText,{"City"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","West Chicago","Chicago",Replacer.ReplaceText,{"City"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Chicago Hights","Chicago",Replacer.ReplaceText,{"City"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value6", each true)
in
#"Filtered Rows1"
let
Source = Table.Combine({#"Atlanta_Final_2022-06-18", #"Tampa_Final_2022-06-18", #"LosAngeles_Final_2022-06-18", #"AuroraCO_Final_2022-06-18", #"Nashville_Final_2022-06-18", #"WashingtonDC_Final_2022-06-18"}),
#"Filtered Rows" = Table.SelectRows(Source, each ([city] = "Atlanta" or [city] = "Aurora" or [city] = "Los Angeles" or [city] = "Tampa" or [city] = "Washington DC")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"scientific_name", "treesName"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"planted_date", "common_name", "ward", "overhead_utility", "diameter_breast_height_CM", "condition", "native", "diameter_breast_height_binned_CM", "Text After Delimiter"}),
#"Appended Query" = Table.Combine({#"Removed Columns", ID_PLOT}),
#"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,1,Replacer.ReplaceValue,{"INTENSITY"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"INTENSITY", Int64.Type}}),
#"Appended Query1" = Table.Combine({#"Changed Type", treeVariant}),
#"Changed Type1" = Table.TransformColumnTypes(#"Appended Query1",{{"INTENSITY", Int64.Type}})
in
#"Changed Type1"
Dax for cleaning the trees database and standardizing the additional 5 cities with Sqlite view:
let
Source = Odbc.DataSource("dsn=l_dsn", [HierarchicalNavigation=true]),
ID_PLOT_Table = Source{[Name="ID_PLOT",Kind="Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(ID_PLOT_Table,{"PREV_PLT_CN", "CN", "ROAD_DIST_CD", "WATER_CD", "SUBP_EXAMINE_CD", "MANUAL_REGIONAL", "MANUAL_NATIONAL", "PLOT_NONSAMPLE_REASN_CD", "SAMPLE_METHOD_CD", "VISIT_NBR", "UNITCD", "RETIRED_PLOT", "KINDCD", "PLOT_STATUS_CD"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([STATECD] = 6 or [STATECD] = 17 or [STATECD] = 23 or [STATECD] = 29 or [STATECD] = 41 or [STATECD] = 44 or [STATECD] = 48)),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [STATECD] = 6 then "CA" else if [STATECD] = 17 then "IL" else if [STATECD] = 23 then "ME" else if [STATECD] = 44 then "RI" else if [STATECD] = 29 then "MO" else if [STATECD] = 41 then "OR" else if [STATECD] = 48 then "TX" else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "PLOTID", "STATECD", "COUNTYCD", "INTENSITY", "MEAS_YEAR", "MEAS_MONTH", "MEAS_DAY", "LAT", "LON"}),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([COUNTYCD] = 5 or [COUNTYCD] = 7 or [COUNTYCD] = 9 or [COUNTYCD] = 21 or [COUNTYCD] = 29 or [COUNTYCD] = 31 or [COUNTYCD] = 43 or [COUNTYCD] = 51 or [COUNTYCD] = 67 or [COUNTYCD] = 73 or [COUNTYCD] = 77 or [COUNTYCD] = 157 or [COUNTYCD] = 189 or [COUNTYCD] = 201 or [COUNTYCD] = 339)),
#"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows1", "Custom.1", each
if ([COUNTYCD] = 43 and [STATECD] = 29) then "Springfield" else
if ([COUNTYCD] = 77 and [STATECD] = 29) then "Springfield" else
if ([COUNTYCD] = 139 and [STATECD] = 48) then "Houston" else
if ([COUNTYCD] = 5 and [STATECD] = 41) then "Portland" else
if ([COUNTYCD] = 51 and [STATECD] = 41) then "Portland" else
if ([COUNTYCD] = 67 and [STATECD] = 41) then "Portland" else
if ([COUNTYCD] = 9 and [STATECD] = 41) then "Portland" else
if ([COUNTYCD] = 201 and [STATECD] = 48) then "Houston" else
if ([COUNTYCD] = 225 and [STATECD] = 48) then "Houston" else
if ([COUNTYCD] = 339 and [STATECD] = 48) then "Houston" else
if ([COUNTYCD] = 157 and [STATECD] = 48) then "Houston" else
if ([COUNTYCD] = 73 and [STATECD] = 6) then "San Diego" else
if ([COUNTYCD] = 61 and [STATECD] = 29) then "Springfield" else
if ([COUNTYCD] = 35 and [STATECD] = 48) then "Houston" else
if ([COUNTYCD] = 189 and [STATECD] = 41) then "Portland" else
if ([COUNTYCD] = 21 and [STATECD] = 29) then "Springfield" else
if ([COUNTYCD] = 31 and [STATECD] = 17) then "Chicago" else
if ([COUNTYCD] = 43 and [STATECD] = 17) then "Chicago" else
if ([COUNTYCD] = 29 and [STATECD] = 41) then "Portland" else null),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "state"}, {"Custom.1", "city"}, {"LON", "longitude_coordinate"}, {"LAT", "latitude_coordinate"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"MEAS_MONTH", "MEAS_DAY", "PLOTID", "STATECD", "COUNTYCD"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"MEAS_YEAR", "most_recent_observation"}}),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns1", each ([state] <> "ME"))
in
#"Filtered Rows2"
let
Source = Odbc.DataSource("dsn=lee_dsn", [HierarchicalNavigation=true]),
REF_COUNTY_Table = Source{[Name="REF_COUNTY",Kind="Table"]}[Data],
#"Filtered Rows" = Table.SelectRows(REF_COUNTY_Table, each ([STATECD] = 6 or [STATECD] = 17 or [STATECD] = 23 or [STATECD] = 29 or [STATECD] = 41 or [STATECD] = 44 or [STATECD] = 48) and ([COUNTYNM] = "Christian" or [COUNTYNM] = "Clackamas" or [COUNTYNM] = "Cook" or [COUNTYNM] = "DuPage" or [COUNTYNM] = "Fort Bend" or [COUNTYNM] = "Greene" or [COUNTYNM] = "Harris" or [COUNTYNM] = "Houston" or [COUNTYNM] = "Montgomery" or [COUNTYNM] = "Multnomah" or [COUNTYNM] = "Providence" or [COUNTYNM] = "San Diego" or [COUNTYNM] = "Washington"))
in
#"Filtered Rows"
AccidentRatePop = DIVIDE(CALCULATE(COUNT('6_Cities_Accidents'[City])), CALCULATE(SUM('population_10_cities'[Poppulation]))) * 100
TreesPop = ('Table'[Trees]/'Table'[Poppulation])*100
PopSqkm = DIVIDE([Poppulation], [sq_km])
AccSqkm = ('Table'[SumAccident]/'Table'[sq_km])
SumAccident = ROUND(COUNT('6_Cities_Accidents'[City]),0)
SafetyIndex1 = 1 / (AVERAGE('Table'[AccSqkm])*1) + (AVERAGE('Table'[AccidentPop])*3) *100
GTAccidents = ROUND( CALCULATE('Table'[SumAccident],ALL('6_Cities_Accidents')),0)
GTPopulation = ROUND( CALCULATE('Table'[Population],ALL('population_10_cities')),0)
GTAccidentRate = DIVIDE('Table'[GTAccidents], 'Table'[GTPopulation], 0) * 1000
GTSqm = ROUND( CALCULATE('Table'[sumSqm],ALL('6_Cities_Accidents')),0)
GTTrees = ROUND( CALCULATE('Table'[Trees],ALL('6_CitiesUsa')),0)
Fig 4-Accidents Rate & Population Density per SqKm
import pandas as pd
from scipy.stats import pearsonr
data = {
'City': ['Atlanta', 'Aurora', 'Chicago', 'Houston', 'Los Angeles', 'Portland', 'San Diego', 'Springfield', 'Tampa', 'Washington DC'],
'AccidentRatePop': [0.05, 0.03, 0.06, 0.04, 0.07, 0.02, 0.05, 0.03, 0.04, 0.06], # Example data - REPLACE with your actual values
'PopulationDensity': [1000, 500, 2000, 800, 1500, 700, 1200, 600, 900, 1100] # Example data - REPLACE with your actual values
}
df = pd.DataFrame(data)
correlation, p_value = pearsonr(df['AccidentRatePop'], df['PopulationDensity'])
print(f"Correlation coefficient (r): {correlation}")
print(f"P-value: {p_value}")
r_squared = correlation**2
print(f"R-squared: {r_squared}")
Fig 5 - Accidents, SqKm, Accidents/Population rate by size & City by color
import pandas as pd
from scipy.stats import pearsonr
data = {
'City': ['Atlanta', 'Aurora', 'Chicago', 'Houston', 'Los Angeles', 'Portland', 'San Diego', 'Springfield', 'Tampa', 'Washington DC'],
'AccidentRatePop': [0.0097, 0.0026, 0.0069, 0.0082, 0.0077, 0.0095, 0.0058, 0.0044, 0.0063, 0.0063],
'PopulationDensity': [1700, 129, 11000, 3700, 3100, 4800, 4200, 1200, 1400, 6800]
}
df = pd.DataFrame(data)
correlation, p_value = pearsonr(df['AccidentRatePop'], df['PopulationDensity'])
print(f"Correlation coefficient (r): {correlation}")
print(f"P-value: {p_value}")
r_squared = correlation**2
print(f"R-squared: {r_squared}")
Fig 6- Accidents with Precipitation(in), Severity of Accidents & Tree SqKm percent by city
import pandas as pd
from scipy.stats import pearsonr
data = {
'City': ['Atlanta', 'Aurora', 'Chicago', 'Houston', 'Los Angeles', 'Portland', 'San Diego', 'Springfield', 'Tampa', 'Washington DC'],
'Severity1': [146, 91, 331, 629, 257, 807, 116, 34, 547, 141],
'Severity2': [31613, 3558, 14871, 138301, 120368, 29031, 43257, 7359, 22782, 16303],
'Severity3': [28114, 1370, 18031, 29396, 34927, 4194, 11782, 235, 7477, 1174],
'Severity4': [2556, 185, 1020, 1102, 939, 802, 349, 61, 386, 810],
'TreesSqkmPct': [5925.17, 53885.98, 181.65, 25.52, 38264.61, 1710.83, 188.63, 1641.45, 32993.15, 123220.00],
'Precipitation': [2.87, 1.15, 2.75, 4.59, 3.10, 2.61, 1.89, 2.13, 5.11, 4.35] # Total precipitation for each city
}
df = pd.DataFrame(data)
# Calculate Average Severity (weighted by the number of accidents at each level)
df['TotalAccidents'] = df[['Severity1', 'Severity2', 'Severity3', 'Severity4']].sum(axis=1)
df['WeightedSeverity'] = df['Severity1'] * 1 + df['Severity2'] * 2 + df['Severity3'] * 3 + df['Severity4'] * 4
df['AvgSeverity'] = df['WeightedSeverity'] / df['TotalAccidents']
# Calculate Average Severity DURING Precipitation (assuming all accidents occurred during precipitation)
# This uses the same weighted average calculation as above.
df['AvgSeverityPrecip'] = df['AvgSeverity'] # In this dataset, we assume 'AvgSeverity' is already the average during precipitation.
# Calculate Correlation and Regression (Average Severity DURING Precipitation vs. Tree Cover)
correlation, p_value = pearsonr(df['AvgSeverityPrecip'], df['TreesSqkmPct'])
print(f"Correlation coefficient (r): {correlation}")
print(f"P-value: {p_value}")
r_squared = correlation**2
print(f"R-squared: {r_squared}")
Fig 7 - Average Temperature, size by Accidents rate & color by Day/Night
import pandas as pd
from scipy.stats import linregress
data = {
'City': ['Atlanta', 'Aurora', 'Chicago', 'Houston', 'Los Angeles', 'Portland', 'San Diego', 'Springfield', 'Tampa', 'Washington DC'],
'TreeCoverPct': [5925.17, 53885.98, 181.65, 25.52, 38264.61, 1710.83, 188.63, 1641.45, 32993.15, 123220.00],
'AverageTemperature': [65.53, 50.11, 54.19, 72.35, 65.86, 56.44, 65.80, 59.97, 74.52, 61.38]
}
df = pd.DataFrame(data)
correlation = df['TreeCoverPct'].corr(df['AverageTemperature'])
slope, intercept, r_value, p_value, std_err = linregress(df['TreeCoverPct'], df['AverageTemperature'])
print(f"Correlation coefficient (r): {correlation}")
print(f"P-value: {p_value}")
print(f"R-squared: {r_value**2}")
Fig 8 -
import pandas as pd
from scipy.stats import pearsonr
data = {
'City': ['Tampa', 'Houston', 'Los Angeles', 'San Diego', 'Atlanta', 'Washington DC', 'Springfield', 'Portland', 'Aurora', 'Chicago'],
'TrafficSignal': [22782, 138301, 120368, 43257, 31613, 16303, 7359, 29031, 3558, 14871],
'Junction': [7477, 29396, 34927, 11782, 28114, 1174, 235, 4194, 1370, 18031],
'Crossing': [7319, 1102, 939, 349, 2556, 810, 61, 802, 185, 1020],
'Station': [386, 629, 257, 116, 146, 141, 34, 807, 91, 331],
'Roundabout': [32, 9, 1, 1, 11, 1, 1, 3, 1, 2],
'TreeCoverPct': [32993.15, 25.52, 38264.61, 188.63, 5925.17, 123220.00, 1641.45, 1710.83, 53885.98, 181.65]
}
df = pd.DataFrame(data)
# Calculate Urban-Related Accidents
df['UrbanRelated'] = df['TrafficSignal'] + df['Junction'] + df['Crossing'] + df['Station']
# Calculate Total Accidents (Correctly)
df['TotalAccidents'] = df[['TrafficSignal', 'Junction', 'Crossing', 'Station', 'Roundabout']].sum(axis=1)
# Calculate Proportion of Urban-Related Accidents
df['ProportionUrban'] = df['UrbanRelated'] / df['TotalAccidents']
# Calculate Correlation and P-value
correlation, p_value = pearsonr(df['ProportionUrban'], df['TreeCoverPct'])
print(f"Correlation coefficient (r): {correlation}")
print(f"P-value: {p_value}")
r_squared = correlation**2
print(f"R-squared: {r_squared}")
Fig 9 -
import pandas as pd
from scipy.stats import pearsonr
data = {
'City': ['Atlanta', 'Aurora', 'Chicago', 'Houston', 'Los Angeles', 'Portland', 'San Diego', 'Springfield', 'Tampa', 'Washington DC'],
'TotalAccidents': [62429, 5204, 34253, 169428, 156491, 34834, 55504, 7689, 31192, 18428],
'sq_km': [353.4, 400.7, 606.1, 1627.0, 1302.0, 370.4, 963.0, 201.0, 447.0, 158.0]
}
df = pd.DataFrame(data)
correlation, p_value = pearsonr(df['TotalAccidents'], df['sq_km'])
print(f"Correlation coefficient (r): {correlation}")
print(f"P-value: {p_value}")
r_squared = correlation**2
print(f"R-squared: {r_squared}")
Fig 10-
import pandas as pd
from scipy.stats import pearsonr
data = {
'City': ['Atlanta', 'Aurora', 'Chicago', 'Houston', 'Los Angeles', 'Portland', 'San Diego', 'Springfield', 'Tampa', 'Washington DC'],
'Severity1': [146, 91, 331, 629, 257, 807, 116, 34, 547, 141],
'Severity2': [31613, 3558, 14871, 138301, 120368, 29031, 43257, 7359, 22782, 16303],
'Severity3': [28114, 1370, 18031, 29396, 34927, 4194, 11782, 235, 7477, 1174],
'Severity4': [2556, 185, 1020, 1102, 939, 802, 349, 61, 386, 810],
'TreesSqkmPct': [5925.17, 53885.98, 181.65, 25.52, 38264.61, 1710.83, 188.63, 1641.45, 32993.15, 123220.00],
'Population': [4987158, 200545, 2746388, 2325502, 3971883, 652898, 1386932, 116893, 3843152, 705749],
'sq_km': [353.4, 400.7, 606.1, 1627.0, 1302.0, 370.4, 963.0, 201.0, 447.0, 158.0]
}
df = pd.DataFrame(data)
# Calculate Average Severity
df['TotalAccidents'] = df[['Severity1', 'Severity2', 'Severity3', 'Severity4']].sum(axis=1)
df['WeightedSeverity'] = df['Severity1'] * 1 + df['Severity2'] * 2 + df['Severity3'] * 3 + df['Severity4'] * 4
df['AvgSeverity'] = df['WeightedSeverity'] / df['TotalAccidents']
# Calculate Trees/SqKm (Tree Density)
df['TreesSqKm'] = df['TreesSqkmPct'] / df['sq_km']
# Calculate Pop/SqKm (Population Density)
df['PopSqKm'] = df['Population'] / df['sq_km']
# Calculate Correlation: AvgSeverity vs. TreesSqKm
correlation_trees, _ = pearsonr(df['AvgSeverity'], df['TreesSqKm'])
print(f"Correlation (AvgSeverity vs. TreesSqKm): {correlation_trees}")
# Calculate Correlation: AvgSeverity vs. PopSqKm
correlation_pop, _ = pearsonr(df['AvgSeverity'], df['PopSqKm'])
print(f"Correlation (AvgSeverity vs. PopSqKm): {correlation_pop}")
Fig 11
# Reloading and cleaning the dataset to ensure proper data types and no missing values
import pandas as pd
import numpy as np
import statsmodels.api as sm
import statsmodels.stats.api as sms
from statsmodels.stats.outliers_influence import variance_inflation_factor
# Simulating a clean dataset again
np.random.seed(42)
data = {
'Tree_Density': np.random.uniform(0, 100, 100),
'Car_Accident_Rate': np.random.uniform(0, 50, 100),
'Population_Density': np.random.uniform(100, 1000, 100),
'Area_Size': np.random.uniform(1, 100, 100),
'Precipitation': np.random.uniform(0, 200, 100),
'Temperature': np.random.uniform(-10, 40, 100),
'Demographics': np.random.choice(['Urban', 'Suburban', 'Rural'], 100)
}
# Creating a DataFrame
df = pd.DataFrame(data)
# Encoding categorical variable 'Demographics'
df_encoded = pd.get_dummies(df, columns=['Demographics'], drop_first=True)
# Defining response and predictors
y = df_encoded['Car_Accident_Rate']
X = df_encoded.drop('Car_Accident_Rate', axis=1)
# Adding constant
X = sm.add_constant(X)
# Ensuring all columns are numeric
X = X.apply(pd.to_numeric, errors='coerce')
y = pd.to_numeric(y, errors='coerce')
# Dropping rows with missing values
X = X.dropna()
y = y[X.index]
# Fitting the regression model
model = sm.OLS(y, X).fit()
print(model.summary())
# Residuals analysis: Breusch-Pagan test
bp_test = sms.het_breuschpagan(model.resid, model.model.exog)
labels = ['Lagrange multiplier statistic', 'p-value', 'f-value', 'f p-value']
print("Breusch-Pagan Test Results:", dict(zip(labels, bp_test)))
# Checking multicollinearity via Variance Inflation Factor (VIF)
vif_data = pd.DataFrame()
vif_data['feature'] = X.columns
vif_data['VIF'] = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]
print("Variance Inflation Factor (VIF):")
print(vif_data)
—-----------
# Check data types of X and y to diagnose the issue
print('X data types:')
print(X.dtypes)
print('\
y data type:')
print(y.dtypes)
print('Inspecting first few rows of X:')
print(X.head())
print('Inspecting first few rows of y:')
print(y.head())
print('Data type inspection complete.')
—----------
# Converting boolean dummy columns to integers
bool_columns = ['Demographics_Suburban', 'Demographics_Urban']
for col in bool_columns:
X[col] = X[col].astype(int)
# Fit the regression model again
model = sm.OLS(y, X).fit()
print(model.summary())
# Residuals analysis: Breusch-Pagan test
bp_test = sms.het_breuschpagan(model.resid, model.model.exog)
labels = ['Lagrange multiplier statistic', 'p-value', 'f-value', 'f p-value']
print("Breusch-Pagan Test Results:", dict(zip(labels, bp_test)))
# Checking multicollinearity via Variance Inflation Factor (VIF)
vif_data = pd.DataFrame()
vif_data['feature'] = X.columns
vif_data['VIF'] = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]
print("Variance Inflation Factor (VIF):")
print(vif_data)
print('Diagnostics for Sections 2 and 3 complete.')
—---------
Breusch-Pagan Test Results
Breusch-Pagan Test Results:
{'Lagrange multiplier statistic': 4.375727854229206, 'p-value': 0.735625408133733, 'f-value': 0.6014118046983608, 'f p-value': 0.7532968837974501}
—---------
const | Tree_Density | Population_Density | Area_Size | Precipitation | Temperature | Demographics_Suburban | Demographics_Urban |
1 | 37.45401188 | 677.8284815 | 6.116490396 | 20.62477377 | 24.9080857 | 0 | 1 |
1 | 95.07143064 | 175.7259685 | 53.60410853 | 180.5105813 | 16.80481832 | 1 | 0 |
1 | 73.19939418 | 245.4658427 | 54.52287704 | 101.0504745 | 5.476380814 | 1 | 0 |
1 | 59.86584842 | 908.6987697 | 64.10556025 | 165.2914932 | 30.68975099 | 0 | 1 |
1 | 15.60186404 | 645.7861537 | 72.88304204 | 64.00992021 | 24.23655863 | 1 | 0 |
1 | 15.59945203 | 108.2773465 | 97.60935587 | 179.1046457 | -1.869153033 | 0 | 1 |
1 | 5.808361217 | 191.3243886 | 52.11373448 | 77.84033575 | 35.54635922 | 1 | 0 |
1 | 86.61761458 | 697.1515922 | 32.97269082 | 2.167530296 | 31.12686215 | 0 | 1 |
1 | 60.11150117 | 104.5554255 | 79.72343328 | 181.0763953 | 37.48999566 | 0 | 1 |
1 | 70.80725778 | 244.7272463 | 27.81239287 | 18.25733536 | 26.28597542 | 0 | 1 |
1 | 2.05844943 | 593.8604104 | 44.45817065 | 63.86272752 | 20.6707598 | 1 | 0 |
1 | 96.99098522 | 722.7056779 | 8.767181753 | 190.0123934 | 10.91215181 | 1 | 0 |
1 | 83.24426408 | 686.7651336 | 3.509723598 | 190.1214294 | 36.63642417 | 1 | 0 |
1 | 21.23391107 | 301.8423785 | 96.30219305 | 114.6875776 | 33.30319448 | 0 | 1 |
1 | 18.18249672 | 740.9612992 | 83.76203193 | 126.3674424 | -7.739066495 | 1 | 0 |
1 | 18.34045099 | 313.5241787 | 69.9014464 | 89.6891044 | -8.681651275 | 1 | 0 |
1 | 30.4242243 | 392.8597283 | 41.4863415 | 58.64215434 | 8.823168344 | 0 | 1 |
1 | 52.47564316 | 771.8422646 | 18.15613769 | 65.73290907 | 30.52766654 | 0 | 0 |
1 | 43.19450186 | 684.6696091 | 16.48726722 | 134.5036912 | 39.36380647 | 0 | 1 |
1 | 29.12291402 | 864.3010694 | 25.77404692 | 150.4749059 | -2.479155445 | 1 | 0 |
1 | 61.18528947 | 691.8516031 | 55.37343981 | 158.3158087 | 19.70653577 | 0 | 1 |
1 | 13.94938607 | 611.477743 | 71.74499635 | 157.9236286 | 9.044542832 | 0 | 0 |
1 | 29.21446485 | 184.307291 | 66.3595403 | 18.24122061 | 38.49571989 | 0 | 1 |
1 | 36.63618433 | 430.9442228 | 28.7134558 | 98.88406094 | 32.10594616 | 1 | 0 |
1 | 45.60699842 | 338.6821309 | 95.53166279 | 11.511752 | 31.91643524 | 0 | 0 |
1 | 78.51759614 | 319.590679 | 74.05179475 | 109.9057765 | 13.43465799 | 1 | 0 |
1 | 19.96737822 | 975.7094993 | 55.8810512 | 88.30610027 | 10.74097512 | 0 | 0 |
1 | 51.42344384 | 453.7879522 | 61.56035388 | 177.5408366 | 3.670353597 | 0 | 0 |
1 | 59.24145689 | 902.8418997 | 42.54040618 | 70.18300251 | -7.181225167 | 0 | 1 |
1 | 4.645041272 | 668.0247634 | 25.52536796 | 23.41340329 | 33.23611881 | 0 | 1 |
1 | 60.75448519 | 815.3301732 | 36.24129519 | 28.59833641 | 30.64505046 | 1 | 0 |
1 | 17.05241237 | 552.3733838 | 76.02676494 | 152.3021263 | 39.98588366 | 0 | 1 |
1 | 6.505159299 | 619.2134962 | 2.424955374 | 123.6436127 | 39.83184185 | 0 | 1 |
1 | 94.88855373 | 543.2659244 | 12.49119141 | 20.22453522 | 17.77158528 | 0 | 0 |
1 | 96.56320331 | 275.718689 | 5.55426156 | 16.82136122 | 28.44937076 | 0 | 1 |
1 | 80.83973481 | 750.2069037 | 5.03215143 | 140.1938263 | 37.23828649 | 0 | 0 |
1 | 30.46137692 | 352.6951262 | 85.69059782 | 14.55260127 | 32.48236953 | 0 | 0 |
1 | 9.767211401 | 121.8843698 | 70.66212808 | 164.3720119 | 2.367405087 | 0 | 0 |
1 | 68.42330265 | 680.9250663 | 47.94320908 | 141.2484454 | 12.52720677 | 1 | 0 |
1 | 44.01524937 | 259.3996115 | 10.6855819 | 16.26975613 | -3.542029242 | 1 | 0 |
1 | 12.20382348 | 946.4127259 | 49.66997164 | 16.96754282 | 37.70255136 | 1 | 0 |
1 | 49.51769101 | 958.5357193 | 47.87370531 | 197.3279157 | 20.30873172 | 0 | 0 |
1 | 3.438852112 | 923.3779512 | 18.14698512 | 74.85415915 | 1.432140275 | 1 | 0 |
1 | 90.93204021 | 433.1428302 | 43.95131327 | 74.12842941 | 23.58503422 | 1 | 0 |
1 | 25.87799816 | 113.9109549 | 40.45196871 | 162.5599135 | 20.90641202 | 0 | 1 |
1 | 66.25222844 | 935.4867063 | 61.96915971 | 189.4497155 | 7.908135902 | 1 | 0 |
1 | 31.17110761 | 485.3657335 | 63.87427144 | 197.2002128 | -4.32212039 | 1 | 0 |
1 | 52.00680212 | 969.9893371 | 5.485096967 | 150.6756371 | 23.57865978 | 0 | 0 |
1 | 54.67102793 | 967.2579794 | 38.08664885 | 75.25191711 | 16.01538505 | 0 | 0 |
1 | 18.48544555 | 867.7085099 | 62.96013166 | 16.70014334 | 28.61591959 | 0 | 0 |
1 | 96.95846278 | 365.0040029 | 50.8104896 | 155.4293832 | 16.00817506 | 0 | 0 |
1 | 77.51328234 | 446.5879557 | 85.79249428 | 111.6808499 | 32.60907502 | 0 | 0 |
1 | 93.94989416 | 866.0230044 | 66.21066953 | 84.84440185 | 17.59534194 | 0 | 0 |
1 | 89.48273504 | 385.2298046 | 17.13050828 | 181.270877 | 18.04689858 | 1 | 0 |
1 | 59.78999788 | 252.543472 | 7.986305993 | 22.23949646 | 33.83268013 | 0 | 0 |
1 | 92.1874235 | 601.1211362 | 64.59950854 | 98.52502086 | 10.17414331 | 0 | 0 |
1 | 8.849250205 | 942.5392967 | 3.624619744 | 2.270728953 | -3.299238577 | 0 | 1 |
1 | 19.59828624 | 726.426817 | 58.99178255 | 93.7321284 | -8.560866184 | 1 | 0 |
1 | 4.522728891 | 613.0550531 | 94.0827939 | 11.26065514 | 27.75686278 | 1 | 0 |
1 | 32.53303308 | 187.4588444 | 57.97194361 | 23.76358325 | 21.01547757 | 1 | 0 |
1 | 38.86772897 | 653.506504 | 39.42882269 | 23.50524936 | 25.2039884 | 0 | 0 |
1 | 27.13490318 | 991.0484651 | 64.68553363 | 129.8420604 | 0.6482080754 | 0 | 0 |
1 | 82.87375092 | 226.0756137 | 46.36703616 | 149.2089759 | -3.181426221 | 0 | 0 |
1 | 35.67533267 | 566.4966871 | 55.01606214 | 116.673753 | -9.272766717 | 0 | 1 |
1 | 28.09345097 | 889.6357647 | 94.20501607 | 192.4345097 | 7.52937794 | 0 | 0 |
1 | 54.26960832 | 766.691756 | 39.22416114 | 74.9741159 | 19.49588434 | 0 | 1 |
1 | 14.0924225 | 727.3141669 | 96.15786582 | 57.14241726 | 9.612202255 | 1 | 0 |
1 | 80.21969808 | 732.2356756 | 90.62971355 | 173.7198256 | 11.8737461 | 0 | 0 |
1 | 7.455064368 | 423.5420361 | 20.38332234 | 44.7191677 | 35.20793472 | 0 | 1 |
1 | 98.68869366 | 364.2326598 | 7.866768787 | 192.6445079 | 7.412773351 | 0 | 1 |
1 | 77.22447693 | 828.4250399 | 10.97702214 | 2.430894938 | 15.69947446 | 0 | 0 |
1 | 19.87156815 | 829.1020552 | 2.80396074 | 193.9757653 | 29.18265064 | 1 | 0 |
1 | 0.5522117124 | 880.3650867 | 10.34985311 | 8.63198239 | 9.827139116 | 1 | 0 |
1 | 81.54614285 | 921.9164973 | 68.61767057 | 178.2286227 | 21.10433501 | 1 | 0 |
1 | 70.68573438 | 560.208159 | 8.047676198 | 105.5402218 | 33.11818544 | 0 | 1 |
1 | 72.9007168 | 551.3646652 | 32.5785874 | 198.5929592 | 37.47603118 | 0 | 1 |
1 | 77.12703467 | 818.4656611 | 84.64265579 | 14.75931295 | -2.646325954 | 0 | 1 |
1 | 7.404465173 | 684.9675377 | 3.303921638 | 110.7708569 | 36.32938126 | 0 | 1 |
1 | 35.84657285 | 731.7701895 | 81.63237978 | 193.8605071 | 14.60581465 | 1 | 0 |
1 | 11.58690595 | 816.2134025 | 28.9036227 | 104.6195688 | 2.912219415 | 0 | 0 |
1 | 86.31034259 | 901.0048076 | 12.69831793 | 125.8797276 | 12.95678781 | 0 | 1 |
1 | 62.32981268 | 404.1956412 | 69.97697937 | 139.1497378 | 39.00162876 | 0 | 1 |
1 | 33.08980249 | 438.0246574 | 63.26534183 | 90.90821295 | 14.6309047 | 1 | 0 |
1 | 6.355835029 | 184.5837459 | 87.86972934 | 125.511616 | 6.437580514 | 0 | 0 |
1 | 31.09823217 | 620.4521269 | 73.77203334 | 116.8628624 | 21.67004272 | 0 | 0 |
1 | 32.5183322 | 132.3480464 | 80.54461211 | 180.2316021 | 2.007280939 | 1 | 0 |
1 | 72.96061783 | 519.0382163 | 28.92142268 | 9.089276068 | -6.206833595 | 1 | 0 |
1 | 63.75574714 | 588.3801712 | 18.56651483 | 56.19263792 | -3.556013904 | 0 | 0 |
1 | 88.72127426 | 357.8871269 | 75.31086041 | 190.0822968 | -3.597708052 | 0 | 1 |
1 | 47.22149252 | 631.7499345 | 80.87663919 | 178.0527568 | -2.404865324 | 0 | 0 |
1 | 11.95942459 | 127.4502249 | 99.06000906 | 91.13135056 | -3.058641368 | 1 | 0 |
1 | 71.32447872 | 133.6133699 | 41.84915001 | 124.0265196 | 22.04373724 | 0 | 1 |
1 | 76.07850486 | 840.3405046 | 37.82979049 | 55.4762366 | -0.90599578 | 0 | 1 |
1 | 56.12771976 | 424.1715773 | 77.86488311 | 37.62423194 | 7.283364166 | 1 | 0 |
1 | 77.096718 | 214.3544614 | 34.73955049 | 92.73968099 | 34.8394205 | 1 | 0 |
1 | 49.37955964 | 570.018934 | 93.14497523 | 70.67044561 | 13.69808201 | 1 | 0 |
1 | 52.27328294 | 792.9941978 | 85.98286243 | 116.7312224 | 23.37788693 | 0 | 0 |
1 | 42.75410184 | 294.2389247 | 43.47040871 | 15.54692739 | -1.38400644 | 0 | 0 |
1 | 2.541912674 | 660.6014282 | 75.33623571 | 194.8789615 | -0.3855490596 | 0 | 1 |
1 | 10.7891427 | 176.8127185 | 75.69974453 | 197.2421489 | -7.956569187 | 1 | 0 |
Washington DC (27.7%) vs. San Diego (0.10%):
Percentage Difference=∣27.7−0.10∣27.7+0.102×100=27.613.85×100≈199.42%\text{Percentage Difference} = \frac{|27.7 - 0.10|}{\frac{27.7 + 0.10}{2}} \times 100 = \frac{27.6}{13.85} \times 100 \approx 199.42\%
Los Angeles (18.4%) vs. Houston (0.07%):
Percentage Difference=∣18.4−0.07∣18.4+0.072×100=18.339.235×100≈198.97%\text{Percentage Difference} = \frac{|18.4 - 0.07|}{\frac{18.4 + 0.07}{2}} \times 100 = \frac{18.33}{9.235} \times 100 \approx 198.97\%
Aurora (14%) vs. San Diego (0.10%):
Percentage Difference=∣14−0.10∣14+0.102×100=13.907.05×100≈197.16%\text{Percentage Difference} = \frac{|14 - 0.10|}{\frac{14 + 0.10}{2}} \times 100 = \frac{13.90}{7.05} \times 100 \approx 197.16\%Percentage Difference=214+0.10∣14−0.10∣×100=7.0513.90×100≈197.16%
Portland (0.31%) vs. Tampa (6%):
Percentage Difference=∣0.31−6∣0.31+62×100=5.693.155×100≈180.76%\text{Percentage Difference} = \frac{|0.31 - 6|}{\frac{0.31 + 6}{2}} \times 100 = \frac{5.69}{3.155} \times 100 \approx 180.76\%Percentage Difference=20.31+6∣0.31−6∣×100=3.1555.69×100≈180.76%
Chicago (16%) vs. Tampa (6%):
Percentage Difference=∣16−6∣16+62×100=1011×100≈90.91%\text{Percentage Difference} = \frac{|16 - 6|}{\frac{16 + 6}{2}} \times 100 = \frac{10}{11} \times 100 \approx 90.91\%Percentage Difference=216+6∣16−6∣×100=1110×100≈90.91%
Washington DC (2.67) vs. San Diego (4.00):
Percentage Difference=∣2.67−4.00∣2.67+4.002×100=1.333.335×100≈39.89%\text{Percentage Difference} = \frac{|2.67 - 4.00|}{\frac{2.67 + 4.00}{2}} \times 100 = \frac{1.33}{3.335} \times 100 \approx 39.89\%
Los Angeles (4.01) vs. Houston (7.35):
Percentage Difference=∣4.01−7.35∣4.01+7.352×100=3.345.68×100≈58.87%\text{Percentage Difference} = \frac{|4.01 - 7.35|}{\frac{4.01 + 7.35}{2}} \times 100 = \frac{3.34}{5.68} \times 100 \approx 58.87\%
Aurora (1.35) vs. San Diego (4.00):
Percentage Difference=∣1.35−4.00∣1.35+4.002×100=2.652.675×100≈99.12%\text{Percentage Difference} = \frac{|1.35 - 4.00|}{\frac{1.35 + 4.00}{2}} \times 100 = \frac{2.65}{2.675} \times 100 \approx 99.12\%Percentage Difference=21.35+4.00∣1.35−4.00∣×100=2.6752.65×100≈99.12%
Portland (5.34) vs. Tampa (8.10):
Percentage Difference=∣5.34−8.10∣5.34+8.102×100=2.766.22×100≈44.38%\text{Percentage Difference} = \frac{|5.34 - 8.10|}{\frac{5.34 + 8.10}{2}} \times 100 = \frac{2.76}{6.22} \times 100 \approx 44.38\%Percentage Difference=25.34+8.10∣5.34−8.10∣×100=6.222.76×100≈44.38%
Chicago (7.35) vs. Houston (7.35):
Percentage Difference=∣7.35−7.35∣7.35+7.352×100=0%\text{Percentage Difference} = \frac{|7.35 - 7.35|}{\frac{7.35 + 7.35}{2}} \times 100 = 0\%Percentage Difference=27.35+7.35∣7.35−7.35∣×100=0%
