Project

General

Profile

dbquery-part1.sql

Aaron Roodman, 05/01/2013 03:51 PM

 
1
-- nohup psql  -A -F","  --pset footer=off -f dbquery-part1.sql > db-part1.csv &  
2

    
3
-- after 20130130 hexapod_lut has the expid
4

    
5
SELECT e.id AS expid, e.date,extract(day from e.date) AS day, extract(month from e.date) AS month, extract(year from e.date) AS year,d.time_recorded,e.filter,replace(replace(translate(e.filter, 'ugrizY', '012345'),'None','-1'),'block','-2') AS ifilter,e.exptime,e.ra,e.declination AS dec,e.telra, e.teldec,e.propid,e.flavor, COALESCE(NULLIF(replace(e.object,',',' '),''),'noobject') AS object, COALESCE(NULLIF(replace(e.program,',',' '),''),'noprogram') AS program,e.utc_beg,e.focus[1] AS hexdx, e.focus[2] AS hexdy, e.focus[3] AS hexdz, e.focus[4] AS hextx, e.focus[5] AS hexty,
6
d.dodx,d.dody,d.dodz,d.doxt,d.doyt,d.dodxErr,d.dodyErr,d.dodzErr,d.doxtErr,d.doytErr,d.zdelta,d.zthetax,d.zthetay,d.zdeltaErr,d.zthetaxErr,d.zthetayErr,d.zmeandeltaBefore,d.zrmsdeltaBefore,d.zmeandeltaAfter,d.zrmsdeltaAfter,d.z5delta,d.z5thetax,d.z5thetay,d.z5deltaErr,d.z5thetaxErr,d.z5thetayErr,d.z5meandeltaBefore,d.z5rmsdeltaBefore,d.z5meandeltaAfter,d.z5rmsdeltaAfter,d.z6delta,d.z6thetax,d.z6thetay,d.z6deltaErr,d.z6thetaxErr,d.z6thetayErr,d.z6meandeltaBefore,d.z6rmsdeltaBefore,d.z6meandeltaAfter,d.z6rmsdeltaAfter,d.z7delta,d.z7thetax,d.z7thetay,d.z7deltaErr,d.z7thetaxErr,d.z7thetayErr,d.z7meandeltaBefore,d.z7rmsdeltaBefore,d.z7meandeltaAfter,d.z7rmsdeltaAfter,d.z8delta,d.z8thetax,d.z8thetay,d.z8deltaErr,d.z8thetaxErr,d.z8thetayErr,d.z8meandeltaBefore,d.z8rmsdeltaBefore,d.z8meandeltaAfter,d.z8rmsdeltaAfter,d.ndonuts_used,
7
ds.sensors_with_stars,ds.av_proc_time,ds.n_donuts,ds.sudz,
8
g.meanx2,g.meany2,g.meanxy,g.seeing,g.transparency,g.fluxvar,g.maxx,g.maxy,g.meanx,g.meany,g.jumpx,g.jumpy,g.autocorx,g.autocory,
9
hl.az,hl.el,hl.telescope_lut_dx,hl.telescope_lut_dy,hl.telescope_lut_dz,hl.telescope_lut_xt,hl.telescope_lut_yt,hl.filter_lut_dz,hl.trim_dx,hl.trim_dy,hl.trim_dz,hl.trim_xt,hl.trim_yt,hl.tweak_dx,hl.tweak_dy,hl.tweak_dz,hl.tweak_xt,hl.tweak_yt,hl.previous_filter,hl.filter AS current_filter,hl.tweakflag,hl.hexapod_x,hl.hexapod_y,hl.hexapod_z,hl.hexapod_tip AS hexapod_xt,hl.hexapod_tilt AS hexapod_yt,
10
bc.dx AS bcamdx, bc.dy AS bcamdy, bc.ax AS bcamxt, bc.ay AS bcamyt,
11
en.utw_temp,en.uts_temp,en.ute_temp,en.utn_temp,en.inside_humidity,en.outside_humidity,en.outside_temp,en.outside_pressure,en.dimm_seeing,en.chimney_temp,en.domefloor_temp,en.lowdome_temp,en.highdome_temp,en.wind_dir,en.wind_speed,en.pme_temp,en.pmw_temp,en.pms_temp,en.pmn_temp,en.pmas_temp,
12
ih.fwhm[1],ih.r50[1],ih.ellipticity[1],ih.whisker[1],ih.e1[1],ih.e2[1],ih.w1[1],ih.w2[1],ih.nstars[1],ih.angle[1],ih.covxx[1],ih.covxy[1],ih.covyy[1],ih.whisker_rms[1]
13
FROM exposure.exposure AS e 
14
LEFT OUTER JOIN donut_ana AS d ON e.id = d.expid 
15
LEFT OUTER JOIN donut_summary AS ds ON e.id = ds.expid 
16
LEFT OUTER JOIN guider_summary AS g ON e.id = g.expid 
17
LEFT OUTER JOIN hexapod_lut AS hl ON e.id = hl.expid 
18
LEFT OUTER JOIN bcam_hexapod AS bc ON e.id = bc.expid
19
LEFT OUTER JOIN image_health_fp AS ih ON e.id = ih.exposure_id 
20
LEFT OUTER JOIN environmental_data AS en ON en.time_recorded BETWEEN e.date - ((2.0)::VARCHAR || 'seconds')::INTERVAL AND e.date
21
WHERE (e.date BETWEEN '2012-09-14 00:00:00' AND '2013-12-31 10:00:00') 
22
AND e.exptime > 0 AND e.flavor = 'object'
23
ORDER BY e.id;
24

    
25

    
26