Examples for KSB-ROB TAP service

Sunspot drawings linked to sunspot groups

This example lists all sunspot drawings with associated sunspot group information.

SELECT * FROM uset_sunspot_drawing.epn_links
        JOIN uset_sunspot_drawing.epn_core on uset_sunspot_drawing.epn_core.granule_uid=uset_sunspot_drawing.epn_links.drawing_ref
        JOIN uset_sunspot_group.epn_core on uset_sunspot_drawing.epn_links.group_ref=uset_sunspot_group.epn_core.granule_uid

SPOCA Example 1: Find the size of 10 random coronal holes

SELECT TOP 10
        granule_uid,
        ch_area_projected
FROM
rob_spoca_ch.epn_core

SPOCA Example 2: Find coronal holes between 2 dates

SELECT
        granule_uid,
        time_min
FROM
        rob_spoca_ch.epn_core
WHERE
        time_min >= gavo_to_jd('2020-01-01 00:00:00') AND gavo_to_jd('2020-01-01 12:00:00')>=time_max

SPOCA Example 3: Find all the coronal holes that follow the cornal hole spoca_coronalhole_10022_20171216_060005

The rob_spoca_ch.tracking contains the information on how to link coronal holes in time. The table has 4 columns, the columns "previous" and "next" specify the granule_uid of 2 coronal holes detection at different times that overlap spatially. The columns "overlap_area_projected" and "overlap_area_pixels" specify the area of the overlap.

Note that because the word "next" is a reserved ADQL word, it must always be specified in quotes.

SELECT
        "next"
FROM
        rob_spoca_ch.tracking
WHERE
        previous = 'spoca_coronalhole_10022_20171216_060005'

SPOCA Example 4: Find the size of the coronal holes that follow the cornal hole spoca_coronalhole_10022_20171216_060005

SELECT
        ch1.granule_uid AS previous_ch,
        ch1.ch_area_projected AS previous_area,
        ch2.granule_uid AS next_ch,
        ch2.ch_area_projected AS next_area
FROM
        rob_spoca_ch.epn_core AS ch1
LEFT JOIN rob_spoca_ch.tracking AS t ON
        ch1.granule_uid = t.previous
JOIN rob_spoca_ch.epn_core AS ch2 ON
        t."next" = ch2.granule_uid
WHERE ch1.granule_uid = 'spoca_coronalhole_10022_20171216_060005'

SPOCA Example 5: Find all the coronal holes that splits into more than 1 coronal hole

SELECT
        ch.granule_uid AS previous_ch,
        COUNT(t.previous) AS split_count,
        ivo_string_agg(t."next", ',') AS next_ch_list
FROM
        rob_spoca_ch.epn_core AS ch
LEFT JOIN rob_spoca_ch.tracking AS t ON
        ch.granule_uid = t.previous
GROUP BY
        ch.granule_uid
HAVING
        COUNT(t."next") >= 2