SUSTAINABLE TRANSPORTATION IN SALT LAKE CITY
GEOG 6150 - Spatial Data Design
The endless expansion and widening of major highways and roads around Salt Lake City is expensive and unsustainable. I created a database that contains many of the eco-friendlier transportation options in, to, and from Salt Lake City that can take cars off of the road. I think that someone who is unaware of transportation options outside of driving would be the most inclined to use this database, and I want people to be able to learn where the points-of-access for these different transportation options are located. I think a lack of knowledge and comfortability is the biggest barrier to people using sustainable transportation options, and I wanted this database to be a tool to help break down those barriers.
Integrity Constraints
The database itself needed to be able to show which station/stops belong to which lines/routes and vice versa. Having the routes connected with the stops, and the stops connected to each other would provide the user with enough information to figure out the fastest route through public transportation. The relationships between each of the transportation types and their stops address this requirement. I also wanted the Green Bike hubs and the points of interest to be accessed by the bike routes, as the bike routes would be used by any Green Bike user and potentially head toward one of the points of interest. The connections between these provide the user with the closest bike routes around both the Hubs and the points of interest.
Entity Relationship (ER) Diagram
The ER Diagram provides the conceptual model for the database. In this case, the ER diagram consists of 11 entities and contains information about the cardinality between entities, entity types, associated primary keys, and all of their assigned attributes.
Figure 1: Entity Relationship (ER) Diagram
Relational Diagram
The Relational Diagram provides the database logic and more information as to the functionality of the primary keys, foreign keys, attributes, and field types.
Figure 2: Relational Diagram
Final Physical Database Creation Process
Creating the physical model first required creating the database in Arc Pro. Once the was done, I uploaded all eleven feature classes/entities into it, which forms the basis of the functionality. Afterwards I started to create the relationship classes, beginning with the relationship between Bus Routes and Bus Stops. In Salt Lake City, there are just over 50 bus routes and over 1100 stops. Due to time constraints, I only related 10 bus routes with their stops. I did this through selecting the bus route, and then selecting all of the bus stops on that route. From there, I opened the attributes and related all of the selected stops to the selected route. Doing this allows the user to select one of these routes which in turn highlights all of the corresponding bus stops as well. The opposite also applies, with the user being able to select one of the related stops and the corresponding route being highlighted. This strategy applied to all of the other relationships as well, just selecting the points or lines from each entity and selecting the points or lines that are either connected to, in proximity to, or along the original points or lines. At the end, I simply selected all of the points or lines for each layer and related them to the city. This allows the user to see that all of the points from every layer belong to Salt Lake City.
Skills Learned / Used
Spatial Data & Algorithms
GIS Workflow
Data Models & Structures
Database Design