San Francisco Department of Public Health - Program on Health, Equity and Sustainability
TransBASESF.org: Linking Transportation Systems to Our Health
Every year in San Francisco, about 30 people lose their lives and over 500 more are seriously injured while traveling on city streets. These deaths and injuries are unacceptable and preventable, and San Francisco is committed to stopping further loss of life. San Francisco, California has one of the highest pedestrian fatality rates in the nation. It ranks fourth of all counties nationally, third of large counties, and first in California. In San Francisco, approximately half of fatal injuries in motor vehicle collisions are suffered by people walkingcompared to 14% nationally. Approximately 20 pedestrians die each year in motor vehicle collisions, and over 800 pedestrians are injured annually. Transportation system-related injuries and deaths have high associated social and medical costs, with pedestrian injury-related hospital costs at San Francisco General Hospital alone estimated at $15 million annually.
The collection and analysis of transportation and health data, especially in geospatial terms, can be a complex process. TransBASESF.org is a geospatially enabled analytics database developed by the San Francisco Department of Public Health (SFDPH) that is used to analyze, evaluate, monitor, and communicate transportation safety issues in San Francisco, California. TransBASESF.org's goal is to serve as the central data repository for public health-related transportation data; to be a free and open data resource for the general public to use; and to support interagency collaboration, data standards, and data sharing within the City and County of San Francisco. TransBASESF.org currently includes over 200 spatially referenced variables from multiple agencies and across a range of geographic scales, including infrastructure, transportation, zoning, sociodemographic, and collision data, all linked to an intersection or street segment. TransBASESF.org's origin was as a tool developed to facilitate Health Impact Assessments and better understand the geographic distribution and characteristics of pedestrian injuries. Its scope and role have continued to evolve and the database now plays a key role in the implementation of San Francisco's Vision Zero policy, an effort to reduce all modes of transportation-related fatalities to zero by 2024.
The goal was to create a product that would allow our partners to understand spatially correlated environmental factors that contribute to transportation system safety can inform targeted, efficient safety practices. In addition, TransBASESF.org was developed to be system agnostic and allow interoperability with various software packages and compliance with widely-used GIS standards. Data is available in standardized formats from OGC, ISO, and other GIS Standards bodies with no licensing required. This allows the data to be accessed and analyzed on various software packages and does not lock data usage into any one system. Community and civic tech groups especially benefit from not needing to purchase expensive software packages to use TransBASESF.org data.
TransBASESF.org has been used for many projects in San Francisco to date. An application example is WalkFirst: a strategic framework for delivering pedestrian safety capital improvement projects, for which SFDPH partnered with San Francisco's transportation and planning agencies. Using data from TransBASESF.org, the project combined public outreach with statistical analysis of where and why pedestrian collisions occur using a combination of collision and built environment variables. These injury patterns were then matched to specific engineering measures using a cost benefit analysis approach. WalkFirst provided a roadmap of where to prioritize needed pedestrian safety projects and potential measures to reduce serious pedestrian injuries and fatalities. By having a centralized geospatial data repository readily available, TransBASESF.org allowed the WalkFirst team to skip collecting and developing datasets for the project, saving significant time and resources. TransBASESF.org helped the team to focus their efforts on data analysis, easing time and financial constraints on a deadline-oriented project.
While its origins are in pedestrian safety, TransBASESF.org is continuously being developed to inform a comprehensive approach to understanding health impacts of transportation systems, including safety, access, physical activity, air and noise quality, and health disparities. TransBASESF.org is part of a larger SFDPH-PHES effort to improve city services through San Francisco's Open Data Policy. The goal of open data initiatives is to keep the San Francisco community informed, connected, and engaged with government. To that end, SFDPH-PHES is working to improve the content and accessibility of TransBASESF.org in coordination with City agencies and community partners so that it can help address transportation system safety, sustainability, community health, and equity in San Francisco.
TransBASESF.org Data Portal: http://transbasesf.org/.
According to the U.S. Census Bureau, San Francisco has an estimated population of 870,887 as of 2017 and is 41 percent white, 35 percent Asian, 6 percent African American, and 15 percent Hispanic or Latino of any race. More than a third of city residents (36 percent) were born outside the United States. 15 percent of the population is 65 years or older while 14 percent of the population is under 18 years old. In addition, the city sees over 24.6 million visitors a year and nearly half a million San Francisco and Bay Area residents commute in and out of San Francisco each day, with approximately 330,000 non-San Francisco residents commuting into the city for work and 113,000 San Francisco residents commuting elsewhere.
As of 2015, the SFMTA Travel Decision Survey showed that an estimated 54 percent of all trips to, from, or within San Francisco took place by non-private auto modes. 11 percent of trips to work in the city are walking compared to 3 percent nationally, while 34 percent of all workers in San Francisco County used public transportation -- excluding taxicab -- to get to their job, compared with 5.0 percent in the nation as a whole. About 4 percent of all workers in the county biked to work, compared with 0.6 percent nationally.
The overall number of traffic deaths in 2016 is 30, compared to 31 in 2015 and 2014. Pedestrians made up the largest number of deaths, comprising over 50% of all traffic fatalities. Over 40% of all traffic fatalities were suffered by seniors. The most frequent primary collision factors for fatalities included red light running, vehicle failure to yield to pedestrians in a crosswalk, and speeding. Preliminary data from the National Highway Traffic Safety Administration (NHTSA) points to the number of traffic deaths rising in other cities nationwide. Road deaths in the U.S. rose 8 percent in the first nine months of 2016 compared to the same period in 2015, driven by increases in pedestrian, bicyclist and motorcyclist deaths. These injuries and deaths are highly concentrated within the city, with only 13 percent of streets accounting for 75 percent of traffic related injuries resulting in a severe injury or fatality and 61 percent of all traffic collisions where a party was injured. In addition, over half of this High Injury Networkâ€ is located in communities of concern, areas of San Francisco that have a high proportion of the population socio-economic disadvantaged.
Previous projects in this field include the work done at the Safe Transportation Research and Education Center (SafeTrec) at UC Berkeley. SafeTrec has implemented a full-fledged linear referencing system for mapping collisions in California along with a web-based interface tool (Transportation Injury Mapping System - TIMS) to query and download data. TIMS provides a map-centric approach to viewing and querying collision data and includes options to layer on additional information like schools, transportation districts, and census tracts. TIMS helped inform the creation of TransBASESF.org, especially the ability to quickly query and map collision data. TransBASESF.org takes TIMS one step further by integrating built-environment data into its query tools.
Previous successful implementations of geospatial databases in a transportation context often fall into three categories: Asset management databases that keep track of physical assets like signals or signage; project tracking and coordination databases showing locations of existing or future projects; and statewide databases used to report and track injury data collected from various police jurisdictions (7). TransBASESF.org seeks to bridge these different database models and create a comprehensive analytical database for understanding the spatial relationship between injuries and socio-environmental factors in San Francisco, California.
SFDPH originally sought to create a relational database system to integrate collision and injury data with existing environmental (including but not limited to transportation factors) and sociodemographic data. While initially developed to harness data SFDPH had been collecting for health impact assessments of transportation policy and support pedestrian safety initiatives, TransBASESF.org was designed to also serve other transportation and health initiatives. Its focus is more narrow and specialized than DataSF, the official open data portal for the City of San Francisco, as it seeks to be a central repository for only transportation systems safety and health-related data and is specifically developed to facilitate geospatial analysis.
TransBASESF.org began as a pedestrian safety-oriented Geodatabase, a proprietary Esri personal geospatial database developed to keep track of transportation data collected for a health impact assessment on a potential congestion pricing policy for San Francisco and for the implementation of a Mayoral Directive on Pedestrian Safety. Although useful for geospatial analysis, this system was limited in three major ways: the geodatabase was a standalone file that was only accessible to those with access to SFDPH's internal network; the database was a proprietary Esri personal geodatabase and lacked the ability to be used by different software packages without first transforming and exporting data; and data within the database was not easily sharable and lacked advance features common to more robust relational database management systems (e.g. multiple users, triggers, advance functions).
The TransBASESF.org system was thus developed to support SFDPH's continued work on health impact assessment and Health in All Policies work specifically focused on transportation safety in San Francisco. It is innovative for the following reasons: It is a single, authoritative spatially enabled PostgresSQL/PostGIS relational database management system; it contains a web-based application portal that allows users to view TransBASESF.org feature layers through an Open Geospatial Consortium (OGC) compliant web mapping service (WMS) tile provider and query feature layers by attribute, location, or overlap with other features in TransBASESF.org through a web feature service (WFS) provider; and it allows for the ability to use an Open Database Connectivity (ODBC) connection to allow other software systems access to the underlying database. Both geographic-based feature layers and database tables are made available and can be accessed through either a GIS-based systems (e.g. ESRI, QGIS), table/spreadsheet-based systems (i.e. Access, R Studio), or any scripting language with ODBC compatibility.
TransBASESF.org currently includes over 200 spatially referenced variables across a range of geographic scales. SFDPH first developed TransBASESF.org to support initiatives for pedestrian safety, but it has evolved to serve other projects including San Francisco's Vision Zero initiative, with the goal of eliminating traffic deaths by 2024. TransBASESF.org's data-driven approach is informed by a large and growing evidence base regarding the importance of transportation system design and land use decisions for traffic safety and sustainable transportation behaviors. TransBASESF.org is also an open data repository for transportation safety data which is freely accessible to the public.
All data in TransBASESF.org is related to either an intersection or a street segment. Datasets can represent either the exact location of a feature or an aggregation of a data variable within a geographic boundary (e.g. count of all senior centers within a quarter mile). When data tables are joined to a street or intersection datasets, a geospatially enabled view is created and can be used in a client application. Users with a technical understanding of standard query language (SQL) can create their own views of the data in TransBASESF.org including performing geospatial queries through PostGIS. These queries can be symbolized and saved as layers for display in a GIS platform.
In addition data in TransBASESF.org is accessible via a web portal. This web portal enables the user to view data layers in TransBASESF.org including stacking order and transparency; perform basic queries including spatial overlays, multivariable attribute definition queries, and spatial boundary searches; view and search metadata either though a table or by clicking layers in the layer contents menu; create PDF maps of data layers; and obtain more information on connecting to the database directly though ODBC. The web portal has been especially useful to community groups who do not have the technical capabilities to do in-depth GIS analysis but want to better understand the distribution of injuries around San Francisco. The website gives all users the ability to explore and visualize injuries through a simplistic interface while not requiring familiarity the schema structure of the backend database.
Another innovative feature is that all the components that make up TransBASESF.org are open source software. This eliminates costs associated with proprietary software packages and makes replicating the system inexpensive for other jurisdictions. TransBASESF.org is hosted on GitHub, freely available for download, and maintains a GNU General Public License. This encourages modification and community collaboration on developing the system's source code. Using open source software has allowed the greatest amount of flexibility and customization for project development.
SFDPH advocates for open data to increase: the transparency of data inputs used in important transportation policy decisions; the social value of data being produced with tax payer funding; and participatory governance and advocacy by citizen groups interested in reducing transportation system-related injuries. TransBASESF.org, as an integral part of Vision Zero data goals for San Francisco, has also served as a catalyst to better develop, document, and maintain engineering project information for evaluation purposes.
Motor Vehicle Injuries
The three major goals of TransBASESF.org are to: 1) Facilitate a data-driven approach to understanding and addressing transportation-related health issues, informed by a large and growing evidence base regarding the importance of transportation system design and land use decisions for health; 2) Serve as a central open source data repository for all public health related transportation data within San Francisco, and to support interagency collaboration, data standards, and data sharing; and 3) Inform public and private efforts to improve transportation system safety, sustainability, community health and equity in San Francisco.
In addition, six major system architecture goals were considered in the creation of the system. Distribution: The database should not be isolated at any one department and inaccessible to all other departments in the city. TransBASESF.org was originally hosted on Amazon's Elastic Cloud Computing Service while being developed and is now hosted on a virtual cloud server maintained by the city's Department of Technology. Interoperability: The database should be accessible to as many different software platforms as possible and not be locked into any proprietary licensed format. Accessibility: All data within the system should have a publicly accessible portal. Integration: Other City databases and datasets should be integrated into the system. Architecture: The project should be open source to keep down costs and encourage replication by others. Flexibility: Database tables and geospatial should be able to be easily maintainable and updatable.
After a prototype and proof of concept of the database was developed, a Technical Advisory Committee (TAC) was established early in the project to solicit suggestions and feedback from agencies within the city. Representatives who routinely work with geospatial data from the Municipal Transportation Agency (SFMTA), Planning Department (CPC), Department of Public Works (SFDPW), Department of Technology (SFDT), and the County Transportation Authority (SFCTA) were invited to participate in workshops focusing on key topics including: data acquisition, metadata standardization, data sharing agreements, and user interface development. The TAC also provided support debugging the initial release of TransBASESF.org and reviewing system documentation. All participating TAC agencies are now active users of TransBASESF.org in their work to understand and address transportation-related safety issues in San Francisco.
The Community Advisory Committee (CAC) that consisted of representatives from the tech community, transportation safety community groups, and academics was also formed early in the project. Similar to the TAC, the CAC provided valuable insights on what participants would find most useful in TransBASESF.org, user experience, and troubleshooting bugs present during the development phase. The CAC was also essential for raising awareness of TransBASESF.org in the general public. CBOs including the San Francisco Bicycle Coalition and Walk SF now routinely use TransBASESF.org in their work to educate the public regarding transportation safety issues.
Through the TAC and CAC process it became apparent that three different sets of potential users existed with different needs that the system would have to address. SFDPH departmental staff: These users may wish to view and download transportation safety related data in order to perform analysis or provide maps in reports. Departmental staff may use the web based portal application to query, display, and print data on a map. Geographic Information Systems (GIS) staff at SFDPH or other City departments: This user group may integrate TransBASESF.org data with other geospatial datasets using desktop GIS applications. GIS staff will have read-only access to data stored in TransBASESF.org. General public: This user group consists of individuals or groups in the general public who have an interest in doing research on or advocating for transportation safety issues in the City. This group may use the web-based portal application to query, display, and print on maps. In addition, users with GIS technical skills will be able to access the underlying database through an ODBC connection.
TransBASESF.org was built using open source software distributions and, outside of the cost of a web server for hosting the project, requires no upfront costs other than the cost of staff time. This project had one Geographic System Analyst dedicating about 25-50 percent of their time to the project over a year period. The Centers of Disease Control and Prevention (CDC) provided funding for initiating this project through the Health Impact Assessment to Foster Healthy Community Design Grant. Cities interested in copying the project can pull the latest source code from GitHub at no cost. Most of the source datasets currently available in TransBASESF.org are created by other city agencies and posted on SF OpenData, San Francisco's open data portal. Many major cities and counties have similar open data portals that could be utilized for similar initiatives. The primary data requirement of TransBASESF.org is a geospatially enabled transportation network dataset where every street and intersection maintains a unique identifier. All other datasets can then be related to this dataset through ETL scripts or other manual GIS processes.
Issues that may hinder the transferability of TransBASESF.org to other cities include the lack of: dedicated technical resources such as GIS staff to implement the project; specific datasets currently produced by SF agencies (estimated traffic volume counts, signal locations, etc.); dedicated funding mechanisms for ongoing costs associated with maintaining a web/database server; and coordination between departments to institutionalize and advance the systems development. Despite these potential challenges, the authors believe this system can be replicated by jurisdictions willing to innovate, dedicate relatively modest resources in the transportation field, and overcome the learning curve of understanding geospatially enabled enterprise level database systems.
Numerous trainings have been conducted since the TransBASESF.org was launched with both city staff and civic advocacy groups. The goal of these trainings were to enhance their capacity for utilizing the system to access data and create cartographic outputs. In addition, SFDPH followed up with all potential users of TransBASESF.org regarding their experience with the system and next steps for improvements. A Survey Monkey was sent out in late 2015 to ascertain how users interact with the system and what improvements they would like to see prioritized in future updates.
Survey respondents (N=15) unanimously understood TransBASESF.org and over 90% of survey respondents believed TransBASESF.org was useful and relevant to the work they do. Nearly half of respondents neither agree nor disagree that they feel comfortable accessing and using data from TransBASESF.org, and nearly 30% do not feel comfortable after the training. Survey respondents also wanted more hands-on training and would like TransBASESF.org to be more user friendly especially in terms of querying.
Based on this evaluation, SFDPH is continuing to expand the visualization and analytical tools in TransBASESF.org. Currently SFDPH is developing a corridor profiler that will allow users to input sections of streets and return collision summary statistics including trends and victim demographics, a commonly requested dataset. SFDPH is also developing dashboard-like tools that easily convey information to the end user, helping to meet the needs lay users interested in transportation safety but lacking the technical background or resources to do in-depth GIS analysis. This tool will keep track of key metrics the city is required to report on for Vision Zero.
SFDPH is also developing a suite of geospatial tools that can be used in ArcGIS to do basic querying of TransBASESF.org and make analysis easier. These tools could also provide standardized methods for accessing and analyzing data so departments are aligned in data reporting. SFDPH is beginning work with the Controller's Office and the Police Department to determine ways to automate the creation of maps showing citations, injuries, and violations to help law enforcement better target problem areas in each police district. This would support the second level of analyst and city staff users identified by the TAC and CAC.
Finally, SFDPH would like to create more customized data entry tools to collect additional information. SFDPH is currently working with SFMTA to develop ways to make speed survey data collection easier. Currently speed data collected by both the SFMTA and consultants does not have a standardized form making input and analysis of the data difficult. It is envisioned that TransBASESF.org could serve as a tool for direct input of speed data collection sheets, making collection and access of speed data timelier for evaluation and analysis. A broader objective of TransBASESF.org is to encourage standardization of data collection processes that will facilitate the uptake and distribution of data collected by city government though the system.
As TransBASESF.org becomes more institutionalized in transportation planning, traffic engineering, and civic advocacy efforts currently taking place in San Francisco, its long term sustainability improves. Each additional user of the database provides added justification for maintaining and enhancing the system. In addition, the more users engaged with the system improves data accuracy and data discovery. TransBASESF.org has been described by city staff to the media as San Francisco's secret weapon to keep pedestrians safe.â€ Staff from San Francisco's Municipal Transportation Department, Police Department, Office of the Controller, Planning Department, Department of Public Works, and County Transit Authority have all used TransBASESF.org in their work on transportation safety projects. In addition, groups outside the city like ZenDrive, Waze, and StreetsBlog have all utilized data from TransBASESF.org in various ways.
Challenges in developing a system like TransBASESF.org include the need for dedicated staff time and resources for keeping the database up-to-date and relevant. Given the large number of datasets, significant time and effort are required to follow up with city GIS leads to maintain current datasets. SFDPH is working to move this manual follow up into ETL scripts and automate data collection from internal agency enterprise GIS databases. SFDPH is also working with other agencies to avoid duplicating efforts, and institutionalize TransBASESF.org through work order agreements and other sources of continued financing.
Another disadvantage of maintaining an open source architecture is the time needed to learn and develop on a software platform that, although most software packages have a robust and active developer community willing to provide help through forums and listservs, often lack the same technical support provided by mainstream proprietary GIS software vendors. Developing TransBASESF.org from scratch and in-house at SFDPH required a steep learning curve and a paradigm shift in how the city creates and operates flexible open source cloud-based web services. Although IT staff are ultimately supportive, it require staff time to reach out, educate, and get permission to implement the project.
Colleague in my LHD|E-Mail from NACCHO