Export PanSTARRS skycells as a 3 week rolling window

[thespacedoctor]

The skycell data will be ingested into ligo_wave database on DB03 and used for coverage stats and plots. Below is a list of what I think we need, followed by the SQL query that should work to grab the data.

• We need exports from both PS04 and PS13PI databases.
• We need WARP-STACK (ws) and STACK-STACK (ss) skycells from both DBs.
• We need the past 3 weeks of this data.
• Export should happen at least once every 2 hours, or more frequently if possible.

The above should result in 4 CSV files.

Here's the query to grab WS skycells:

SELECT
    imageid,
    ppsub_input,
    filename,
    m.exptime exp_time,
    TRUNCATE(mjd_obs, 8) mjd,
    LEFT(fpa_filter, 1) AS filter,
    IF(deteff_counts < 200,
        m.zero_pt + m.deteff_magref+2.5*log(10,exptime),
        m.zero_pt + m.deteff_magref + m.deteff_calculated_offset+2.5*log(10,exptime)) AS limiting_mag
FROM
    tcs_cmf_metadata m
    where filename like "%.ws.%" and mjd_obs > mjdnow - 21

You will need to calculate the mjdnow to get the 3-week window.

To get the STACK-STACK cells the last line needs changed to:

    where filename like "%.ss.%" and mjd_obs > mjdnow - 21

@genghisken

[genghisken]

The query above now works as written - with mjdnow written as mjdnow().

[genghisken]

I don't like this query. I think this one is better. You only want the skycells. I've also no idea what imageid means, so we can probably drop it.

SELECT
    imageid,
    skycell,
    m.exptime exp_time,
    TRUNCATE(mjd_obs, 8) mjd,
    LEFT(fpa_filter, 1) AS filter,
    IF(deteff_counts < 200,
        m.zero_pt + m.deteff_magref+2.5*log(10,exptime),
        m.zero_pt + m.deteff_magref + m.deteff_calculated_offset+2.5*log(10,exptime)) AS limiting_mag
FROM
    tcs_cmf_metadata m
WHERE
    filename like "%.ss.%"
AND
    mjd_obs > mjdnow() - 21
ORDER BY mjd_obs DESC

+-----------+----------+----------+----------------+--------+--------------------+
| imageid   | skycell  | exp_time | mjd            | filter | limiting_mag       |
+-----------+----------+----------+----------------+--------+--------------------+
| 125923367 | 1454.004 |     1800 | 60033.45425179 | z      |  21.47887092187692 |
| 125923348 | 1365.047 |     1800 | 60033.45425179 | z      | 21.445241504457485 |
| 125923328 | 1364.062 |     1800 | 60033.45425179 | z      | 21.722754486945163 |
| 125923356 | 1365.069 |     1800 | 60033.45425179 | z      |  21.74795969679643 |
| 125923355 | 1365.068 |     1800 | 60033.45425179 | z      | 21.699117818024646 |
| 125923347 | 1365.039 |     1800 | 60033.45425179 | z      | 21.739605740752708 |
| 125923312 | 1364.030 |     1800 | 60033.45425179 | z      | 21.779896681305658 |
| 125923343 | 1364.092 |     1800 | 60033.45425179 | z      | 21.742485554066192 |
| 125923338 | 1364.082 |     1800 | 60033.45425179 | z      |  21.73811956971575 |
| 125923319 | 1364.043 |     1800 | 60033.45425179 | z      | 21.592483543899547 |

Additionally I could load up the RINGS.V3.centres file into a database table and join it to get the actual RA and Dec values you need for the plot. I may do that tomorrow.

[thespacedoctor]

Thanks Ken. I already have the RINGS.V3.centres in the ligo_waves database (now on DB3). I was using the imageid as it helped me associate the skycells with the full PS1 pointings scrapped from the PS1 nightlogs. I don't we I need these any longer. We can go with your new query and iterate if I need more info.

[genghisken]

Now runs every hour from ligo@db3.