dev-chiefworks
50 lines
1.5 KiB
SQL
50 lines
1.5 KiB
SQL
CREATE TABLE gps_trips (
|
|
id bigserial,
|
|
member_id bigint,
|
|
start_members_tracking_id bigint REFERENCES members_tracking(id),
|
|
last_members_tracking_id bigint REFERENCES members_tracking(id),
|
|
travel_date timestamp,
|
|
travel_date_end timestamp,
|
|
duration real,
|
|
distance real,
|
|
location_start_lat double precision,
|
|
location_start_lng double precision,
|
|
location_start geography,
|
|
location_start_id bigint,
|
|
location_end_lat double precision,
|
|
location_end_lng double precision,
|
|
location_end geography,
|
|
location_end_id bigint,
|
|
avg_speed real,
|
|
PRIMARY KEY(id)
|
|
);
|
|
|
|
CREATE TABLE gps_trips_legs (
|
|
id bigserial,
|
|
member_id bigint,
|
|
start_members_tracking_id bigint REFERENCES members_tracking(id),
|
|
last_members_tracking_id bigint REFERENCES members_tracking(id),
|
|
gps_trip_id bigint REFERENCES gps_trips(id),
|
|
travel_date timestamp,
|
|
travel_date_end timestamp,
|
|
duration real,
|
|
distance real,
|
|
location_start_lat double precision,
|
|
location_start_lng double precision,
|
|
location_start geography,
|
|
location_start_id bigint,
|
|
location_end_lat double precision,
|
|
location_end_lng double precision,
|
|
location_end geography,
|
|
location_end_id bigint,
|
|
avg_speed real,
|
|
transport_mode varchar(30),
|
|
PRIMARY KEY(id)
|
|
);
|
|
|
|
comment on column gps_trips.duration is 'in minutes';
|
|
comment on column gps_trips.distance is 'in kms';
|
|
|
|
comment on column gps_trips_legs.duration is 'in minutes';
|
|
comment on column gps_trips_legs.distance is 'in kms';
|