osa logo
OSA Home
Start Here
Data Overview
Known Issues
the Surveys
Coverage Maps
Schema browser
Data access
Login
Archive Listing
GetImage
MultiGetImage
Region
Freeform SQL
CrossID
SQL Cookbook
Q&A
Glossary
Release History
Gallery
Publications
Monitor
Downtime
Links
osa logo bottom
IFA     ROE
Home | Overview | Browser | Access | Login | Cookbook 
  OSA logo

SQL queries - notes and tips

Links to image cut-outs
If your results contain the following three columns: RA, Dec and framesetID/multiframeID then links will be retuned in the HTML results table that allow you to view image cut-outs of that part of the sky using the multiframes referenced by framesetID or mulitframeID eg

select top 10 ra,dec,framesetID,hapermag3 from lasSource where hapermag3 > 0
or
select top 10 ra,dec,multiframeID,apermag3 from atlasDetection where apermag3 > 0

Another way of achieving a similar result can be combined with TOPCAT. This method requires you to build a column URL as part of the query eg

select top 10 cast ('http://osa.roe.ac.uk:8080/osa/GetImage?mode=show&database='+ db_name()+'&ra='+ CONVERT(varchar(17), cast(ra as decimal (17,6)) )+ '&dec='+CONVERT(varchar(17), cast(dec as decimal (17,5)))+ '&fsid='+convert(varchar(18),framesetid)as varchar(192)) as url,ra,dec from atlassource

Again the table you are querying needs to have the columns RA,Dec and framesetID/multiframeID. If querying detection tables rather than source tables i.e using multiframeID then it would be

select top 10 cast ('http://osa.roe.ac.uk:8080/osa/GetImage?mode=show&database='+ db_name()+'&ra='+ CONVERT(varchar(17), cast(ra as decimal (17,6)) )+ '&dec='+CONVERT(varchar(17), cast(dec as decimal (17,5)))+ '&mfid='+convert(varchar(18),multiframeid)as varchar(192)) as url,ra,dec from atlasDetection

The format that the query should be written to should be FITS or VOTable. Once loaded into TOPCAT click on the Activation Action button in the main window. Then check View URL as Web Page and select Web Page Locataion Column as URL and Browser Type as system browser the click OK. Now when viewing the table in TOPCAT if you click on a row the cut-out(s) should be displayed in your default browser.

Uploading and using a file as #userTable
The enhanced version of the SQL form allows a file to be uploaded as a temporary table, called #userTable, and used in the query. If column names are given in the file then the same names are used where possible in #userTable (some names might be reserved SQL keywords). If no column names are present then the columns are labelled col1 ... colN.
This functionality is useful if you have a long list of ID numbers eg multiframeIDs, sourceIDs that you wish to use in you query. For example if a file contained a column of sourceIDs then it could be uploaded and used with

select s.ra,s.dec,s.framesetid from atlasSource as s, #userTable as u where s.sourceID=u.sourceID

The temp table #userTable only lasts for the lifetime of the query.



Home | Overview | Browser | Access | Login | Cookbook
Listing | FreeSQL
Links | Credits

WFAU, Institute for Astronomy,
Royal Observatory, Blackford Hill
Edinburgh, EH9 3HJ, UK

osa-support@roe.ac.uk
23/4/2015