Design of Geospatial Database for Analysis of Road Traffic Accidents in Aichi Prefecture

Transcript

1. SCIS&ISIS2022 Design of Geospatial Database for Analysis of Road Traffic Accidents in Aichi Prefecture Keisuke ANDO1 Kazunori IWATA2 Nobuhiro ITO1 Naoto MUKAI3 Kazuhiko SHIMIZU1 Takeshi UCHITANE1 Yong JIANG2 1Aichi Institute of Technology 2Aichi University 3Sugiyama Jogakuen University
2. Traffic Safety Studies Data analysis to reduce the number of traffic accidents 2 Knowledge to prevent occurrence of accidents Analyze various data
3. Data in Traffic Safety Studies Not easy to combine data in difference formats 3 Traffic Accident Data Other data Database for analyzing traffic accidents How can we organize the data as a bundle of datasets?
4. Objective of This Study Designed a general-purpose database for analyzing traffic accidents 4 Perform multifaceted analysis in a variety of studies Traffic Accident Database
5. Database Construction in Previous Studies Individual database construction for each study 5 statistical models1,2 road geometry3,4 spatiotemporal data5 Focusing on A new analysis requires collecting the necessary data building a database from scratch 1Watanabe and Nakamura (2015) 2Hyodo et al. (2017) 3Shiomi et al. (2016) 4Mukai et al. (2020) 5Kato et al. (2020)
6. Key Point in the Proposal Database commonly available for various studies 6 Traffic Accident Database Various studies Researchers can use the database to replicate analysis of previous studies initiate new analysis
7. Benefits for Database Users What the traffic accident database provides to users 7 ⚫ Data model to represent accident conditions ⚫ Extensibility to add items and link data ⚫ Tutorial for analysis using the database Organize the bundle of raw datasets and provide three points are not arranged neatly and in order Raw Data
8. Data Model Represent accident situations by geospatial objects 8 time 14:58 weather clear collision_ type rear-end collision ...etc. ...etc. Attributes Data Spatial Data (Point) (35.19…, 137.12…) lat lon
9. Extensibility Users can add any data and relate it to traffic accidents 9 Check overlap with spatial function Add geospatial data City area data Party data Add non-geospatial data Define which accident the party is involved in
10. Tutorial Scenario Create a Heat Map of Traffic Accidents in Aichi Prefecture 10 Accident Points Grid Squares Administration Areas Spatially combining/aggregating
11. Sample Database Database collecting data on traffic accidents in Aichi Prefecture 11 Raw Data Sample Database Chart Register Data Analyze Data R Script to build the database R script to analyze data (Tutorial) ⚫ Traffic accident data ⚫ Spot traffic volume information ⚫ Digital national land information ⚫ National population census
12. Analysis System Provide a Docker-based analysis system with a sample database 12 Users can quickly start the analysis on any OS Client Local Server Local directory PostgreSQL + PostGIS RStudio Server Docker Engine Query Data Mount Web browser Command Results of Analysis Container Container
13. Non-geospatial data must be associated with the existing table Evaluation of Database Schema Satisfied the requirements of the data model and extensibility 13 Data model Extensibility Users can analyze accidents situations spatiotemporally Users can add items and link any data to the database
14. 19.79 seconds Evaluation of Database Performance Traffic accident database has sufficient performance for operation 14 Visualization 1.948 hours 217 million records System requirements Operating System Linux (Docker) System Memory 8GB (Max usage 7.609 GB) Disc Depending on the amount of data to register into the database Processor AMD Ryzen 5 3600 6-Core (Use 2-Core only; Max usage rate 77.8%) Build the database
15. Conclusion Designed of a database for analyzing traffic accident data 15 Traffic Accident Database ⚫ Functionality to obtain data representing the accident situation by specifying the time and space ⚫ Extensibility to add any data or items ⚫ Sufficient operational performance https://github.com/NONONOexe/rtadb Published on We will make the database better with researchers in the future