Ticket #409 (closed enhancement: fixed)

Opened 11 years ago

Last modified 7 years ago

SDQA database schema

Reported by: jbecla Owned by: jbecla
Priority: normal Milestone:
Component: dbSchema Keywords:
Cc: rlaher Blocked By:
Blocking: Project: LSST
Version Number:
How to repeat:

not applicable


Implement SDQA schema (Russ Laher). Integrate with baseline LSST schema (Jacek)

Change History

comment:1 Changed 11 years ago by jbecla

  • Status changed from new to assigned

comment:2 Changed 11 years ago by jbecla

  • Owner changed from jbecla to Tim Axelrod

Next step: review http://dev.lsstcorp.org/trac/wiki/MetricsForSDQA and decide which of the fields described there go to ccd_Exposure/amp_ Exposure tables, and which to sdqa_Status (Tim)

comment:3 Changed 11 years ago by TimAxelrod

Below, I'd like to suggest some modifications to the SDQA-related columns. But, before going into those, here's my view of where the contents should go.

  • The contents of "Exposure-level Metadata" should go into Raw_FPA_Exposure.
  • The contents of "Amplifier-level Metrics" should go into Science_Amp_Exposure
  • The contents of "CCD-level Metrics" should go into Science_CCD_Exposure.
  • The contents of "SRD Metrics" needs to go into a new table (more probably, a groupd of tables), which I'd suggest be called Survey_Status.

OK, onto the individual tables:

Exposure-level Metadata

  • The items fpa.ccd.offset.x and .y should not be here, but rather contained in a new table, whose name I'll suggest as FPA_Configuration. This should contain a full description of the FPA configuration as a function of time (really some sort of FPA_Version). This description should include not only the geometrical description, but CCD and Raft serial numbers, etc. The versioning needs to be part of some comprehensive scheme for handling this kind of thing, but an example would be a parent table, FPA_History, which would have a start time, an end time, and a FPA_VersionId that identifies a particular FPA_Configuration table.
  • If we're going to have moon data, which is a fine idea, I'd suggest we give ra_moon, decl_moon, and moon_phase. We can leave the calculation of the angle to the moon to a stored function. Similarly, we might as well add ra_sun, dec_sun.

Amplifier-level Metrics

  • These seem fine, but I'd like to see some metrics added that quantify the bias level and structure as determined from the overscan pixels. This is frequently one of the first indications that something is going awry.

CCD-Level Metrics

  • I'm not sure that img.bg.slope.x and .y are useful at the CCD level, since one of the things that will be present at some level is a mismatch of the backgrounds from the various CCD segments. We'd like some metric to quantify this mismatch, but a slope doesn't capture it. Perhaps these slope quantities should be moved to the segment (Amp) level.
  • I don't understand phot.response. A ratio of magnitudes doesn't seem useful. Perhaps a difference of mags is, but I still don't understand the intent.
  • It might be nice to have img.stat.starN and .galN, but I don't see where they come from. The nightly pipeline just works with difference images. I suppose we could fill these in during data release. But I wonder about their usefulness. We certainly want to know star and galaxy counts over the sky as determined by deep detection. But at the single exposure level, it's probably more interesting just to look at the photometric zeropoint.

SRD Metrics

  • I think such a table is a very good idea, but I'd like to see it be part of some group of tables that captures the whole survey state. This will involve understanding various metrics as a function of position over the sky, as well as single numbers. I'd suggest that capturing the sort of quantities that the OpSim? team produces for visualizing the performance of a simulated survey would be a good place to start.

comment:4 Changed 11 years ago by TimAxelrod

  • Cc rlaher added

comment:5 Changed 11 years ago by TimAxelrod

  • Owner changed from Tim Axelrod to jbecla

comment:6 Changed 11 years ago by jbecla

  • Milestone changed from DC3b Apps DB Schema to DC3a MW DB

Changing milestone to dc3a, as there is still one thing to be done here: need to add a directory in svn for functions, and move sdqa function(s) there.

Reminder: the following should be also done as part of this task (for DC3b)

  • follow up with Kem about sdqa for opSim [Jacek]
  • capture in the provenance document information about SDQA needs for two time dimensions [Jacek]

comment:7 Changed 11 years ago by jbecla

  • Owner changed from jbecla to rlaher

Russ, please rewrite the SDQA function http://dev.lsstcorp.org/trac/attachment/wiki/LSSTSDQA/sdqaFunctions.sql to match the latest schema (eg we decided to not use the vBest).

comment:8 Changed 11 years ago by rlaher

  • Owner changed from rlaher to jbecla
  • reviewstatus changed from notReady to needsReview

Hi Jacek,

I attached the re-written SQL file at


It looks like the following SQL files, which contain database content, also need to be modified:

addSdqaThresholdRecords.sql insertSdqaMetricRecords.sql insertSdqaStatusRecords.sql

The modifications are needed for slight changes to database table and field names, and because the metricNames have evolved over the past six weeks.

comment:9 Changed 11 years ago by jbecla

  • Owner changed from jbecla to rlaher

Implemented, see DMS/cat/trunk/sql - Russ please double check, then reassign back to me and change milestone to "DC3b MW DB"

comment:10 Changed 11 years ago by rlaher

  • Owner changed from rlaher to jbecla

Hi Jacek,

In lsstSchema4mysqlDC3a.sql,

TABLE sdqa_Rating_4ScienceFPAExposure, add KEY (sdqa_threshold)

TABLE sdqa_ImageStatus, add UNIQUE UQ_sdqa_ImageStatus_statusName(statusName)

Also add the following:

ALTER TABLE sdqa_Rating_4ScienceFPAExposure ADD CONSTRAINT FK_sdqa_Rating_4ScienceFPAExposure_sdqa_Threshold

FOREIGN KEY (sdqa_thresholdId) REFERENCES sdqa_Threshold (sdqa_thresholdId);

Otherwise, it looks good.



comment:11 Changed 11 years ago by jbecla

  • Milestone changed from DC3a MW DB to DC3b MW DB

FK added (revision 6794). Looks like all is set for DC3a

comment:12 Changed 10 years ago by jbecla

  • Status changed from assigned to closed
  • Resolution set to fixed

comment:13 Changed 7 years ago by robyn

  • Milestone DC3b MW DB deleted

Milestone DC3b MW DB deleted

Note: See TracTickets for help on using tickets.