Importing large .csv files via sqlite3 command line interface

If they are too big to import via the GUI manager, that's one way to go (I created a database of emission line data):

CREATE TABLE "elines" ("CALIFA_id" text,"morph" text,"bar"
text,"radius" float,"xpos" float,"ypos" float,"Hbeta_flux"
float,"Hbeta_eflux_float" float,"Hgamma_flux_float"
float,"Hgamma_eflux" float,"Hdelta_flux" float,"Hdelta_eflux"
float,"Halpha_flux" float,"Halpha_eflux" float,"OIII5007_flux"
float,"OIII5007_eflux" float,"OI6300_flux" float,"OI6300_eflux"
float,"NII6584_flux" float,"NII6584_eflux" float,"SII6717_flux"
float,"SII6717_eflux" float,"SII6730_flux" float,"SII6730_eflux"
float,"vel" float,"evel" float,"disp" float,"edisp" float,"Ha_cont"
float)

.separator ","

.nullvalue -999

.import elines.csv elines

SDSS: a query for clean photometry galaxies with redshifts

SELECT g.objid, g.ra, g.dec, g.petroMag_u, g.petroMag_g, g.petromag_r, g.petromag_i, g.petromag_z, s.z, g.petroR50_r, g.petroR90_r, g.rho, g.isoA_r, g.isoB_r, g.isophi_r,
dbo.fPhotoFlagsN(flags) as flags into mydb.galaxies from Galaxy as G
   JOIN SpecObj as S ON G.objId=S.bestObjId 
WHERE ((flags & 0x10000000) != 0)       
AND ((flags & 0x8100000c00a0) = 0)     
       
       
       
AND (((flags & 0x400000000000) = 0) or (psfmagerr_g <= 0.2))
       
       
AND (((flags & 0x100000000000) = 0) or (flags & 0x1000) = 0)

SQLite: one more query

in case my computer freezes again: SELECT r.califa_id, r.el_mag, 0.396*r.el_hlma, g.el_hlma, f.sum, g.gc_sky - r.gc_sky FROM r_tot as r, gc_flags as f, gc as g where 0.396*r.el_hlma > 40 and r.califa_id = f.califa_id and r.califa_id = g.califa_id

SQLite: some average values

For copying and pasting:SELECT avg(zpt), avg(ext_coeff), avg(airmass) FROM u_tsfieldParams
select z.califa_id, z.z_mag - m.petroMag_z, z.z_mag, m.petroMag_z from z_test as z, mothersample as m where m.califa_id = z.califa_id

SQLITE: query for multiple quantities from several tables

Nothing fancy, just putting it out there for reuse.

SELECT g.califa_id, g.r_mag, g.el_hlma, l.hlr, m.isoA_r, g.ba, g.flags, r.mean_sky, 
r.gc_sky  FROM gc as g, gc_r as r, lucie as l, mothersample as m where (g.califa_id 
= r.califa_id) and (g.califa_id = m.califa_id) and (g.califa_id = l.califa_id)

and (g.el_hlma > 25) order by g.el_hlma desc

A wrapper for wrapper for kcorrect

A little script I cobbled together in order to feed the data to kcorrect, and save its output (absolute ugriz magnitudes and stellar masses, in this case).

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 

NED positional query -- a script to export ra, dec from database

It's a general purpose script I quickly put together to export entries from database to csv. Now it does it the retarded way, as I have to use NED Batch Jobs system. https://github.com/astrolitterbox/growth-curve-photometry/blob/noSciPy/get_califa_values.py

SQLite: field names and leading space

Just spent a few minutes, frustrated by

sqlite3.OperationalError: no such column: el_hlma

. The column name had a leading space before it, that's why the query failed.

SDSS CASjobs -- looking for objects within a given radius of a coordinate pair

There's an inbuilt function for that:

select p.ra,p.dec,p.b,p.l,p.objID,p.run,p.rerun,p.camcol,p.field,p.obj,p.type,p.flags,p.fiberMag_r,p.petroMag_u,p.petroMag_g,p.petroMag_r,p.petroMag_i,p.petroMag_z,
       p.petroRad_r,p.petroR50_r,p.petroR90_r,p.isoA_g,p.isoB_g,p.isoA_r,p.isoB_r,p.isoPhi_r,p.specObjID into mydb.MiceB from PhotoObjAll as p,

dbo.fGetNearbyObjEq(191.5, 30.7, 1.0)

n 
WHERE p.objID = n.objID 

      and
      ( flags & (dbo.fPhotoFlags('NOPETRO') +
       dbo.fPhotoFlags('MANYPETRO')
       +dbo.fPhotoFlags('TOO_FEW_GOOD_DETECTIONS')) ) = 0

Set values to null in SQLite; table updating

Lest I forget:

UPDATE Table SET z = z/300000 where abs(z) > 2

Left outer joins with SQLITE

from here. Basically, SELECT a FROM DB1 NATURAL LEFT OUTER JOIN DB2;

SDSS axis ratio measures

I copy it verbatim from here, to have a reference ready.

The model fits yield an estimate of the axis ratio and position angle of each object, but it is useful to have model-independent measures of ellipticity. In the data released here, frames provides two further measures of ellipticity, one based on second moments, the other based on the ellipticity of a particular isophote. The model fits do correctly account for the effect of the seeing, while the methods presented here do not.

The first method measures flux-weighted second moments, defined as:

• Mxx = x^2/r^2
• Myy = y^2/r^2
• Mxy = x^y/r^2

In the case that the object's isophotes are self-similar ellipses, one can show:

• Q = Mxx - Myy = [(a-b)/(a+b)]cos2φ
• U = Mxy = [(a-b)/(a+b)]sin2φ

where a and b are the semi-major and semi-minor axes, and φ is the position angle. Q and U are Q and U in PhotoObj and are referred to as "Stokes parameters." They can be used to reconstruct the axis ratio and position angle, measured relative to row and column of the CCDs. This is equivalent to the normal definition of position angle (East of North), for the scans on the Equator. The performance of the Stokes parameters are not ideal at low S/N. For future data releases, frames will also output variants of the adaptive shape measures used in the weak lensing analysis of Fischer et al. (2000), which are closer to optimal measures of shape for small objects.

Isophotal Quantities

A second measure of ellipticity is given by measuring the ellipticity of the 25 magnitudes per square arcsecond isophote (in all bands). In detail, frames measures the radius of a particular isophote as a function of angle and Fourier expands this function. It then extracts from the coefficients the centroid (isoRowC,isoColC), major and minor axis (isoA,isoB), position angle (isoPhi), and average radius of the isophote in question (Profile). Placeholders exist in the database for the errors on each of these quantities, but they are not currently calculated. It also reports the derivative of each of these quantities with respect to isophote level, necessary to recompute these quantities if the photometric calibration changes.

Many more about those second order moments, and why they are called 'Stokes' parameters' can be found in references herein.

NED batch job form

It's amazing what contrived workarounds are constructed in public astronomical databases in order to avoid letting people see and harness the SQL underneath. Here's a setup file for NED batch jobs tool I made to work finally:

OUTPUT_FILENAME test.csv
OUTPUT_OPTION compact
COMPRESS_OPTION none
INPUT_COORDINATE_SYSTEM equatorial
OUTPUT_COORDINATE_SYSTEM equatorial
INPUT_EQUINOX J2000.0
OUTPUT_EQUINOX J2000.0
EXTENDED_NAME_SEARCH yes
OUTPUT_SORTED_BY Distance_to_search_center
REDSHIFT_VELOCITY 1000.0
SEARCH_RADIUS 0.05
BEGIN_YEAR 1900
END_YEAR 2012
IAU_STYLE S
FIND_OBJECTS_NEAR_POSITION
177.34806988d; -1.08377011d; 0.05

REDSHIFT Unconstrained
UNIT z

INCLUDE
INCLUDE ALL
 Galaxies X GClusters X Supernovae _ QSO X AbsLineSys _ GravLens _
 Radio _ Infrared _ EmissnLine X UVExcess X Xray X GammaRay _
 GPairs _ GTriples _ GGroups _
END_INCLUDE

EXCLUDE
 Galaxies _ GClusters _ Supernovae _ QSO _ AbsLineSys _ GravLens _
 Radio _ Infrared _ EmissnLine _ UVExcess _ Xray _ GammaRay _
 GPairs _ GTriples _ GGroups _
END_EXCLUDE

END_OF_DATA
END_OF_REQUESTS

HyperLeda sql field names

Somehow I keep losing this.

Left outer joins in SDSS

While I was working on our survey sample characterisation paper, I had to redo the sample selection again. There's an interesting effect in diameter limiter surveys: they should theoretically include more highly-inclined disk galaxies near the lower diameter limit, as their total brightness is projected onto a smaller area.

I was selecting two samples from the SDSS DR7: one which was only redshift-selected, and another which had no cuts in redshift, but was diameter-limited. I wanted to keep the redshift information for the diameter-limited sample (wherein not all galaxies have spectroscopic redshifts), if it was available for a particular galaxy. Hence, outer joins.

SDSS database utilities

I've digested some available scripts for SDSS into my code, namely, Min-Su Shin's code that fetches SDSS ugriz magnitudes (actually, not necessarily, any query can do) from SDSS database, and Tamas Budavari's sqlcl.py. The first one was disemboweled to be a callable function instead of standalone command-line tool. It's in github.