● videoFind the Flybys
Creating the Working Set
Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.
SECTION
Find the Flybys
NEXT UP
Summary of Flyby Work
COURSE
PostgreSQL Fundamentals
28 lessons
About this lesson
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 <span class="hljs-string">"select * from enceladus.plans where title ~* 'flyby'"</span> > 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:
<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> <span class="hljs-keyword">start</span>::<span class="hljs-type">date</span>
<span class="hljs-keyword">from</span> enceladus.plans
<span class="hljs-keyword">where</span> title <span class="hljs-operator">~</span><span class="hljs-operator">*</span> <span class="hljs-string">'flyby'</span>
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">start</span>::<span class="hljs-type">date</span>;
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:
<span class="hljs-keyword">drop</span> schema if <span class="hljs-keyword">exists</span> enceladus cascade;
<span class="hljs-keyword">create</span> schema enceladus;
<span class="hljs-keyword">set</span> search_path<span class="hljs-operator">=</span><span class="hljs-string">'enceladus'</span>;
<span class="hljs-keyword">create table</span> flybys(
id serial <span class="hljs-keyword">not null</span> <span class="hljs-keyword">primary key</span>,
name text <span class="hljs-keyword">not null</span>,
<span class="hljs-type">date</span> <span class="hljs-type">date</span>
);
<span class="hljs-keyword">insert into</span> flybys(name, <span class="hljs-type">date</span>)
<span class="hljs-keyword">values</span> (<span class="hljs-string">'E-0'</span>, <span class="hljs-string">'2005-02-17'</span>),
(<span class="hljs-string">'E-1'</span>, <span class="hljs-string">'2005-03-09'</span>),(<span class="hljs-string">'E-2'</span>, <span class="hljs-string">'2005-07-14'</span>),
(<span class="hljs-string">'E-3'</span>, <span class="hljs-string">'2008-03-12'</span>),(<span class="hljs-string">'E-4'</span>, <span class="hljs-string">'2008-08-11'</span>),
(<span class="hljs-string">'E-5'</span>, <span class="hljs-string">'2008-10-09'</span>),(<span class="hljs-string">'E-6'</span>, <span class="hljs-string">'2008-10-31'</span>),
(<span class="hljs-string">'E-7'</span>, <span class="hljs-string">'2009-11-02'</span>),(<span class="hljs-string">'E-8'</span>, <span class="hljs-string">'2009-11-21'</span>),
(<span class="hljs-string">'E-9'</span>, <span class="hljs-string">'2010-04-28'</span>),(<span class="hljs-string">'E-10'</span>, <span class="hljs-string">'2010-05-18'</span>),
(<span class="hljs-string">'E-11'</span>, <span class="hljs-string">'2010-08-13'</span>),(<span class="hljs-string">'E-12'</span>, <span class="hljs-string">'2010-11-30'</span>),
(<span class="hljs-string">'E-13'</span>, <span class="hljs-string">'2010-12-21'</span>),(<span class="hljs-string">'E-14'</span>, <span class="hljs-string">'2011-10-01'</span>),
(<span class="hljs-string">'E-15'</span>, <span class="hljs-string">'2011-10-19'</span>),(<span class="hljs-string">'E-16'</span>, <span class="hljs-string">'2011-11-06'</span>),
(<span class="hljs-string">'E-17'</span>, <span class="hljs-string">'2012-03-27'</span>),(<span class="hljs-string">'E-18'</span>, <span class="hljs-string">'2012-04-14'</span>),
(<span class="hljs-string">'E-19'</span>, <span class="hljs-string">'2012-05-02'</span>),(<span class="hljs-string">'E-20'</span>, <span class="hljs-string">'2015-10-14'</span>),
(<span class="hljs-string">'E-21'</span>, <span class="hljs-string">'2015-10-28'</span>),(<span class="hljs-string">'E-22'</span>, <span class="hljs-string">'2015-12-19'</span>);
Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.