User Tools

Site Tools



This shows you the differences between two versions of the page.

Link to this comparison view

information_for_astronomers:user_guide:tips_database [2013/07/31 22:24] (current)
Line 1: Line 1:
 +===== Effelsberg Database (MySQL) =====
 +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. 
 +===== Basics =====
 +If one starts the ''​mysql-query-browser''​ on observer5, after logging in, a window like this will appear:\\
 +In the right part, several tables are listed. The most useful are
 +  * **obsinfo** - contains a lot of observational parameters for every subscan of every measurement (except pulsar and VLBI observations)
 +  * **receiver_parameters** - receiver parameters (frequency range, number of basebands/​feeds,​ gain curves)
 +  * **receiver_calibration** - typical calibration values (Tcal, antenna efficiency, etc.) for a number of frequencies
 +  * **receiver_versions** - is the database version of the frontend files
 +Double-clicking a table brings up the most basic SQL query (into the query field):
 +<code SQL>​SELECT * FROM effdb.obsinfo o LIMIT 0,​1000</​code>​
 +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:
 +<code SQL>​SELECT * FROM effdb.obsinfo o ORDER BY obstimestamp DESC LIMIT 0,​1000</​code>​
 +===== Filtering =====
 +It is very easy to filter the information using the ''​WHERE''​-clause.
 +<code SQL>​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</​code>​
 +===== Joins =====
 +Joining means to link entries from different tables with each other. One differentiates between //inner// and //outer// joins [[http://​​wiki/​Join_%28SQL%29|(for more information see here)]].
 +<code SQL>​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</​code>​
 +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
 +<code SQL>​SELECT object,​scan,​observer,​febe FROM effdb.obsinfo o where projectid='​17-10'​ AND subsnum=1 ​
 +   order by obstimestamp desc LIMIT 0,​1000</​code>​
 +Now it would be nice, if we had calibration information (Tcal/Tsys values) for each entry.
 +To do this, we perform a join like this
 +<code SQL>​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</​code>​
 +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:
 +<code SQL>​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</​code>​
 +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. 
 +===== Exporting data =====
 +It is very easy to save the results into a (csv-)file for further use (file -> export resultset -> csv). An example:
 +<code SQL>​SELECT lst,azim FROM effdb.obsinfo o where lst>0 LIMIT 0,​100000</​code>​
 +Plotting this does not reveal amazing scientific wisdom, yet looks nice.\\
 +===== Useful queries =====
 +Find all spectroscopic ON-OFFs toward a known calibrator.
 +<code SQL>​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 </​code>​
 +===== Using python to work with the database =====
 +The following shows an example Python script which queries the database to show the exposure time of every W3MAIN observation since Feb, 2012.
 +<code python>​import MySQLdb
 +import sys
 +# please ask our staff members for account details
 +def connectToDB(host = "​",​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[0], e.args[1])
 +        connected=False
 +    return conn,​connected
 +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][0],":",​int(sqlResult[a][1]+0.5),"​seconds"​
 +    print "no entries found"
 +<​code>​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.
information_for_astronomers/user_guide/tips_database.txt ยท Last modified: 2013/07/31 22:24 (external edit)