TAP Standards and Feedback

1. TAP Standards and Feedback Deoyani Nandrekar-Heinis Johns Hopkins University

2. Points of Discussion Upload tables Authentication VOSpace integration ADQL and spatial queries Other issues

3. Upload Tables Scope of tables Life of uploaded table Access Usage and query edit

4. Authentication Upload access to only authorized user User query and result access

5. VOSpace Integration Store results in user’s vospace Store job.xml and result in vospace

6. ADQL and Spatial Queries Generic Queries? Spatial constraints e.g. Using HTM ids Complex Queries Parsing Difficulties Suggestions and Options

7. ADQL Example • ADQL (Spatial) • SELECT o.ra, o.dec FROM photoobjall as o WHERE CONTAINS( POINT('J2000', o.ra, o.dec), Region('CIRCLE J2000 180 0 0.3')) = 1 • SDSS SQL Query: • Select o.ra, o.dec From photoobjAll as o inner join dbo.fSphGetHtmRanges(dbo.fSphSimplifyString('CIRCLE J2000 180 0 0.3')) h on o.HtmID between h.HtmIdStart and h.HtmIdEnd Where dbo.fSphRegionContainsXYZ(dbo.fSphSimplifyString('CIRCLE J2000 180 0 0.3'), o.cx ,o.cy,o.cz) = 1

8. ADQL and Spatial Queries SELECT a.objid, a.ra, a.dec, b.objid, b.ra, b.dec FROM CatalogA AS a INNER JOIN CatalogB AS b ON CONTAINS(CIRCLE(a.ra, a.dec, 0.1), POIN T(b.ra, b.dec)) WHERE (some condition) circle has to be calculated for every row in A in order to evaluate the query HTM based optimization (not sufficient?) Solution:: temporary tables, generate the HTM ranges, put an index on them and then do the BETWEEN query.

9. ADQL and Spatial Queries CREATE TABLE ##htmtemp (ObjID bigint, HtmStart bigint, HtmEnd bigint) -- + create the indices here: PK on ObjID, cover index on HtmStart/End INSERT ##htmtemp WITH (TABLOCK) (ObjID, HtmStart, HtmEnd) SELECT ObjID, htm.Start, htm.End FROM CatalogA CROSS APPLY dbo.fHtmCoverCircleEq(Ra, Dec, 0.1) AS htm SELECT a.objid, a.ra, a.dec, b.objid, b.ra, b.dec FROM CatalogA AS a INNER JOIN ##htmtemp htm ON htm.ObjID = a.ObjID INNER JOIN CatalogB AS b ON dbo.fGetHtmEq(b.Ra, B.Dec) BETWEEN htm.HtmStart AND htm.HtmEnd WHERE ...

10. ADQL and Spatial Queries CREATE TABLE ##htmtemp (ObjID bigint, HtmStart bigint, HtmEnd bigint) -- + create the indices here: PK on ObjID, cover index on HtmStart/End INSERT ##htmtemp WITH (TABLOCK) (ObjID, HtmStart, HtmEnd) SELECT ObjID, htm.Start, htm.End FROM CatalogA CROSS APPLY dbo.fHtmCoverCircleEq(Ra, Dec, 0.1) AS htm SELECT a.objid, a.ra, a.dec, b.objid, b.ra, b.dec FROM CatalogA AS a INNER JOIN ##htmtemp htm ON htm.ObjID = a.ObjID INNER JOIN CatalogB AS b ON dbo.fGetHtmEq(b.Ra, B.Dec) BETWEEN htm.HtmStart AND htm.HtmEnd WHERE ... Multiway joins are slow!!

11. Special Spatial Objects bright in Optical and invisible in UV Query: SELECT ...FROM SDSS AS a INNER JOIN Galex AS b ON NOT CONTAINS(CIRCLE(a.ra, a.dec, 0.1), POINT(b.ra, b.dec)) Ugly where clauses WHERE CONTAINS(CIRCLE(POINT(a.ra, a.dec), 0.5), POINT(b.ra, b.dec) OR     CONTAINS(CIRCLE(POINT(a.ra + 0.2, a.dec + 0.2), 0.5), POINT(b.ra,b.dec) Multiple conditions where clause WHERE CONTAINS(CIRCLE(POINT(a.ra, a.dec), 0.5), POINT(b.ra, b.dec) AND type= galaxy OR     CONTAINS(CIRCLE(POINT(a.ra, a.dec), 0.5), POINT(b.ra, b.dec) AND type= star

12. Suggestions Spatial region: only constant values Spatial constraints: Outside where clause CROSS MATCH : handling separately

13. Thank You !