Examples

Retrieve all spectra within certain temperature and HRV ranges (DR6)

The example shows how to select basic characteristics from the DR6 tables limited to a certain range in hrv and temperature

SELECT
    t1.rave_obs_id, t1.ra_input, t1.dec_input,
    t2.hrv_sparv, t2.hrv_error_sparv, t2.snr_med_sparv,
    t3.teff_cal_madera, t3.teff_error_madera, t3.logg_cal_madera, t3.logg_error_madera,
    t3.m_h_cal_madera, t3.m_h_error_madera, t3.snr_madera, t3.algo_conv_madera
FROM ravedr6.dr6_obsdata AS t1
JOIN ravedr6.dr6_sparv   AS t2 ON t1.rave_obs_id = t2.rave_obs_id
JOIN ravedr6.dr6_madera  AS t3 ON t1.rave_obs_id = t3.rave_obs_id
WHERE t2.hrv_sparv between 20.0 and 25.0 
AND t3.algo_conv_madera = 1
AND t3.teff_cal_madera between 6500 and 7000

Retrieve RAVE data and spectra files with certain conditions (DR6)

4 fold join for retrieving the values and the files within a certain hrv range

SELECT t1.rave_obs_id, t1.ra_input, t1.dec_input, t2.hrv_sparv, t2.hrv_error_sparv, t2.snr_med_sparv, t3.teff_cal_madera, t3.teff_error_madera, t3.logg_cal_madera, t3.logg_error_madera, t3.m_h_cal_madera, t3.m_h_error_madera, t3.snr_madera, t3.algo_conv_madera,
t4.spectrum_png, t4.spectrum_fits
FROM ravedr6.dr6_obsdata AS t1 
JOIN ravedr6.dr6_sparv AS t2 ON t1.rave_obs_id = t2.rave_obs_id 
JOIN ravedr6.dr6_madera AS t3 ON t1.rave_obs_id = t3.rave_obs_id 
JOIN ravedr6.dr6_spectra AS t4 ON t1.rave_obs_id = t4.rave_obs_id 
WHERE t2.hrv_sparv between 20.0 and 25.0 
AND t3.algo_conv_madera = 1

Count the number of objects in the RAVE DR4 database

SELECT count(*)
FROM ravedr4.rave_dr4;

Multiple observations in DR4

How many objects are observed more than once in DR4 (=> use DR6 table dr6_repeats)

SELECT F.raveid, F.radeg, F.dedeg, T.N 
FROM
    ( SELECT raveid, count(*) AS N 
          FROM ravedr4.rave_dr4
              GROUP BY raveid
              HAVING count(*) > 1 
              ORDER BY N DESC ) T JOIN ravedr4.rave_dr4 F 
    ON F.raveid = T.raveid

Multiple observations in DR3

Show all multiple observations of DR3A, ordered by N observations (=> use DR6 table dr6_repeats)

SELECT F.raveid, F.radeg, F.dedeg, T.N 
FROM 
    ( SELECT raveid, count(*) AS N 
      FROM ravedr3.rave_dr3a 
          GROUP BY raveid
          HAVING count(*) > 1 
          ORDER BY N DESC ) T JOIN ravedr3.rave_dr3a F 
    ON F.raveid = T.raveid

Get column names and metadata with TAP

Retrieve column names, type and metadata of a table using the IVOA tap_schema

SELECT 
	* 
FROM rave_tap_schema.columns
WHERE table_name like 'ravedr6.dr6_sparv'

Simple Cone Search

using the Simple Conesearch Additional Conesearch queries, which deliver more information from the DR6 (use: PostgreSQL query language) ------- SELECT t1.rave_obs_id, t1.ra_input, t1.dec_input, t2.hrv_sparv, t2.hrv_error_sparv, t2.snr_med_sparv FROM ravedr6.dr6_obsdata AS t1 JOIN ravedr6.dr6_sparv AS t2 ON t1.rave_obs_id = t2.rave_obs_id WHERE t1.pos @ scircle(spoint(RADIANS(23.5), RADIANS(-12.5)), RADIANS(0.2) ---- SELECT t1.rave_obs_id, t1.ra_input, t1.dec_input, t2.hrv_sparv, t2.hrv_error_sparv, t2.snr_med_sparv, t3.teff_cal_madera, t3.teff_error_madera, t3.logg_cal_madera, t3.logg_error_madera, t3.m_h_cal_madera, t3.m_h_error_madera, t3.snr_madera, t3.algo_conv_madera FROM ravedr6.dr6_obsdata AS t1 JOIN ravedr6.dr6_sparv AS t2 ON t1.rave_obs_id = t2.rave_obs_id JOIN ravedr6.dr6_madera AS t3 ON t1.rave_obs_id = t3.rave_obs_id WHERE t1.pos @ scircle(spoint(RADIANS(23.5), RADIANS(-12.5)), RADIANS(0.2)

-- using  degrees
-- ra := 23.5, dec := -12.5, search_radius := 0.2

SELECT rave_obs_id, ra_input, dec_input, 
		DISTANCE( POINT('ICRS', ra_input, dec_input), POINT('ICRS', {23.5}, {-12.5}) ) AS dist
FROM ravedr6.dr6_obsdata
WHERE 1 = CONTAINS( POINT('ICRS', ra_input, dec_input), CIRCLE('ICRS', {23.5}, {-12.5}, {0.2}) )

Get objects with radial velocites within a range (DR4)

select all objects in DR4 with radial velocities between 5 km/s and 25km/s, ordered by velocity

SELECT * 
FROM ravedr4.rave_dr4
  WHERE hrv
    BETWEEN 5.0 AND 25.0 
  ORDER BY hrv DESC

StellarParams for ChromoActiveStars

extract Stellar Parameters from RAVE_DR4 for stars which are in Chromo_Active_Stars2 and limit this to year 2008

SELECT t2.*, t1.teff_sparv, t1.logg_sparv, t1.met_k, t1.met_n_k 
FROM ravedr4.rave_dr4 as t1, ravedr4.chromo_active_stars as t2  
  WHERE t1.rave_obs_id = t2.rave_obs_id
    AND t1.rave_obs_id LIKE '2008%'