Herschel archive ADQL example queries

PACS source point catalogue cone search sorted by distance on band 70

Use case: I want to retrieve all the objects in PACS source point catalogue, band 70, in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: hsa.pacs_point_source_070

Query: 
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.pacs_point_source_070
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

PACS source point catalogue cone search sorted by distance on band 100

Use case: I want to retrieve all the objects in PACS source point catalogue, band 100, in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: hsa.pacs_point_source_100

Query: 
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.pacs_point_source_100
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

PACS source point catalogue cone search sorted by distance on band 160

Use case: I want to retrieve all the objects in PACS source point catalogue, band 160, in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: hsa.pacs_point_source_160

Query: 
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.pacs_point_source_160
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

SPIRE source point catalogue cone search sorted by distance on band 250

Use case: I want to retrieve all the objects in SPIRE source point catalogue, band 250, in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: hsa.spire_point_source_250

Query: 
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.spire_point_source_250
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

SPIRE source point catalogue cone search sorted by distance on band 350

Use case: I want to retrieve all the objects in SPIRE source point catalogue, band 350, in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: hsa.spire_point_source_350

Query: 
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.spire_point_source_350
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

SPIRE source point catalogue cone search sorted by distance on band 500

Use case: I want to retrieve all the objects in SPIRE source point catalogue, band 500, in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: hsa.spire_point_source_500

Query: 
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.spire_point_source_500
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

Sources combining two catalogues

Get sources from PACS point source catalogue, band 70, by distance respect PACS point source catalogue, band 160, sources.

Target table: hsa.pacs_point_source_070

Query: 
SELECT red.source_id FROM hsa.pacs_point_source_070 AS blue
INNER JOIN hsa.pacs_point_source_160 as red
ON DISTANCE(POINT('ICRS', blue.ra, blue.dec),POINT('ICRS', red.ra, red.dec)) < 20/3600.
WHERE red.source_id = 'HPPSC160A_J054512.2-011335'

Target distance to all catalogue sources, specifying a radius

Get the distances to a specific target (e.g. located at located at ra=10.6847083,dec=41.26875) of all sources inside a circle or radius 5 arc min (0.001388888888888889 deg.) of PACS point source catalogue, band 70.

Target table: hsa.pacs_point_source_070

Query: 
SELECT TOP 500 source_id,ra,dec, 
DISTANCE(  POINT('ICRS', ra, dec),POINT('ICRS',10.6847083,41.26875)) as dist 
FROM hsa.pacs_point_source_070  WHERE 
CONTAINS(POINT('ICRS',hsa.pacs_point_source_070.ra,hsa.pacs_point_source_070.dec),
CIRCLE('ICRS',10.6847083,41.26875,0.001388888888888889))=1

Get fluxes in several PACS bands for a specifc source

Get the fluxes in several PACS bands for 'NGC4579' source (target).

Target table: hsa.pacs_point_source_070, hsa.pacs_point_source_100, pacs_point_source_160

Query: 
SELECT obs.observation_id, 
p70.source_id AS p70_source, p70.flux AS p70_flux,
p100.source_id AS p100_source, p100.flux AS p100_flux,
p160.source_id AS p160_source, p160.flux AS p160_flux
FROM hsa.v_active_observation AS obs
LEFT JOIN hsa.pacs_point_source_070 AS p70 ON obs.observation_id = p70.obsid
LEFT JOIN hsa.pacs_point_source_100 AS p100 ON obs.observation_id = p100.obsid
LEFT JOIN hsa.pacs_point_source_160 AS p160 ON obs.observation_id = p160.obsid
WHERE obs.target_name = 'NGC4579' ORDER BY obs.observation_id

Get fluxes in several PACS bands by ra/dec, specifing a radius

Get the fluxes in several PACS bands for ra=219.56867 and dec=-0.34969 .

Target table: hsa.pacs_point_source_070, hsa.pacs_point_source_100, pacs_point_source_160

Query: 
SELECT p.obsid, p.source_id AS p70_source, p.flux AS p70_flux,
              q.obsid, q.source_id AS p100_source, q.flux AS p100_flux,
              r.obsid,  r.source_id AS p160_source, r.flux AS p160_flux
FROM
   (SELECT p70.obsid,p70.source_id, p70.flux
           FROM hsa.pacs_point_source_070 AS p70
           WHERE (1=CONTAINS(POINT('ICRS', p70.ra, p70.dec),CIRCLE('ICRS', 219.5687, -0.3497, 0.0014)))
 ) AS p
FULL OUTER JOIN
(SELECT p100.obsid,p100.source_id, p100.flux
      FROM hsa.pacs_point_source_100 AS p100
      WHERE (1=CONTAINS(POINT('ICRS', p100.ra, p100.dec),CIRCLE('ICRS', 219.5687, -0.3497, 0.0014)))
) as q ON p.obsid = q.obsid
FULL OUTER JOIN (
    SELECT p160.obsid,p160.source_id, p160.flux
        FROM hsa.pacs_point_source_160 AS p160
        WHERE (1=CONTAINS(POINT('ICRS', p160.ra, p160.dec),CIRCLE('ICRS', 219.5687, -0.3497, 0.0014)))
) AS r ON q.obsid = r.obsid

Get all data for a HIFI spectral line native for an specific science category/area and species name

Get all data for HIFI spectral line native for science area 'Interstellar Medium/Star Formation' and science category 'Star Formation/Young Stellar Objects' and species name 'o-H2O'.

Target table: hsa.hifi_spectral_line_native

Query: 
SELECT * FROM hsa.hifi_spectral_line_native AS cat
INNER JOIN hsa.v_active_observation AS aor ON aor.observation_id = cat.obsid
WHERE aor.science_area = 'Interstellar Medium/Star Formation' AND
aor.science_category = 'Star Formation/Young Stellar Objects' AND
cat.species = 'o-H2O'

Circle around a defined RA and Dec in SPIRE spectral feature finder catalogue

Select points based on distance from a point defined in RA and Dec. In SPIRE spectral feature finder catalogue. Set condition that entities are within a 0.0833 deg radius circle from defined point. Order by distance.

Target table: hsa.spire_spectral_feature_finder_catalogue

Query: 
SELECT DISTANCE(
POINT('ICRS', ra, dec),
POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.spire_spectral_feature_finder_catalogue
WHERE 1=CONTAINS(
POINT('ICRS', ra, dec),
CIRCLE('ICRS', 266.41683, -29.00781, 0.08333333))
ORDER BY dist ASC

Search for all the SPIRE spectral feature finder catalogue information of an object

Show all information in the SPIRE spectral feature finder catalogue for object name 'MWC349'. Order by observation ID number.

Target table: hsa.spire_spectral_feature_finder_catalogue

Query: 
SELECT *
FROM hsa.spire_spectral_feature_finder_catalogue AS obs
WHERE obs.object = 'MWC349' ORDER BY obs.obsid

Search for 500 nearest sources to a given RA and Dec in SPIRE spectral feature finder catalogue

Select 500 points based on distance from a point defined in RA and Dec in SPIRE spectral feature finder catalogue. Order by distance.

Target table: hsa.spire_spectral_feature_finder_catalogue

Query: 
SELECT TOP 500 DISTANCE(
POINT('ICRS', ra, dec),
POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM hsa.spire_spectral_feature_finder_catalogue
ORDER BY dist ASC