Next: H. Acronyms Up: gen_hb Previous: F. Quaternions

G. IDA SQL-Queries: Worked Examples

Here are some worked examples of IDA SQL-queries based on the current data model^G.1.

The SQL-applet which allows expert users to perform these queries is available at the IDC web site under:

https://www.cosmos.esa.int/web/iso Access the Archive SQL Queries

To fully understand these examples it is necessary to know the definitions and the structures of the ISO Data Archive Physical Data Model, [47].

Find all ISO observations of NGC 7582:


  select observations.obsno 
    from observations 
    where observations.target = 'NGC 7582'

Find all SWS02 observations of HR6705 (wildcarding is important for name searches):


  select observations.obsno 
    from observations 
    where observations.target like '%HR%6705%'

Find all SWS01 observations at 0

6h and DEC

30 $^{\circ}$ :


  select distinct observations.obsno
    from planning_info p, observations 
    where observations.aotname = 'S01'
    and p.orgra > 0 
    and p.orgra < 6
    and p.orgdec > 30
    and p.obsno = observations.obsno

  NOTE: orgra is in hours and orgdec in degrees. We assume that the
        observations made, apertures used, are points. The above query
        can be performed, better, via the browse/request applet interface.
        This interface will make use of the `positions' table, the actual
        aperture sizes, and the downlink (IRPH) coordinates.

Find all SWS01 observations with exposure time

1.5hours:


  select observations.obsno
    from observations 
    where observations.aotname = 'S01'
    and observations.utc_end - observations.utc_start > 5400

Find all SWS01 observations with 40 min

exposure time

1hour:


  select observations.obsno
    from observations 
    where observations.aotname = 'S01'
    and (observations.utc_end - observations.utc_start) > 2400
    and (observations.utc_end - observations.utc_start) < 3600

Find all SWS06 observations of 'HR6705 GAMMA-DRA' from SWS_CAL with exposure time between 30 and 40 minutes:


  select observations.obsno
    from observations 
    where observations.aotname = 'S06'
    and observations.target = 'HR6705 GAMMA-DRA'
    and obsid = 'SWS_CAL'
    and (observations.utc_end - observations.utc_start) > 1800
    and (observations.utc_end - observations.utc_start) < 2400

Find all SWS data from revolution 79:


  select observations.obsno 
    from observations
    where observations.instid = 'S'
    and observations.revno >= 079
    

  NOTE: assumed that `data' means all FITS product files (filename and
        version). The above query can be performed, better, via the
        browse/request applet interface.

Find all SWS data with start time between 04 Feb 1996 12:00:00 (date1) and 04 Feb 1996 18:00:00 (date2):


  select observations.obsno 
    from observations
    where observations.instid = 'S'
    and observations.utc_start > date1
    and observations.utc_start < date2

  NOTE: date1 and date2 should be converted to UTC in seconds since 1989.

Find all SWS02 observations which include a line at rest wavelength between 17.91 and 17.93 $\mu$ m:


  select distinct observations.obsno
    from observations, wavelengths 
    where observations.aotname = 'S02'
    and wavelengths.lower > 17910
    and wavelengths.lower < 17930
    and wavelengths.obsno = observations.obsno

Find all SWS02 observations that include both a line in the rest wavelength range 12.8-12.85 and a line in the range 15.5-15.6 $\mu$ m:


  select o1.obsno
    from observations o1, wavelengths w1 
    where o1.aotname = 'S02'
    and (w1.lower > 12800
    and w1.lower < 12850)
    and w1.obsno = o1.obsno
    and exists 
    (select * from observations o2, wavelengths w2
    where o2.aotname = 'S02'
    and (w2.lower > 15500
    and w2.lower < 15600)
    and w2.obsno = o2.obsno
    and o1.obsno = o2.obsno)

Find all SWS observations following a CAM-CVF measurement:


  select obsno, utc_end, aotname into #sws1
    from observations 
    where aotname in ('C01','C03','C04')

  select obsno, fltr_cvf, beam into #sws2
    from cam_measurement
    where fltr_cvf in (12,11)

  select distinct s1.obsno, s1.utc_end into #sws3
    from  #sws1 s1, #sws2 s2
    where s1.aotname= 'C04' or ((s1.aotname = 'C01' or s1.aotname = 'C03')
    and (s2.fltr_cvf = 12 or (s2.fltr_cvf = 11 and (s2.beam=3 or s2.beam=4))))
    and s1.obsno=s2.obsno

  select o.obsno, o.utc_start, s3.utc_end from observations o, #sws3 s3
    where o.instid = 'S' and abs(o.utc_start - s3.utc_end) < 120
    and prodqlty != 'J'

  NOTE: Assumes that 'following' means 'within 120 seconds of'

Find all SWS02 observations which requested S/N in excess of 200 (easy, but only useful if proposers were careful...):


  select distinct observations.obsno 
    from observations,
    sws_measurement s
    where observations.aotname = 'S02' 
    and s.sgnr_req > 200
    and observations.obsno = s.obsno

Find all PHT40 observations from RGENZEL since revolution 600:


  select observations.obsno
    from observations 
    where aotname = 'P40'
    and obsid = 'RGENZEL'
    and revno >= 600

Find all CAM observations which changed the gain during one observation:


  select obsno 
    from cam_cstat 
    group by obsno having count(distinct cstagain) > 1

Find all CAM parallel data

IxPH files where HUGO is the observer of the prime instrument:


  select obsno from observations where obsno-1 in (
    select obsno from observations where cam_par_flag = 'Y' and
      obsid like '%HUGO%')

  NOTE: This gives the observations that match, these must then be
        added to the shopping basket and the type of data requested specified.

Find all LWS02 observations where LVDT $\le$ 1000:


  select distinct o.obsno from observations o, lspd l where
    aotname = 'L02' and lspdglvp <= 1000.0
    and o.obsno = l.obsno

Find all LWS04 observations using detector LW5:


  select distinct o.obsno from observations o, lspd l where
    aotname = 'L04' and l.lspdadet = 512 and o.obsno=l.obsno

Find all LWS observations of the 88 $\mu$ m line on detector SW5 with FPS:


  select distinct l.obsno from wavelengths w, liac l 
    where lower <= 88000 and upper >=  88000 
    and countno=5 and liacwhap=0
    and w.obsno = l.obsno

Find all occurrences of the total number of ramps in an LWS scan being less than x (LSCA file):
```
  select obsno, lscanrmt, lscaitks, lscaitks from lsca where lscanrmt < x
```

Find all LWS observations containing more than 20 scans (LSAN file):


  select distinct obsno from lsan where (lsan.scanb + lsan.scanf) > 20

Find all LWS observations where all scans are done in the forward direction (LSCA file):


  select distinct obsno from lsca where obsno not in
    (select distinct obsno from lsca where lscadir != 0)

Find all occurrences of the last flash in an LWS observation having an LW2 responsivity correction factor above x (LIAC file):
```
  select obsno, liaciks, liacike from liac where liacres7 > x
```
Find all occurrences of the LWS dark current for SW1 being greater than x (LIAC file):
```
  select obsno, liaciks, liacike from liac where liacbk1 > x
```

Find all occurrences of the LWS absolute responsivity correction factor for any detector greater than x when the wheel is in the FPL position:


  select obsno, liaciks, liacike from liac where liacwhap = 2 and 
    liacres1 > x or liacres2 > x or liacres3 > x or liacres4 > x or liacres5 > x
    or liacres6 > x or liacres7 > x or liacres8 > x or liacres9 > x
    or liacres10 > x

Find all occurrences of the LWS responsivity drift slope for SW3 being above x:
```
  select obsno, lgifitks, lgifitke from lgif where lgif2rel3 > x
```

Find all CAM LW3 rasters with 1.5 $^{\prime \prime}$ pixel field of view (pfov), which have a raster dimension

4 $\times$ 4 and at least 25 readouts per position:


  select distinct o.obsno from observations o, cam_cstat c,
    cam_measurement m, raster_map r
    where c.cstafltw=125 and
    m.pfov = 1.5 and
    r.m*r.n > 16 and
    c.cstaread >= 25 and
    o.obsno = c.obsno and
    o.obsno = m.obsno and  select distinct o.obsno from observations o, cam_cstat c,
    cam_measurement m, raster_map r
    where c.cstafltw=125 and
    m.pfov = 1.5 and
    r.m*r.n > 16 and
    c.cstaread >= 25 and
    o.obsno = c.obsno and
    o.obsno = m.obsno and
    o.obsno = r.obsno

  select distinct o.obsno from observations o, cam_cstat c, aph a,
    obs_pointing p
    where o.obsno=p.obsno and p.pointing_id = a.pointing_id and
    o.obsno = c.obsno
    and c.cstafltw=125 and c.cstalnsw=192 and a.scan_dist*a.linedist > 16
    and c.cstaread >= 25
    o.obsno = r.obsno

  select distinct o.obsno from observations o, cam_cstat c, aph a,
    obs_pointing p
    where o.obsno=p.obsno and p.pointing_id = a.pointing_id and
    o.obsno = c.obsno
    and c.cstafltw=125 and c.cstalnsw=192 and a.scan_dist*a.linedist > 16
    and c.cstaread >= 25

Find all CAM raster observations longer than 1hour, which started at the latest 2hours after activation:


  select distinct t1.obsno, (t1.utc_end - t1.utc_start)
    from observations t1, raster_map where
    t1.obsno = raster_map.obsno and
    (t1.instid = 'C') and
    (t1.utc_end - t1.utc_start > 3600) and
    exists (select * from observations t2  where
    (t2.aotname='C60' or t2.aotname='C61') and
    (t1.utc_start - t2.utc_end) < 7200)

  Or, alternatively:

  select distinct t1.obsno, (t1.utc_end - t1.utc_start)
    from observations t1, aph a, obs_pointing p where
    t1.obsno = p.obsno and p.pointing_id = a.pointing_id and
    (a.reqtype = 'R') and (t1.instid = 'C') and (t1.type != 'C') and
    (t1.utc_end - t1.utc_start > 3600) and
    exists (select * from observations t2  where
    (t2.aotname='C60' or t2.aotname='C61') and
    (t1.utc_start - t2.utc_end) < 7200)

  NOTE: The second query is probably better as it uses the aph to
        distinguish a raster observation. This is generally more accurate
        than using the raster_map table as this only contains planned
        uplink values (and so obs which did not go through PGA into the
        Uplink MDB, such as C99, will not be included). Note,
        that this query also explicitly excludes CAM parallel observations
        - these could easily be included if wanted.

Find all CAM parallel data

IxPH files for LWS rasters

2hours observing time with a dimension

3 $\times$ 3:


  select distinct obsno from observations where type='C' and obsno-1 in (
    select o.obsno from observations o, raster_map r where instid='L'
      and r.m*r.n > 9
      and utc_end - utc_start > 7200
      and o.obsno = r.obsno )

  NOTE: Again, this query can also (better) be performed using the aph table.

Find all LWS raster observations longer than 1hour which started at the latest 2hours after activation:


  select o.obsno from observations o, raster_map
    where o.obsno = raster_map.obsno and
    (o.instid = 'L') and
    (o.utc_end - o.utc_start > 3600) and
    exists (select * from observations o2  where
    (o2.aotname='C60' or o2.aotname='C61') and
    (o.utc_start - o2.utc_end) < 7200)

  Or, alternatively:

  select o.obsno from observations o, aph a, obs_pointing p
    where o.obsno = p.obsno and p.pointing_id = a.pointing_id and
    a.reqtype='R' and (o.instid = 'L') and type = 'S' and
    (o.utc_end - o.utc_start > 3600) and
    exists (select * from observations o2  where
    (o2.aotname='C60' or o2.aotname='C61') and
    (o.utc_start - o2.utc_end) < 7200)

  NOTE: This query is restricted to science observations - this could
        easlily be modified to also return LWS parallel observations.

Next: H. Acronyms Up: gen_hb Previous: F. Quaternions

ISO Handbook Volume I (GEN), Version 2.0, SAI/2000-035/Dc