Information for Astronomers
Information for Astronomers
In Effelsberg we store a lot of auxiliary information (not the astronomical data itself!) regarding the observations in an SQL database. For example, timestamps, project id's, sky positions, frequency setup, etc. Using a tool, like
mysql-query-browser, one can easily query the database for information. (Please ask to Effelsberg staff, preferably H. Hafok or B. Winkel, on login names and passwords.)
Entries in the database include measurements since January 2009.
In the right part, several tables are listed. The most useful are
Double-clicking a table brings up the most basic SQL query (into the query field):
SELECT * FROM effdb.obsinfo o LIMIT 0,1000
This will show the 1000 entries last added or changed. More useful is to order (
ORDER BY) the results (e.g., by date and time) for example to show the 1000 most recent entries:
SELECT * FROM effdb.obsinfo o ORDER BY obstimestamp DESC LIMIT 0,1000
It is very easy to filter the information using the
SELECT * FROM effdb.obsinfo o WHERE projectid='17-10' SELECT * FROM effdb.obsinfo o WHERE observer LIKE '%kraus%' OR observer LIKE '%AK%' # the '%' works AS wild-card
Joining means to link entries from different tables with each other. One differentiates between inner and outer joins (for more information see here).
SELECT * FROM effdb.receiver_calibration c INNER JOIN effdb.receiver_parameters r ON c.frontend=r.frontend AND c.baseband=r.baseband AND c.subband=r.subband
This example will perform an inner join of the receiver parameters and receiver calibration tables forming a large table containing a lot of information on the receivers (just compare with a simple query on the individual tables…).
Joins can be very useful. In the following we will show increasingly complex queries to associate calibration information to the obsinfo database. Lets start simple
SELECT object,scan,observer,febe FROM effdb.obsinfo o WHERE projectid='17-10' AND subsnum=1 ORDER BY obstimestamp DESC LIMIT 0,1000
To do this, we perform a join like this
SELECT o.object,o.scan,o.febe,o.restfreq,c.frequency,c.baseband,c.tcal,c.tsys FROM effdb.obsinfo o INNER JOIN effdb.receiver_calibration c ON locate(c.frontend,o.febe)>0 WHERE o.projectid='17-10' AND o.subsnum=1 ORDER BY o.obstimestamp DESC,c.frequency ,c.baseband ASC LIMIT 0,1000
The basic syntax is the same as before. The key to associate the rows from obsinfo with receiver_calibration is the frontend name (e.g., "P13mm"). Unfortunately, in the obsinfo database there is only a
febe keyword, which contains the frontend-backend pair (e.g., "P13mm-XFFTS"). To produce a match, we just perform a substring search, using the
LOCATE(s1,s2) function, which will either return 0 (if s1 is not in s2) or the first occurrence of s1 in s2. The result looks promising.
However, for each row in obsinfo we might have several entries in receiver_calibration (for different polarization channels and frequencies).
To find the row which is closest in frequency, we could let compute MySQL the difference in frequency between both tables:
SELECT o.object,o.scan,o.febe,o.restfreq,c.frequency,abs(o.restfreq/1.e6-c.frequency) AS diff,c.baseband, c.tcal,c.tsys FROM effdb.obsinfo o INNER JOIN effdb.receiver_calibration c ON locate(c.frontend,o.febe)>0 WHERE o.projectid='17-10' AND o.subsnum=1 ORDER BY o.obstimestamp DESC,c.frequency ,c.baseband ASC LIMIT 0,1000
It would be also possible to just show the results with the smallest frequency "diff" values, but this would require nested queries, which we omit here for now.
It is very easy to save the results into a (csv-)file for further use (file → export resultset → csv). An example:
SELECT lst,azim FROM effdb.obsinfo o WHERE lst>0 LIMIT 0,100000
Find all spectroscopic ON-OFFs toward a known calibrator.
SELECT object,projectid,observer,scan,obstimestamp,azim,elev,febe,feversion, bandwidth,restfreq FROM effdb.obsinfo o WHERE scantype='ONOFF' AND scanmode='SAMPLE' AND object REGEXP '(3C48|3C123|3C161|3C147|3C286|3C295|3C196|3C138|NGC7027|W3OH)' AND obstimestamp>='2011-05' ORDER BY obstimestamp DESC
The following shows an example Python script which queries the database to show the exposure time of every W3MAIN observation since Feb, 2012.
import MySQLdb import sys # please ask our staff members for account details user="" passwd="" def connectToDB(host = "127.0.0.1",port=3307,user = "" ,passwd = "",db = "effdb"): connected=False conn=None try: conn = MySQLdb.connect (host = host,port=port,user = user,passwd = passwd,db = db) print "successfully connected to db" connected=True except MySQLdb.Error, e: print "Error %d: %s" % (e.args, e.args) connected=False return conn,connected # conn,connected=connectToDB(user=user,passwd=passwd) if not connected: print "could not connect to database" sys.exit(1) # squery="select obstimestamp,sum(24.*3600.*(sublastmjd-subfirstmjd)) from effdb.obsinfo where object like 'W3MAIN' and obstimestamp>'2012-01' group by obstimestamp" print squery cursor = conn.cursor () cursor.execute (squery) sqlResult = cursor.fetchall () #print self.row if len(sqlResult)>0: for a in range(len(sqlResult)): print sqlResult[a],":",int(sqlResult[a]+0.5),"seconds" else: print "no entries found"
2012-01-04T15:34:15 : 116 seconds 2012-01-04T23:01:49 : 116 seconds 2012-01-04T23:05:59 : 116 seconds 2012-01-04T23:12:07 : 116 seconds 2012-01-04T23:15:09 : 118 seconds . . . 2012-02-06T21:05:10 : 116 seconds 2012-02-06T21:10:34 : 112 seconds 2012-02-08T15:51:34 : 116 seconds 2012-02-08T15:59:26 : 116 seconds
Note, that in order to connect to the server a local tunnel to the MPIfR SQL Server has to be created. Furthermore, the "MySQLdb" Python Module needs to be installed. Please ask local staff for help, or login to "observer5" (where everything is already installed) to run the scripts.