assignment01/db_structure.sql at main · jrigsbyr5/assignment01 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Enable PostGIS
create extension if not exists postgis;

-- Create schema
create schema if not exists indego;

-- Trips tables
drop table if exists indego.trips_2021_q3;
create table indego.trips_2021_q3 (
  trip_id text,
  duration integer,
  start_time timestamp,
  end_time timestamp,
  start_station text,
  start_lat double precision,
  start_lon double precision,
  end_station text,
  end_lat double precision,
  end_lon double precision,
  bike_id text,
  plan_duration integer,
  trip_route_category text,
  passholder_type text,
  bike_type text
);

drop table if exists indego.trips_2022_q3;
create table indego.trips_2022_q3 (
  trip_id text,
  duration integer,
  start_time timestamp,
  end_time timestamp,
  start_station text,
  start_lat double precision,
  start_lon double precision,
  end_station text,
  end_lat double precision,
  end_lon double precision,
  bike_id text,
  plan_duration integer,
  trip_route_category text,
  passholder_type text,
  bike_type text
);

-- Stations table
drop table if exists indego.station_statuses;
create table indego.station_statuses (
  id integer,
  name text,
  geog geography(point, 4326)
);