<div dir="ltr">Hi Mark, hi all,<div>I second your proposal and Markus comment on arguments</div><div>order.</div><div><br></div><div>I may agree with François-Xavier, but would you use</div><div>CONTAINS or DISTANCE in that case?</div><div><br></div><div>I also agree that Laszlo's input is too much for ADQL-2.1,</div><div>especially at this stage. I suggest Laszlo to join the DAL list</div><div>and keep us alerted.</div><div><br></div><div>Cheers</div><div> Marco</div><div><br></div><div><br></div><div><div class="gmail_extra"><div class="gmail_quote">2017-11-05 11:40 GMT+01:00 Mark Taylor <span dir="ltr"><<a href="mailto:m.b.taylor@bristol.ac.uk" target="_blank">m.b.taylor@bristol.ac.uk</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Laszlo,<br>
<br>
thank you for this input. It might be a promising direction for<br>
future major revisions of ADQL, but since I'm no kind of database<br>
expert, I'm not qualified to say.<br>
<br>
However, it's addressing a somewhat different problem than I've<br>
raised in this thread, which is proposing one of the several<br>
existing equivalent options within the existing ADQL 2.1 syntax<br>
to use as a preferred form for a common type of crossmatch.<br>
Your suggestion would be a significant new addition to the ADQL<br>
syntax which I doubt (? unless the authors disagree) is<br>
appropriate for a minor ADQL update, especially near the end of the<br>
revision process.<br>
<br>
I'll leave it to others to consider whether this looks like<br>
something worth pursuing for ADQL v3.<br>
<br>
Best wishes,<br>
<br>
Mark<br>
<div class="HOEnZb"><div class="h5"><br>
On Sat, 4 Nov 2017, Dobos, Laszlo wrote:<br>
<br>
> Hi guys,<br>
><br>
> I'm not on the DAL list but Tamas Budavari forwarded the mail to me so let<br>
> me chip in. In the new skyquery, we put the cross-match definition into the<br>
> FROM clause but not as a list of joined tables but rather something like a<br>
> table-valued function. Putting it into the FROM clause is much better than<br>
> in the WHERE clause, not because it's much easier to parse but because if<br>
> it's in the WHERE clause, it's quite hard to sort the other conditions out<br>
> if there's complex boolean algebra allowed in the clause. If xmatching has<br>
> some special, custom syntax, then the possibility of writing queries that<br>
> cannot be executed can be minimized. Also, putting it into the xmatch clause<br>
> makes query rewriting much easier because you simply replace the xmatch part<br>
> with the output of the xmatch operation and leave all other joins in place<br>
> to execute the final query.<br>
><br>
> Here's an example:<br>
><br>
> SELECT s.objid, s.ra, s.dec, g.objid, g.ra, g.dec, x.ra, x.dec<br>
> INTO twowayxmatch<br>
> FROM XMATCH<br>
> (MUST EXIST IN SDSSDR7:PhotoObjAll AS s WITH(POINT(s.ra, s.dec),<br>
> ERROR(0.1, 0.1, 0.1)),<br>
> MUST EXIST IN GALEXGR6:PhotoObjAll AS g WITH(POINT(g.ra, g.dec),<br>
> ERROR(0.2, 0.2, 0.2)),<br>
> LIMIT BAYESFACTOR TO 1e3) AS x<br>
> WHERE s.ra BETWEEN 0 AND 5 AND s.dec BETWEEN 0 AND 5<br>
> AND g.ra BETWEEN 0 AND 5 AND g.dec BETWEEN 0 AND 5<br>
><br>
> The MUST EXIST will soon be extended to MAY EXIST and MUST NOT EXIST<br>
> (drop-outs, but for those, you need the footprints). This syntax is quite<br>
> extensible, because you can add more catalog-specific options to the WITH()<br>
> part. For instance, we could support extended source matching by replacing<br>
> POINT with ELLIPSE, etc. LIMIT BAYESFACTOR could be replaced with LIMIT<br>
> SEPARATION etc. to use a different algorithm.<br>
><br>
> We also support region-based filtering with the REGION clause. This is<br>
> intentionally not on defined on a per-table basis to avoid query plan<br>
> complexities.<br>
><br>
> SELECT s.objid, s.ra, s.dec, g.objid, g.ra, g.dec, x.ra, x.dec<br>
> INTO twowayxmatch<br>
> FROM XMATCH<br>
> (MUST EXIST IN SDSSDR7:PhotoObjAll AS s WITH(POINT(s.ra, s.dec),<br>
> ERROR(0.1, 0.1, 0.1)),<br>
> MUST EXIST IN GALEXGR6:PhotoObjAll AS g WITH(POINT(g.ra, g.dec),<br>
> ERROR(0.2, 0.2, 0.2)),<br>
> LIMIT BAYESFACTOR TO 1e3) AS x<br>
> REGION 'CIRCLE J2000 0 0 5'<br>
><br>
> You can try it here: <a href="http://test.voservices.net/skyquery" rel="noreferrer" target="_blank">http://test.voservices.net/<wbr>skyquery</a> . It doesn't<br>
> support adql yet but I'm working on that.<br>
><br>
> -Laszlo<br>
><br>
> -----Original Message-----<br>
> From: <a href="mailto:dal-bounces@ivoa.net">dal-bounces@ivoa.net</a> [mailto:<a href="mailto:dal-bounces@ivoa.net">dal-bounces@ivoa.net</a>] On Behalf Of Mark<br>
> Taylor<br>
> Sent: Friday, November 3, 2017 5:27 PM<br>
> To: Francois-Xavier PINEAU <<a href="mailto:francois-xavier.pineau@astro.unistra.fr">francois-xavier.pineau@astro.<wbr>unistra.fr</a>><br>
> Cc: <a href="mailto:dal@ivoa.net">dal@ivoa.net</a><br>
> Subject: Re: ADQL 2.1: Preferred crossmatch syntax<br>
><br>
><br>
> On Fri, 3 Nov 2017, Francois-Xavier PINEAU wrote:<br>
><br>
> > Dear Mark and DAL,<br>
> ><br>
> > Although I agree with the approach, there is one point that bothers me.<br>
> ><br>
> > I know that not everyone agrees on the matter, but I personally prefer<br>
> > to declare the cross-match condition in the JOIN (like in your<br>
> > InteropOct2015DAL<br>
> > presentation) rather than in the WHERE.<br>
> ><br>
> > In fact, in relational algebra, I prefer to see the cross-match as a<br>
> > theta-join, not as a selection on the result of a cross-product.<br>
><br>
> OK, I don't really have an opinion on that, I never really know about what<br>
> syntax is best for joins, or whether it makes a difference.<br>
> [And I'm afraid I wouldn't know a theta-join if one trod on my foot]<br>
><br>
> Is it likely to make a difference in performance terms which of these two<br>
> ways it's done? I.e. do we need to specify here what's the preferred way to<br>
> use a constraint like DISTANCE()<threshold, or is this something we can<br>
> harmlessly leave to the taste of the query author?<br>
><br>
> Mark<br>
><br>
> --<br>
> Mark Taylor Astronomical Programmer Physics, Bristol University, UK<br>
> <a href="mailto:m.b.taylor@bris.ac.uk">m.b.taylor@bris.ac.uk</a> <a href="tel:%2B44-117-9288776" value="+441179288776">+44-117-9288776</a> <a href="http://www.star.bris.ac.uk/~mbt/" rel="noreferrer" target="_blank">http://www.star.bris.ac.uk/~<wbr>mbt/</a><br>
><br>
><br>
><br>
<br>
--<br>
Mark Taylor Astronomical Programmer Physics, Bristol University, UK<br>
<a href="mailto:m.b.taylor@bris.ac.uk">m.b.taylor@bris.ac.uk</a> <a href="tel:%2B44-117-9288776" value="+441179288776">+44-117-9288776</a> <a href="http://www.star.bris.ac.uk/~mbt/" rel="noreferrer" target="_blank">http://www.star.bris.ac.uk/~<wbr>mbt/</a></div></div></blockquote></div><br></div></div></div>