Motion database

The Motion Database is used to keep track of my rolling stock including cars and bicycles. It's a demo database for developing concepts in PostgreSQL and Wavemaker.

Create the database and add extensions

createdb -U webadmin motion
psql -U webadmin motion

CREATE EXTENSION postgis;

# Add support for Large Objects
CREATE EXTENSION lo;

# Show installed extensions
SELECT * FROM pg_available_extensions
WHERE comment LIKE '%string%' OR installed_version IS NOT NULL ORDER BY name;

# Get more info on an extension
\dx+ postgis
\dx+ lo

Tables

First the look up tables

The vehicle table is used to describe a vehicle

DROP TABLE IF EXISTS vehicle;
CREATE TABLE vehicle (
 vehicle_id SERIAL NOT NULL, 
 name VARCHAR(40) NOT NULL,
 description TEXT,
 serial_number VARCHAR(40),
 PRIMARY KEY(vehicle_id)
);
INSERT INTO vehicle (name,description) VALUES ('Davidson', '1995 Davidson Discovery');
INSERT INTO vehicle (name,description) VALUES ('Fuji', '1998 Fuji Touring Series');
INSERT INTO vehicle (name,description) VALUES ('Blue', '2013 Longbikes Slipstream');
INSERT INTO vehicle (name,description) VALUES ('Swallow', '1990 Specialized Stumpjumper');
INSERT INTO vehicle (name,description) VALUES ('Alba', '2010 Toyota Yaris');
INSERT INTO vehicle (name,description) VALUES ('MG', '1970 MG Midget');
INSERT INTO vehicle (name,description) VALUES ('Honda', 'Honda Civic');
SELECT * FROM vehicle;

The rider table describes people who can be bicyclists or car drivers/passengers.

DROP TABLE IF EXISTS rider;
CREATE TABLE rider (
 rider_id SERIAL NOT NULL,
 name VARCHAR(10) NOT NULL,
 PRIMARY KEY (rider_id)
);
INSERT INTO rider (name) VALUES ('Brian');
INSERT INTO rider (name) VALUES ('Julie');
INSERT INTO rider (name) VALUES ('Rob');
SELECT * FROM rider;

Trip tables

One trip can have more than one bicyclist and that means more than one rider, or one vehicle can carry two riders on one trip, so we need a junction table to link together riders, vehicles, and trips. I don't care who drives if more than one person is in a car together. (Maybe they take turns.)

DROP TABLE IF EXISTS trip;
CREATE TABLE trip (
 trip_id SERIAL NOT NULL,
 date DATE,
 mileage NUMERIC,
 notes TEXT,
 gpxlog TEXT,
 PRIMARY KEY(trip_id)
);
INSERT INTO trip (date,mileage,notes) VALUES ('3/14/2013',15,'Airport loop');
INSERT INTO trip (date,mileage,notes) VALUES ('3/15/2013',22,'Airport loop + Walnut Ave');
INSERT INTO trip (date,mileage,notes) VALUES ('3/16/2013',14,'Bald Hill loop');
INSERT INTO trip (date,mileage,notes) VALUES ('3/18/2013',12,'HP loop');
INSERT INTO trip (date,mileage,notes) VALUES ('3/20/2013',93,'Home - Flightstats');
SELECT * FROM trip;

DROP TABLE IF EXISTS j_trip;
CREATE TABLE j_trip (
 j_trip_id SERIAL NOT NULL,
 trip_id INT, FOREIGN KEY (trip_id) REFERENCES trip(trip_id),
 rider_id INT, FOREIGN KEY (rider_id) REFERENCES rider(rider_id),
 vehicle_id INT, FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id),
 PRIMARY KEY(j_trip_id)
);
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (1,1,1); --1 brian davidson
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (2,1,1); --2 "     "
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (2,2,4); --2 julie swallow
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (3,1,2); --3 brian fuji
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (4,1,2); --4 brian fuji
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (4,2,4); --4 julie swallow
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,1,7); --5 brian honda
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,2,7); --5 julie honda
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,3,7); --5 rob honda
SELECT * FROM j_trip;

SELECT t.date,t.notes,r.name,v.name
FROM trip AS t,rider AS r,vehicle AS v, j_trip AS j
WHERE (j.trip_id=t.trip_id) AND (j.rider_id=r.rider_id) AND (j.vehicle_id=v.vehicle_id);

-- WRONG - shows constraint
INSERT INTO j_trip (trip_id,rider_id,vehicle_id) VALUES (5,3,8); --5 rob ???

Maintenance records

DROP TABLE IF EXISTS maintenance;
CREATE TABLE maintenance (
 maint_id SERIAL NOT NULL,
 date DATE,
 description TEXT,
 notes TEXT,
 vehicle_id INT, FOREIGN KEY(vehicle_id) REFERENCES vehicle(vehicle_id),
 PRIMARY KEY(maint_id)
);
INSERT INTO maintenance (date, description, vehicle_id) VALUES ('2/1/2013', 'Oiled chain', 1);
INSERT INTO maintenance (date, description, notes, vehicle_id) VALUES ('3/17/2013', 'New front wheel', 'Phil Wood hub', 4);
SELECT date,name,maintenance.description FROM maintenance,vehicle WHERE maintenance.vehicle_id=vehicle.vehicle_id;

Photo tables

Using lo (Large Object) extension to hold photo in database, I like to keep everything in one place so I don't lose them. (In the database instead of keeping them on the filesystem)

I could do photo/image processing in the database if I installed the PostPIC extension, see http://drotiro.github.com/postpic/ Possibly I could use it to generate the thumbnails?

DROP TABLE IF EXISTS photo;
CREATE TABLE photo (
 photo_id SERIAL NOT NULL,  PRIMARY KEY(photo_id),
 time TIMESTAMP,
 thumbnail BYTEA,
 image LO,
 lat NUMERIC(8,6), lon NUMERIC(9,6),
 description TEXT
);
CREATE TRIGGER t_image BEFORE UPDATE OR DELETE ON photo
   FOR EACH ROW EXECUTE PROCEDURE lo_manage(image);
-- Lat Lon are from iTouchMap.com
INSERT INTO photo (description) VALUES ('Davidson photo');
INSERT INTO photo (description) VALUES ('Fuji photo');
INSERT INTO photo (description,lat,lon) VALUES ('Bald Hill',44.56739,-123.332099);
INSERT INTO photo (description) VALUES ('New wheel photo');
INSERT INTO photo (description,lat,lon) VALUES ('Bellfountain Rd', 44.495692, -123.337787);
INSERT INTO photo (description,lat,lon) VALUES ('MG in Gold Beach',42.564287, -124.383210);
SELECT * FROM photo;

Photos are good for everything -- people, rides, vehicles, maintenance... so I need a junction table to accommodate this

DROP TABLE IF EXISTS j_photo;
CREATE TABLE j_photo (
 j_photo_id SERIAL NOT NULL, PRIMARY KEY(j_photo_id),
 photo_id INT NOT NULL, FOREIGN KEY (photo_id) REFERENCES photo(photo_id),
 trip_id INT, FOREIGN KEY (trip_id) REFERENCES trip(trip_id),
 rider_id INT, FOREIGN KEY (rider_id) REFERENCES rider(rider_id),
 vehicle_id INT, FOREIGN KEY(vehicle_id) REFERENCES vehicle(vehicle_id),
 maint_id INT, FOREIGN KEY (maint_id) REFERENCES maintenance(maint_id)
);
INSERT INTO j_photo (photo_id,vehicle_id) VALUES (1,1);
INSERT INTO j_photo (photo_id,vehicle_id) VALUES (2,2);
INSERT INTO j_photo (photo_id,trip_id) VALUES (3,3);
INSERT INTO j_photo (photo_id,maint_id) VALUES (4,2);
INSERT INTO j_photo (photo_id,trip_id) VALUES (5,3);
INSERT INTO j_photo (photo_id,vehicle_id) VALUES (6,6);

SELECT p.description,v.name
FROM j_photo AS j, photo AS p, vehicle AS v
WHERE ((j.photo_id=p.photo_id) AND (j.vehicle_id=v.vehicle_id));

SELECT p.description,m.notes
FROM j_photo AS j, photo AS p, maintenance AS m
WHERE ((j.photo_id=p.photo_id) AND (j.maint_id=m.maint_id));

SELECT p.description,t.notes
FROM j_photo AS j, photo AS p, trip AS t
WHERE ((j.photo_id=p.photo_id) AND (j.trip_id=t.trip_id));