ADQL new function to compute area of intersection?

[alberto micol]

Dear All,
cc: Bruno, for the Standing Committee on Science Priorities <http://wiki.ivoa.net/twiki/bin/view/IVOA/IvoaSciencePriorities>

The following science use case cannot be accomplished without a new ADQL function:

I want to find images of the Galactic Centre in two bands (J and H) provided that the images
in the 2 bands overlap, in order to get the colours (J-H) of the sources imaged by both.
The overlapping region must be big enough so that I can detect sources
in both images: an overlap of, say, 0.001 square degrees would be irrelevant for my science case.

I’ll show in the PS how this can be achieved using ObsCore with a user-defined function.

The issue is that, to avoid images just “touching” each other, we had to create a user-defined function
that computes the area of the intersection of the two regions.

Issues: (1) only those users that read the documentation or the TAPRegExt will know
that such functionality exists, and (2) this function cannot be used when querying other data centres.

Can I suggest to introduced a new function in ADQL 2.1 which can be used to compute
the area of the intersections between two regions?

Many thanks,
Alberto

PS: Here below the corresponding ADQL, including the ESO_INTERSECTION user-defined function.

The first part of the use case can be easily implemented using ObsCore, doing a cross match like:

SELECT TOP 50 J.target_name, J.obs_publisher_did, J.s_ra, J.s_dec, J.em_min, J.em_max, J.instrument_name,
              H.target_name, H.obs_publisher_did, H.s_ra, H.s_dec, H.em_min, H.em_max, H.instrument_name,
              J.s_region, H.s_region\n\
FROM\n\
     (SELECT * FROM ivoa.Obscore WHERE dataproduct_type='image'
        AND INTERSECTS(CIRCLE('ICRS',266.42,-29.0,5),s_region) = 1  -- Contains galactic centre
        AND em_min < 1.265E-6 AND em_max > 1.265E-6                 -- Contains lambda(J)
     ) as J
,
     (SELECT * FROM ivoa.Obscore WHERE dataproduct_type='image'
        AND INTERSECTS(CIRCLE('ICRS',266.42,-29.0,5),s_region) = 1  -- Contains galactic centre
        AND em_min < 1.66E-6 AND em_max > 1.66E-6                   -- Contains lambda(H)
     ) as H
WHERE INTERSECTS(J.s_region, H.s_region)=1
ORDER by 1, 8


This statement though returns all overlapping images, that is, even the ones
that overlap for an insignificant amount.

To solve this problem and return only images with significant overlap
we have defined an new function: ESO_INTERSECTION
which computes the area of the intersection between 2 regions and returns
the the area in square degrees:

SELECT TOP 50 J.target_name, J.obs_publisher_did, J.s_ra, J.s_dec, J.em_min, J.em_max, J.instrument_name,
              H.target_name, H.obs_publisher_did, H.s_ra, H.s_dec, H.em_min, H.em_max, H.instrument_name,
              ESO_INTERSECTION(J.s_region,H.s_region) as OverlappingArea, J.s_region, H.s_region
FROM
     (SELECT * FROM ivoa.Obscore WHERE dataproduct_type='image'
        AND INTERSECTS(CIRCLE('ICRS',266.42,-29.0,5),s_region) = 1
        AND em_min < 1.265E-6 AND em_max > 1.265E-6
     ) as J
,
     (SELECT * FROM ivoa.Obscore WHERE dataproduct_type='image'
        AND INTERSECTS(CIRCLE('ICRS',266.42,-29.0,5),s_region) = 1
        AND em_min < 1.66E-6 AND em_max > 1.66E-6
     ) as H
WHERE INTERSECTS(J.s_region, H.s_region)=1
  AND ESO_INTERSECTION(J.s_region,H.s_region) > 1.79
ORDER by 1, OverlappingArea desc


—oOo—

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ivoa.net/pipermail/dal/attachments/20170607/c8cbf026/attachment.html>