datamodel.md

Datamodel prototype

A visual representation of the datamodel can be found in the following link

Relationships between tables are specified by the lines connecting multiple tables. In the diagram its specified the type of matching expected, where 1:1 represents a one to one matching, m:1 many to one, and m:m many to many.

The datamodel is composed of the following tables:

• Demographics
• TransitStation
• TransitRoute
• StationRouteRelation
• RidershipRoute
• RidershipStation
• BikeStation
• BikeRidership
• SurveyUser
• SurveyResponse

Demographics

The Demographics table contains demographic information at the Census Tract level obtained from the American Community Survey, containing information for the following variables: Population, Median Household Income, Means of Transportation to Work, Time of Departure to Go to Work, Travel Time to Work, Vehicles Available and Disability Status. The table is structured in the following way:

Name	Type	Description
census_tract	string	Census tract at which demographic data is aggregated
state	string	State the census tract is part of
county	string	County the census tract is part of
population	integer	Population in census tract
median_income	integer	Median income in census tract
transportation_to_work_total	integer	Total number of people considered for mean of transportation to work question
transportation_to_work_car	integer	Number of people that use cars as mean of transportation to work
transportation_to_work_public	integer	Number of people that use public transportation as mean of transportation to work
work_commute_time_less_15	integer	Number of people that take less than 15' commuting to work
work_commute_time_15_29	integer	Number of people that take between 15 and 30 minutes commuting to work
work_commute_time_30_45	integer	Number of people that take between 30 and 45 minutes commuting to work
work_commute_time_45_60	integer	Number of people that take between 45 and 60 minutes commuting to work
work_commute_time_60_90	integer	Number of people that take between 60 and 90 minutes commuting to work
work_commute_time_over_90	integer	Number of people that take over 90' commuting to work
geographic_representation	Polygon	Geographic representation of census tract

Public Transportation

The public transportation system information from each city is stored in the TransitStation and TransitRoute tables. The TransitStation represent each bus stop and subway station while TransitRoute represents each bus route, subway line or any other route (i.e. rail line). The TransitStation table is structured of the following way:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
city	string	City where the station is located
station_id	string	Station identificator for bus stop or subway station
station_name	string	Station identificator for bus stop or subway station
location	Geos Point	Location of the station/bus stop
mode	integer	Mode of transportation (bus, subway, train)

For this table, as a constraint, there must be uniqueness in the combination of the fields station_id and city

The TransitRoute table is represented by the following table:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
city	string	City where the route is located
route_id	string	Identificator for the route/line
route_name	string	Name of the route/line
geo_representation	MultiLineString	Geographic representation of the route/line
color	string	Color of the station
mode	integer	Mode of transportation (bus, subway, train)

For this table, as a constraint, there must be uniqueness in the combination of the fields route_id and city

The relations between Stations and Routes are stored in the StationRouteRelation table which maps an Station to every route it serves and is represented by:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
station	ForeignKey(TransitStation)	Station/Stop instance
route	ForeignKey(TransitStation)	Route/Line instance

The ridership information for Stations and Routes are stored in the RidershipRoute and RidershipStations tables. The RidershipRoute stores information of daily ridership data for a Route and is represented by:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
route_id	ForeignKey(TransitRoute)	Route/Line instance
date	datetime	Date of ridership
ridership	integer	Number of riders

For this table, as a constraint, there must be uniqueness in the combination of the fields route_id and date

The RidershipStation stores information of daily ridership data for a Station/Stop and is represented by:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
station_id	ForeignKey(TransitStation)	Station/Stop instance
date	datetime	Date of ridership
ridership	integer	Number of riders

For this table, as a constraint, there must be uniqueness in the combination of the fields station_id and date

Bike data

The BikeStations and BikeRidership represent the stations and ridership data for publicly available bikes for rent (CitiBikes,Divvy and BIKETOWN). The BikeStation table is represented in the following way:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
city	string	City where the station is located
station_id	string	ID of the bike docking station
station_name	string	Name of the bike docking station
short_name	string	Alternative id for the station
location	Point	Location of the bike docking station
n_docks	integer	Capacity of the bike station

For this table, as a constraint, there must be uniqueness in the combination of the fields station_id and city

The BikeRidership table is structured in the following way:

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
station_id	ForeignKey(BikeStation)	Bike station for which ridership is reported
date	Datetime object	Date of the reported ridership
n_started	integer	Number of trips started at the station
n_ended	integer	Number of trips ended at the station

For this table, as a constraint, there must be uniqueness in the combination of the fields station_id and date

Survey

For the survey information there are two tables that represent the necessary information SurveyUser and SurveyResponse. SurveyUser stores a user id and basic transit information.

The SurveyUser table is represented by the following structure:

Name	Type	Description
user_id	Primary Key	Identificator generated for each user (session)
city	string	City of residence of the user
frequent_transit	Bool	True if the user answer to use transit frequently, False otherwise
car_owner	Bool	True if the user answer to own a car, False otherwise

The SurveyResponse table represents each response to the survey associated to one trip submitted

Name	Type	Description
id	Primary Key	Identificator autogenerated by Django
user_id	ForeignKey(SurveyUser)	User associated with the response
city	string	City of residence of the user
route	LineString	Geoetric representation of the submitted route
starting_point	Point	Starting point of the submitted route
end_point	Point	Endinging point of the submitted route
trip_frequency	Integer	Frequency on how often a user take the submitted route
trip_tod	Integer	Time of day of when the user takes the submitted route
trip_time	Integer	Time it takes the user to complete the submitted route
mode_of_transportation	Integer	Mode of transportation used to take the submitted route
satisfied	Integer	Date and time of when the answer was submitted
transit_improvement	Integer	Choice of how to improve the submitted route
transit_improvement_open	Integer	Open answer on hot to improve the submitted route
switch_to_transit	Integer	Factor that would make a user switch to transit