Creating the Working Set

Buy or Subscribe

You can access this course in just a minute, and support my efforts to rid the world of crappy online courses!

Buy Standalone  Subscribe

It might be good to see if the description can tell us something about the closest approach for each flyby. I could use a GUI, but pushing the data out to HTML is more fun:

psql cassini -H -c "select * from enceladus.plans where title ~* 'flyby'"  > flybys.html && open flybys.html

Flybys, as far as we're concerned, are the set of dates when Cassini flew closest to Enceladus. We can, once again, use distinct on our timestamp field, this time casting it as a date first. We're also meeting a new SQL friend - order by! This will sort the data for us in ascending fashion. If we append desc on the end of the statement, it will sort in descending fashion:

select distinct start::date 
from enceladus.plans 
where title ~* 'flyby' 
order by start::date;

Our results from the mission plan table proved useless :( and this happens sometimes. We ended up scraping a website, which is OK if that website is nasa.gov and we can hack the querystring. For this, inserting the raw values by hand will suffice and for that we meet another new SQL friend: insert values:

drop schema if exists enceladus cascade;
create schema enceladus;
set search_path='enceladus';

create table flybys(
  id serial not null primary key,
  name text not null,
  date date
);
insert into flybys(name, date)
values ('E-0', '2005-02-17'),
('E-1', '2005-03-09'),('E-2', '2005-07-14'),
('E-3', '2008-03-12'),('E-4', '2008-08-11'),
('E-5', '2008-10-09'),('E-6', '2008-10-31'),
('E-7', '2009-11-02'),('E-8', '2009-11-21'),
('E-9', '2010-04-28'),('E-10', '2010-05-18'),
('E-11', '2010-08-13'),('E-12', '2010-11-30'),
('E-13', '2010-12-21'),('E-14', '2011-10-01'),
('E-15', '2011-10-19'),('E-16', '2011-11-06'),
('E-17', '2012-03-27'),('E-18', '2012-04-14'),
('E-19', '2012-05-02'),('E-20', '2015-10-14'),
('E-21', '2015-10-28'),('E-22', '2015-12-19');