Thursday, October 11, 2012

SQLITE: useful query: matching two tables by ra, dec

I wanted to cross-match two tables, one of which had IDs, another -- only ra, dec coordinates. THe second one had extinction for all SDSS bands, and it's a pain to go and do a SDSS query for a list of objects again. So:

SELECT m.CALIFA_ID,  round(m.ra, 5), round(m.dec, 5), round(s.ra, 5), round(s.dec, 5)
 from mothersample as m, sdss_match as s where (round(m.ra, 5) = round(s.ra, 5) 
and round(m.dec, 5) = round(s.dec, 5)) order by CALIFA_ID asc
or rather, the actual useful query:

SELECT m.rowid, m.CALIFA_ID,  round(m.ra, 5), round(m.dec, 5), s.petroMagErr_u, 
 s.petroMagErr_g, s.petroMagErr_r, s.petroMagErr_i, s.petroMagErr_z, s.extinction_u, 
s.extinction_g, s.extinction_r, s.extinction_i, s.extinction_z

from mothersample as m, sdss_match as s where (round(m.ra, 5) = round(s.ra, 5) and 
round(m.dec, 5) = round(s.dec, 5)) order by m.rowid 

No comments:

Post a Comment