TAP 1.1 comment on INTERSECTS and point-valued columns

[Dave Morris]

Hi all,

I agree that we should not exclude some functionality because it is not 
supported by a specific platform.

On the other hand, requiring services to provide functionality that is 
hard to implement on their platform acts as a barrier to adoption.

Would the following work as a compromise solution:

INTERSECTS is already declared as an optional feature in the current 
draft [1].

     type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo
     name: INTERSECTS

 From the discussion, it is clear that on some platforms requiring 
support for INTERSECTS with POINT would be a problem.

Can we solve this by adding second feature to cover INTERSECTS with 
POINT ?

So the text for INTERSECTS becomes :

     "The arguments to INTERSECTS SHOULD be geometric expressions 
evaluating
      to either BOX, CIRCLE or POLYGON."

     "If a service supports the additional INTERSECTS-POINT feature, then
      the arguments to INTERSECTS MAY also include POINT."

     "If a service does not support the INTERSECTS-POINT feature, then
      the service MAY return an error when processing INTERSECTS with
      a POINT argument"

Then we add an additional feature for INTERSECTS-POINT :

     type: ivo://ivoa.net/std/TAPRegExt#features-adql-geo
     name: INTERSECTS-POINT

     "This functionality extends the INTERSECTS operator to include 
support
      for POINT arguments."

     "Note - this feature does not add a new operator, it merely acts as 
a
      modifier to the behaviour of the INTERSECTS operator."

This allows services that can support INTERSECTS with POINT to declare 
that ability in the service registration.

If in the future all our service implementations support INTERSECTS with 
POINT with no problem then we can revisit this.

In the mean time, it buys us time, allowing services that do not support 
INTERSECTS with POINT to legitimately return an error.

Hope this helps,
Dave


[1] http://wiki.ivoa.net/internal/IVOA/ADQL/WD-ADQL-2.1-20160502-05.pdf

--------
Dave Morris
Research Software Engineer
Wide Field Astronomy Unit
Institute for Astronomy
University of Edinburgh
--------

On 2017-09-06 14:58, alberto micol wrote:
> Hi Dave, Pat, Markus, all,
> 
> As already stated (see attached discussion if you have nothing better 
> to do),
> I have a major trouble regarding this two paragraphs:
> 
> ADQL2.1, §4.2.14 INTERSECTS:
>> The arguments to INTERSECTS SHOULD be geometric expressions evaluating 
>> to either BOX, CIRCLE,
>> POLYGON, or REGION. Previous versions of this specification allow 
>> POINTs as well and require servers
>> to interpret the expression as a CONTAINS with the POINT moved into 
>> the first position. Servers
>> SHOULD still implement that behaviour, but clients SHOULD NOT expect 
>> it. This behaviour will be
>> dropped in the next major version of this specification.
> 
> TAP 1.1, §2.7.2:
>> POINT(including a pointed-value column or function return value) 
>> cannot be used as an argument to
>> INTERSECTS (even if allowed by older versions of ADQL).
> 
> I’d like to ask to change them both, so not to exclude POINT from 
> INTERSECTS.
> The reasons are provided here below.
> 
> Requiring, as stated in ADQL2.1, to interpret INTERSECTS as a CONTAINS
> might be a valid suggestion for implementing the standard on a
> specific technology, but the standard should not care of how
> developers will implement that.
> 
> Dropping INTERSECTS support for POINTs just because pgsphere cannot
> cope well with it
> is not the right way to go, other databases cope fine with that.
> 
> I think we are confusing a Standard with a particular implementation
> of it done in pgsphere.
> So, please let’s go back to the basic point: the ADQL is a standard,
> and a Standard should:
> - not mandate implementations
> - not be based on particular technologies
> - not limit its capabilities due to limitations in a specific 
> technology
> 
> In the ESO case:
> 
> - SQLServer is able to INTERSECTS any two “geometry” data types.
> 
> - ElasticSearch (Lucene) will soon do that too (it does already at
> ESO, thanks to the work of
> an ESO colleague who is collaborating with the Lucene team: Ignacio
> Vera will indeed present this work
> at the ADASS, do not miss it!)
> 
> So, there is no valid reason to bend the standard as it has been done 
> so far.
> We are talking ADQL here, and not “ADQL implemented using PGSphere”.
> 
> To avoid all the mentioned problem, I please ask to change the TAP1.1
> and ADQL2.1 specifications,
> so to re-admit POINTs as valid arguments to the INTERSECTS function.
> 
> The standard should also specify what implementors should do if a
> specific functionality cannot be supported by their own server (e.g.
> throwing a specific error), and not the other way around, banning some
> functionality because otherwise an error occurs to some implementors.
> 
> Many thanks,
> Alberto
> 
> 
> 
>> On 21 Aug 2017, at 13:27, Markus Demleitner 
>> <msdemlei at ari.uni-heidelberg.de> wrote:
>> 
>> Hi Alberto,
>> 
>> On Mon, Aug 21, 2017 at 12:29:41PM +0200, alberto micol wrote:
>>> I need to come back to this one issue of the missing INTERSECTS 
>>> support for point-valued columns.
>>> I quote here the relevant ADQL2.1 text:
>>>> The arguments to INTERSECTS SHOULD be geometric expressions 
>>>> evaluating to either BOX, CIRCLE,
>>>> POLYGON, or REGION. Previous versions of this specification allow 
>>>> POINTs as well and require servers
>>>> to interpret the expression as a CONTAINS with the POINT moved into 
>>>> the first position. Servers
>>>> SHOULD still implement that behaviour, but clients SHOULD NOT expect 
>>>> it. This behaviour will be
>>>> dropped in the next major version of this specification.
>>> 
>>> Here is my simple and useful ObsCore use case:
>>>   Find *all* ESO products whose footprints (s_region) intersects a
>>>   given region.
>>> 
>>> Some of the ESO products have an s_region expressed as a polygon
>>> (e.g. images), some other products (e.g. all the ESO spectra) have
>>> an s_region that is a point.
>> 
>> My take is that this is where things get odd -- I don't think
>> s_region should be a point in the first place; "point-like" and
>> "extended" make things different for so many applications.  Also, are
>> you sure your database system can cope with this sort of polymorphic
>> column?
>> 
>> And anyway, the apertures of the spectra aren't pointlike in reality.
>> So, I'd claim using little circles (or some approximation using
>> polygons, which is what I do) with a radius of the order of the
>> aperture would both map reality more closely and make for a more
>> self-consistent data model.
>> 
>> 
>>> Do I understand correctly that a user executing this ADQL:
>>> 
>>>   select * from ivoa.ObsCore where INTERSECTS( s_region, 
>>> circle(??????,83.867,-69.276,1)=1)
>>> 
>>> should not expect to see returned any record with a point-valued 
>>> s_region,
>>> that is, none of the ESO spectra?
>> 
>> I'd expect the database to issue an error when trying to run such a
>> query when s_region is a point.  I've just tried
>> 
>>  select * from lsw.plates where spoint '(30d, 30d)' && coverage;
>> 
>> (where coverage is an spoly), which somewhat confusingly give a
>> "ERROR:  operator is not unique: spoint && spoly".
>> 
>> Anyway, as far as I can see, when we strike the type-based decay to
>> contains, intersects(point, region) essentially becomes undefined,
>> which we should to avoid, too.  Perhaps we should then (i.e., in ADQL
>> 2.1-next) say:
>> 
>>  A system must either treat intersects(point, region) like
>>  contains(point, region) or raise an error.
>> 
>> or:
>> 
>>  intersects(point, region) must raise an error.
>> 
>> 
>> or:
>> 
>>  intersects(point, region) is like contains(point, region)
>> 
>> 
>> -- since these are our functions, we are responsible for precisely
>> defining their semantics.
>> 
>> Of course, with the last option, we're back at ADQL 2.0 and hence
>> perhaps we shouldn't touch the whole decaying of INTERSECTS-thing in
>> 2.1 at all? Just as a reminder, the motivation to drop the decay
>> requirement was that it is the only place in ADQL 2.0 where you
>> absolutely have to know argument types when morphing a query to SQL
>> (at least unless your database doesn't magically support
>> intersects(point, region) itself).
>> 
>> However, I think all the major ADQL implemenations do some sort of
>> type inference even without the INTERSECTS business.  So perhaps that
>> concern is moot by now and requiring type inference isn't a problem
>> in the first place?
>> 
>>> Should we change the ESO implementation and return, for spectra and
>>> visibilities, a CIRCLE with a zero radius instead of a point?
>> 
>> Well, I'd avoid the (unrealistic) zero radius, but other than that,
>> yes, regardless of any INTERSECT decaying.
>> 
>>          -- Markus