This is a number of queries that I’ve come up with over the years to obtain different results using HELIO services. I hope these examples help you to build the queries you need to solve your science questions. I will update this post as I find new fancy ways to use this system.

HELIO Event Catalogue (HEC)

The HEC provides a number of catalogues about events in the heliosphere, some of them have been built automatically while others were manually gathered. Check each catalogue source for the information about what it contains and how has been populated.

Group flares per month and type

SELECT 
    DATE_TRUNC('month', time_start) AS month, SUBSTRING(xray_class from 1 for 3) AS xray, 
    COUNT(*) 
  FROM goes_sxr_flare 
    WHERE time_start > '2002-01-01' 
    GROUP BY month,xray 
    ORDER BY month

ascii histogram

SELECT 
    EXTRACT(year from time_start) AS year, COUNT(*) as count, 
    REPEAT('*',((COUNT(*))::INT)) AS bar 
  FROM goes_sxr_flare 
    WHERE time_end >= (time_start + INTERVAL '2 hours')  
    GROUP BY year ORDER BY year

Number of Flares per year longer than two hours

SELECT 
    EXTRACT(year from time_start) AS year, COUNT(*) as count, 
    REPEAT('*',((COUNT(*))::INT)) AS bar 
  FROM goes_sxr_flare 
    WHERE time_end >= (time_start + INTERVAL '2 hours') 
    GROUP BY year
    ORDER BY year

Long duration flares happening in the north hemisphere and close to the central meridian

SELECT * FROM goes_sxr_flare
    WHERE time_end >= time_start + interval '2 hours' AND
          (long_hg BETWEEN -5.0 AND 5.0) AND
          lat_hg >= 0 
    ORDER BY time_start

Hours from a date and other operations in other fields

SELECT EXTRACT(EPOCH from time_start - date '2003-01-01')::int/3600. as hours, 
       duration, pa, pa_width, pa - pa_width/2 as pa_neg, pa + pa_width/2 as pa_pos, v 
   FROM cactus_soho_cme 
     WHERE time_start BETWEEN '2003-01-01' AND '2004-01-01'

Time of ARs between anything and beta-gamma-delta

SELECT
   bgd.nar, EXTRACT('days' from (bgd.mint-nbgd.maxt)) as dift, bgd.bgd_n, 
   nbgd.maxt, bgd.mint, nbgd.mag_class 
FROM 
   (
   SELECT 
      nar, MIN(time_start) as mint, COUNT(*) as bgd_n 
     FROM noaa_active_region_summary 
       WHERE time_start>='2012-03-01 00:00:00' AND time_start<='2016-04-01 23:59:59' AND 
             mag_class='Beta-Gamma-Delta' 
       GROUP BY nar
   ) bgd 
INNER JOIN 
   (
   SELECT 
      nar, MAX(time_start) as maxt, mag_class 
   FROM noaa_active_region_summary 
     WHERE time_start>='2012-03-01 00:00:00' AND time_start<='2016-04-01 23:59:59' AND 
           mag_class!='Beta-Gamma-Delta' 
     GROUP BY nar, mag_class 
     ORDER BY nar
   ) nbgd 
ON (bgd.nar = nbgd.nar) WHERE bgd.mint > nbgd.maxt

Join GOES and RHESSI events around the 2003 Halloween event

SELECT g.time_start as GOES_Ts, r.time_start as RHESSI_Ts, 
       g.xray_class, (g.time_start - r.time_start) as Dt
  FROM goes_sxr_flare g, rhessi_hxr_flare r
    WHERE g.time_start BETWEEN '2003-10-20' AND '2003-11-10' AND
          (g.time_start, g.time_end) OVERLAPS (r.time_start, r.time_end) AND
          g.time_end >= g.time_start + INTERVAL '2 hours'

CMEs after flares from an Active Region

SELECT goes.time_start as goes_ts, goes.nar, goes.lat_hg, goes.xray_class, 
       cme.time_start as cme_ts, cme.pa_width, cme.event_detail
  FROM goes_sxr_flare AS goes, cactus_soho_cme AS cme 
    WHERE cme.time_start BETWEEN (goes.time_start + INTERVAL '30 minutes') AND 
                                 (goes.time_start + INTERVAL '4 hours') AND
          goes.nar = 11389 AND
          goes.xray_class > 'C'
    ORDER BY goes.time_start, cme.pa_width

HELIO Feature Catalogue (HFC)

The HFC provides access to features detected by codes running within HELIO systems, that includes active regions, sunspots, coronal holes, etc. Some of these features have been detected by different codes - check the information of each algorithm to understand their limitations. To use the SQL query you have first to remove the line breaks in the queries below.

Number of filaments seen per month when ARs detected by SMART were present

SELECT EXTRACT(year from fil.date_obs) AS year, 
       EXTRACT(month from fil.date_obs) AS month, COUNT(1) 
   FROM view_fil_hqi AS fil, view_ar_hqi AS ar
     WHERE fil.date_obs BETWEEN '1997-03-25' AND '1998-06-16' AND
           ar.date_obs BETWEEN '1997-03-25' AND '1998-06-16' AND 
           DATE(fil.date_obs)=DATE(ar.date_obs) AND 
           ar.noaa_number IS NOT NULL AND 
           ar.code="SMART" 
     GROUP BY year, month
     ORDER BY year, month

Instrument Location Service (ILS)

The ILS provides information about the location of planets and some spacecraft in the heliosphere. This is very useful to find, for example when STEREO A was at certain degrees away from Earth or other spacecraft.

SELECT t1.time, t1.long_hci, t2.time, t2.long_hci,
       ABS(t1.long_hci-t2.long_hci)
   FROM trajectories AS t1, trajectories AS t2
     WHERE t1.time >= '2006-10-01' AND t1.time <= '2015-01-01' AND
           t2.time >= '2006-10-01' AND t2.time <= '2015-10-01' AND
           t1.time = t2.time AND
           t1.target_obj = 'stereob' AND t2.target_obj = 'earth' AND
           IF(ABS(t1.long_hci - t2.long_hci) > 180,
              360 - ABS(t1.long_hci - t2.long_hci) >= 118,
              ABS(t1.long_hci - t2.long_hci) >= 118) AND
           IF(ABS(t1.long_hci - t2.long_hci) > 180,
              360 - ABS(t1.long_hci - t2.long_hci) <= 122,
              ABS(t1.long_hci - t2.long_hci) <= 122)
     ORDER BY t1.time

One value per month per orbit

Don’t remember what I was trying to do with this

SELECT MONTH(time) AS month, time, target_obj,date(time) as time 
  FROM trajectories 
    WHERE time BETWEEN '2002-10-28' AND '2003-11-03' AND 
          target_obj='Earth'
    GROUP BY MONTH(Time)

David PS

Handy HELIO SQL queries