next up previous contents index
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 modelG.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 $\rightarrow$Access the Archive $\rightarrow$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].

  1. Find all ISO observations of NGC 7582:
    
      select observations.obsno 
        from observations 
        where observations.target = 'NGC 7582'
    

  2. Find all SWS02 observations of HR6705 (wildcarding is important for name searches):
    
      select observations.obsno 
        from observations 
        where observations.target like '%HR%6705%'
      

  3. Find all SWS01 observations at 0 $<$ RA $<$ 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.
    

  4. 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
    

  5. 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
    

  6. 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
    

  7. 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.
    

  8. 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.
    

  9. 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
    

  10. 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)
    

  11. 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'
    

  12. 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
    

  13. Find all PHT40 observations from RGENZEL since revolution 600:

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

  14. Find all CAM observations which changed the gain during one observation:
    
      select obsno 
        from cam_cstat 
        group by obsno having count(distinct cstagain) > 1
    

  15. 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.
    

  16. 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
    

  17. 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
    

  18. 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
    

  19. 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
    

  20. Find all LWS observations containing more than 20 scans (LSAN file):
    
      select distinct obsno from lsan where (lsan.scanb + lsan.scanf) > 20
    

  21. 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)
    

  22. 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
    

  23. 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
    

  24. 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
    

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

  26. 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
    

  27. 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.
    

  28. 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.
    

  29. 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 up previous contents index
Next: H. Acronyms Up: gen_hb Previous: F. Quaternions
ISO Handbook Volume I (GEN), Version 2.0, SAI/2000-035/Dc