Under The Hood - Direct Searches

Overview

This page provides a basic introduction to searching the postgres meta-data database directly. This kind of access will be useful to anyone needing to search in ways that the web interface does not allow.

Prerequisites

In order to succeed with direct search, you'll need to have;

• configured your iVec account appropriately. Steps involved are covered in Preparing Your Account. Please check that you've covered all of these steps.
• a basic understanding of unix command line tools
• a basic understanding of SQL. If you've not used SQL before, then you'll just need to be prepared to learn.

The Postgres Client

Wastac Database Basics - Simplified Views

wastac.v_swath

wastac.v_archivefile

Simple Searches

How many swaths are in the database?

wastac=> select count(*) from wastac.v_swath;
 count
--------
 130250
(1 row)

How many swaths from each acquirer?

wastac=> select acquirer_name, count(swath_name) from wastac.v_swath group by acquirer_name;
 acquirer_name | count
---------------+--------
 wastac        |  26500
 nasa          | 103750

What MODIS DACC (NASA) granules exist between two dates?

wastac=> select swath_name
from wastac.v_swath
where
     swath_timestamp_start > timestamp '2009-08-01'
and  swath_timestamp_end   < timestamp '2009-08-31'
and acquirer_name = 'nasa'
order by swath_timestamp_start;

Note to save the results of the above query to a file, you could use the output re-direct function of psql as follows;

wastac=> \o ~/results.txt
wastac=> select swath_name
         from wastac.v_swath
         where
              swath_timestamp_start > timestamp '2009-08-01'
         and  swath_timestamp_end   < timestamp '2009-08-31'
         and  acquirer_name = 'nasa'
         order by swath_timestamp_start;
wastac=> \q
hlynch@wastac-4240% less ~/results.txt

wastac=> select
       date_part('year', swath_timestamp_start) as year, 
       date_part('month', swath_timestamp_start) as month, 
       acquirer_name, 
       count(swath_name) 
from wastac.v_swath 
group by year, month, acquirer_name 
order by year, month, acquirer_name;

Which swaths fall in october of any year?

wastac=> select swath_name 
  from wastac.v_swath 
where 
     acquirer_name = 'nasa' 
and  date_part('month', swath_timestamp_start) = '10' 
order by swath_timestamp_start;

Which swaths cover a specific area?

wastac=> SELECT swath_name, swath_timestamp_start AT TIME ZONE 'Australia/Perth' AS localtime 
    FROM wastac.t_swath_metadata 
    WHERE swath_bounding IS NOT NULL 
      AND swath_bounding && GeomFromText('POLYGON((121.75 -34.0, 122.0 -34.0, 122.0 -33.75, 121.75 -33.75, 121.75 -34.0))',-1) 
    ORDER BY swath_timestamp_start;

How can I display times as my local time rather than UTC?

wastac=> SELECT swath_name, swath_timestamp_start AT TIME ZONE 'Australia/Perth' AS localtime;