TAP 1.1 comment on INTERSECTS and point-valued columns

[alberto micol]

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