CREATE TABLE spectrum_smallz (specind int, survey varchar, ra_j2000 float, dec_j2000 float, 
z float, z_err float, z_q int,
sdss_mjd int, sdss_plate int, sdss_fiberID int, 
twodf_seqnum varchar, sixdf_specid int, 
uzc_zname varchar, cfa_RFN varchar,
hectospec_date varchar, hectospec_dataset varchar, hectospec_spec varchar, 
lamost_mjd int, lamost_spid int, lamost_fiberid int, lega_c_spectid varchar, 
deep2_objno int, deep3_objno int, wigglez_specfile varchar, gama_specid varchar, twodflens_target int);

COPY spectrum_bigz FROM '/tmp/speclist_bigz_psql.csv' WITH (FORMAT csv);

CREATE TABLE smallz_spec_spoint AS 
    SELECT 
        *, 
        spoint(ra_j2000*pi()/180.0,dec_j2000*pi()/180.0) AS crd2000 
    FROM         
        spectrum_smallz; 
        
VACUUM FULL smallz_spec_spoint;        
CREATE INDEX sindex ON smallz_spec_spoint using gist (crd2000);     
VACUUM ANALYZE smallz_spec_spoint;
 
CREATE VIEW hldata_z AS 
    SELECT 
        pgc, al2000/24*360 AS ra, de2000 AS dec, objname, objtype, v/299792.5 AS z, e_v/299792.5 AS z_err,
        spoint(al2000/24*360*pi()/180.0,de2000*pi()/180.0) AS crd2000,
        "type" AS gtype, bt, e_bt, logd25, logr25, pa, numtype 
    FROM                                                       
        m000;
        
SELECT  pgc,objname,objtype, ra, dec, z, z_err, crd2000,
  sellipse(crd2000, 
0.5*(pow(10, logd25) / 10.0)*(pi()/180.0/60.0),
0.5*(pow(10, logd25) / (10.0 * pow(10.0, logr25)))*
        (pi()/180.0/60.0),
pa*pi()/180.0) as gal, logd25, gtype, bt, e_bt, numtype 
INTO TABLE hlgal_z_pa
FROM hldata_z_w1pa;    

ALTER TABLE hlgal_z ADD COLUMN true_ell int;
UPDATE hlgal_z SET true_ell=1 WHERE hlgal_z.gal is not null;
UPDATE hlgal_z SET gal=sellipse(hlgal_z.crd2000,6.0/206265.0,6.0/206265.0,0) where hlgal_z.gal is null;     
            
SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.objtype INTO TABLE spec_smallz_ledamatch FROM spectrum_smallz_spoint AS a, hlgal_z AS b WHERE a.crd2000 @ b.gal AND (b.objtype='G' OR b.objtype='g' OR b.objtype='M' OR b.objtype='M2' OR b.objtype='M3' OR b.objtype='MG' OR b.objtype='MC') AND ABS(a.z-b.z)<0.0003;

EXPLAIN ANALYZE SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist FROM spectrum_spoint AS a, hlgal AS b WHERE a.crd2000 @ b.gal LIMIT 10000;

COPY (SELECT specind, survey, ra_j2000, dec_j2000, ra, dec, z, redshift, pgc FROM spec_smallz_ledamatch where pgc=5818) TO '/tmp/one_obj.csv' WITH (FORMAT csv);

SELECT * INTO full_spec FROM
(SELECT specind, survey, ra_j2000, dec_j2000, z, z_err, z_q, sdss_mjd, sdss_plate, sdss_fiberid, 
twodf_seqnum, sixdf_specid, uzc_zname, cfa_rfn, hectospec_date, hectospec_dataset, hectospec_spec, 
lamost_mjd, lamost_spid, lamost_fiberid, lega_c_spectid, deep2_objno, deep3_objno, wigglez_specfile, 
gama_specid, twodflens_target FROM highz_spec UNION SELECT (row_number() OVER(order by specind))+5598026 AS specind2, survey, ra_j2000, dec_j2000, z, z_err, 
z_q, sdss_mjd, sdss_plate, sdss_fiberid, twodf_seqnum, sixdf_specid, uzc_zname, cfa_rfn, hectospec_date, 
hectospec_dataset, hectospec_spec, lamost_mjd, lamost_spid, lamost_fiberid, lega_c_spectid, deep2_objno, 
deep3_objno, wigglez_specfile, gama_specid, twodflens_target FROM lowz_leda_noQual) AS tmp;

SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.objtype INTO TABLE full_spec_leda2 FROM full_spec_spoint AS a, hlgal2_z AS b WHERE a.crd2000 @ b.gal AND b.true_ell=1 AND ABS(a.z-b.z)<0.0003;

SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.z_err AS e_redshift, b.objtype INTO TABLE spec_smallz_ledamatch FROM spectrum_smallz_spoint AS a, hlgal_z AS b WHERE a.crd2000 @ b.gal AND (b.objtype='G' OR b.objtype='g' OR b.objtype='M' OR b.objtype='M2' OR b.objtype='M3' OR b.objtype='MG' OR b.objtype='MC') AND ABS(a.z-b.z)<2*GREATEST(a.z_err-b.z_err);


SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.z_err AS e_redshift, b.objtype INTO TABLE smallz_spec_ledamatch FROM smallz_spec_spoint AS a, hlgal_z AS b WHERE ((((z_q is null) OR (z_q >= 3 AND (survey='2dF' OR survey='6dF' OR survey='gama' OR survey='2dFLenS'))) AND a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*GREATEST(a.z_err-b.z_err)) 
OR ((z_q < 3 AND (survey='2dF' OR survey='6dF' OR survey='gama' OR survey='2dFLenS')) OR (z_q > 0 AND survey='sdss') AND a.crd2000 @ b.gal)) 
AND (b.objtype='G' OR b.objtype='g' OR b.objtype='M' OR b.objtype='M2' OR b.objtype='M3' OR b.objtype='MG' OR b.objtype='MC');


SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.z_err AS e_redshift, b.objtype INTO TABLE fullz_spec_ledamatch FROM fullz_spec_spoint AS a, hlgal_z AS b WHERE ((((z_q is null) OR (z_q >= 3 AND (survey='2dF' OR survey='6dF' OR survey='gama' OR survey='2dFLenS'))) AND a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*GREATEST(a.z_err-b.z_err)) 
OR ((z_q < 3 AND (survey='2dF' OR survey='6dF' OR survey='gama' OR survey='2dFLenS')) OR (z_q > 0 AND survey='sdss') AND a.crd2000 @ b.gal)) 
AND b.true_ell=1;

ALTER TABLE full_spec_spoint ADD COLUMN true_filter int;
UPDATE full_spec_spoint SET true_filter=1 WHERE ((z_q is null) OR (z_q >= 3 AND (survey='2dF' OR survey='6dF' OR survey='gama' OR survey='2dFLenS')));

SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.z_err AS e_redshift, b.objtype, GREATEST(ABS(a.z_err)spe, ABS(b.z_err), 0.00025) AS error INTO TABLE full_spec_leda_qualfilter FROM full_spec_spoint AS a, hlgal_z AS b WHERE ((true_filter=1 AND a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*GREATEST(ABS(a.z_err), ABS(b.z_err), 0.00025)) OR  (true_filter is null AND a.crd2000 @ b.gal)) AND b.true_ell=1;


CREATE VIEW lslga_spoint AS SELECT *, spoint(ra*pi()/180, dec*pi()/180) AS crd2000 FROM lslga;

CREATE TABLE hldata_z_w1pa AS SELECT * FROM hldata_z;
UPDATE hldata_z_w1pa SET pa=lslga.w1pa FROM lslga WHERE hldata_z_w1pa.pa is null AND hldata_z_w1pa.pgc=lslga.pgc;

SELECT  pgc,objname,objtype, ra, dec, z, z_err, crd2000,
  sellipse(crd2000, 
0.5*(pow(10, logd25) / 10.0)*(pi()/180.0/60.0),
0.5*(pow(10, logd25) / (10.0 * pow(10.0, logr25)))*
        (pi()/180.0/60.0),
pa*pi()/180.0) as gal, logd25, gtype, bt, e_bt, numtype 
INTO TABLE hlgal_z_pa
FROM hldata_z_w1pa;    

SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.z_err AS e_redshift, b.objtype, GREATEST(ABS(a.z_err), ABS(b.z_err), 0.001) AS error, b.crd2000 AS ledacrd2000 INTO TABLE full_spec_leda_qualfilter_pa FROM full_spec_spoint AS a, hlgal_z_pa AS b WHERE ((true_filter=1 AND a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*GREATEST(ABS(a.z_err), ABS(b.z_err), 0.001)) OR  (true_filter is null AND a.crd2000 @ b.gal));

SELECT a.*, b.specind AS specind2 INTO TABLE spec_no_leda_match FROM full_spec AS a LEFT OUTER JOIN full_spec_leda_qualfilter_pa AS b ON a.specind=b.specind limit 100;

SELECT a.specind, a.ra_j2000, a.dec_j2000, a.pgc, a.ra AS ra_leda, a.dec AS dec_leda, 0.5*(pow(10, b.logd25) / 10.0)*60.0 AS amaj, 1 - 1/pow(10.0, b.logr25) AS ell, b.pa INTO TABLE spec_ledasizes FROM full_spec_leda_qualfilter_pa AS a LEFT OUTER JOIN hldata_z_w1pa AS b ON a.pgc=b.pgc;



==SDSS part==

CREATE TABLE spec_noleda_sdss_size (
specind int, ra_j2000 float, dec_j2000 float, sdss_mjd int, sdss_plate int, sdss_fiberid int, ra float, dec float, objid bigint, petrorad_r float, deVAB_r float, deVPhi_r float
);

CREATE TABLE spec_noleda_nosdss_size (
specind int, ra_j2000 float, dec_j2000 float, ra float, dec float, objid bigint,
petrorad_r float, deVAB_r float, deVPhi_r float
);

COPY spec_noleda_sdss_size FROM '/tmp/spec_noleda_sdss_size_result.csv' WITH (FORMAT csv);
UPDATE spec_noleda_sdss_size SET deVAB_r = null WHERE deVAB_r<0;
UPDATE spec_noleda_sdss_size SET deVPhi_r = null WHERE ABS(deVPhi_r)>=360;

COPY spec_noleda_nosdss_size FROM '/tmp/spec_noleda_nosdss_size_result.csv' WITH (FORMAT csv);
UPDATE spec_noleda_nosdss_size SET deVAB_r = null WHERE deVAB_r<0;
UPDATE spec_noleda_nosdss_size SET deVPhi_r = null WHERE ABS(deVPhi_r)>=360;

CREATE VIEW spec_nosdss_size_sdssspoint AS 
    SELECT 
        a.*,
        spoint(a.ra*pi()/180.0,
        a.dec*pi()/180.0) AS crd2000
    FROM                                                       
        spec_noleda_nosdss_size AS a;
        
SELECT  *,
  sellipse(crd2000, 
petrorad_r/206265,
petrorad_r*deVAB_r/206265,
deVPhi_r*pi()/180.0) as gal 
INTO TABLE spec_nosdss_size_sdsssellipse
FROM spec_nosdss_size_sdssspoint; 

CREATE TABLE spec_no_leda_nosdss_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra_j2000*pi()/180.0,a.dec_j2000*pi()/180.0) AS crd2000
    FROM                                                       
        spec_noleda_nosdss AS a;
        
CREATE INDEX index2 ON spec_no_leda_nosdss_spoint using gist (crd2000);  

DELETE FROM spec_nosdss_size_sdsssellipse T1
    USING   spec_nosdss_size_sdsssellipse T2
WHERE   T1.specind > T2.specind  
    AND T1.objid  = T2.objid;     

SELECT a.*, b.ra_j2000 AS ra, b.dec_j2000 AS dec, b.crd2000 AS match_crd2000, a.crd2000 <-> b.crd2000 AS dist, b.gal, petrorad_r, deVAB_r, deVPhi_r, objid INTO TABLE spec_noleda_nosdss_matched FROM spec_no_leda_nosdss_spoint AS a, spec_nosdss_size_sdsssellipse AS b WHERE a.crd2000 @ b.gal;

DELETE FROM spec_noleda_nosdss_matched T1
    USING   spec_noleda_nosdss_matched T2
WHERE   T1.dist > T2.dist  
    AND T1.specind  = T2.specind;  
     
SELECT * INTO spec_sizes_from_sdss FROM
(SELECT specind, ra_j2000, dec_j2000, objid, ra AS ra_sdss, dec AS dec_sdss, petrorad_r, deVAB_r, deVPhi_r FROM spec_noleda_sdss_size UNION ALL SELECT specind, ra_j2000, dec_j2000, objid, ra AS ra_sdss, dec AS dec_sdss, petrorad_r, deVAB_r, deVPhi_r FROM spec_noleda_nosdss_matched) AS tmp;      

SELECT a.objid, a.ra_sdss, a.dec_sdss, a.petrorad_r, a.deVAB_r, a.deVPhi_r, spoint(a.ra_sdss*pi()/180.0,a.dec_sdss*pi()/180.0) AS sdsscrd2000, b.*, spoint(b.ra_j2000*pi()/180.0,b.dec_j2000*pi()/180.0) AS crd2000 INTO TABLE spec_sdsssizes_allcolumns FROM spec_sizes_from_sdss AS a LEFT OUTER JOIN full_spec AS b ON a.specind=b.specind;

=== LEGACY ===

CREATE TABLE legacy_sizes (
specind int, ra_j2000 float, dec_j2000 float, survey varchar, z float, ra_ls float, dec_ls float, ls_id bigint, shapedev_e1 float, shapedev_e2 float, shapedev_r float, shapeexp_e1 float, shapeexp_e2 float, shapeexp_r float, flux_r float, psfsize_r float, type varchar
);

COPY legacy_sizes FROM '/tmp/legacy_sizes.csv' WITH (FORMAT csv);

DELETE FROM legacy_sizes T1
    USING   legacy_sizes T2
WHERE   T1.specind > T2.specind  
    AND T1.ls_id  = T2.ls_id; 
    
UPDATE legacy_sizes SET shapedev_e1=null WHERE shapedev_e1=0;  
UPDATE legacy_sizes SET shapedev_e2=null WHERE shapedev_e2=0;  
UPDATE legacy_sizes SET shapedev_r=null WHERE shapedev_r=0;  
UPDATE legacy_sizes SET shapeexp_e1=null WHERE shapeexp_e1=0;  
UPDATE legacy_sizes SET shapeexp_e2=null WHERE shapeexp_e2=0;  
UPDATE legacy_sizes SET shapeexp_r=null WHERE shapeexp_r=0;   
    
CREATE TABLE legacy_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra_ls*pi()/180.0,
        a.dec_ls*pi()/180.0) AS crd2000, shapedev_r*2 AS rad, 
        0.5*ATAN2(shapedev_e2,shapedev_e1)/pi()*180 AS pa, 
        (1-SQRT(shapedev_e1^2+shapedev_e2^2))/(1+SQRT(shapedev_e1^2+shapedev_e2^2)) AS ba
    FROM                                                       
        legacy_sizes AS a;
        
UPDATE legacy_spoint SET rad=shapeexp_r*2, pa=0.5*ATAN2(shapeexp_e2,shapeexp_e1)/3.14*180, ba=(1-SQRT(shapeexp_e1^2+shapeexp_e2^2))/(1+SQRT(shapeexp_e1^2+shapeexp_e2^2)) WHERE type='EXP' OR type='REX';  
UPDATE legacy_spoint SET rad=GREATEST(shapeexp_r*2, shapedev_r*2), pa=0.5*ATAN2(shapeexp_e2,shapeexp_e1)/3.14*180, ba=(1-SQRT(shapeexp_e1^2+shapeexp_e2^2))/(1+SQRT(shapeexp_e1^2+shapeexp_e2^2)) WHERE type='COMP';    
UPDATE legacy_spoint SET rad=psfsize_r*2, ba=1, pa=0 WHERE type='PSF';        
UPDATE legacy_spoint SET ba=1 WHERE rad is not null AND ba is null; 
UPDATE legacy_spoint SET pa=0 WHERE rad is not null AND pa is null;
        
SELECT  *,
  sellipse(crd2000, 
rad/206265,
rad*ba/206265,
pa*pi()/180.0) as gal 
INTO TABLE legacy_sellipse
FROM legacy_spoint;  

CREATE TABLE for_legacy_match_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra_j2000*pi()/180.0,
        a.dec_j2000*pi()/180.0) AS crd2000
    FROM                                                       
        for_legacy_match AS a;      
 
CREATE INDEX index3 ON for_legacy_match_spoint using gist (crd2000);  

 
SELECT a.*, b.ra_ls, b.dec_ls, b.ls_id, b.shapedev_e1, b.shapedev_e2, b.shapedev_r, b.shapeexp_e1, b.shapeexp_e2, b.shapeexp_r, b.pa, b.rad, b.ba, b.flux_r, b.psfsize_r, b.type, b.crd2000 AS match_crd2000, a.crd2000 <-> b.crd2000 AS dist, b.gal INTO TABLE spec_legacysizes FROM for_legacy_match_spoint AS a, legacy_sellipse AS b WHERE a.crd2000 @ b.gal;
        
DELETE FROM spec_legacysizes T1
    USING   spec_legacysizes T2
WHERE   (T1.dist > T2.dist  
    AND T1.specind  = T2.specind) OR (T1.dist = T2.dist  
    AND T1.specind  = T2.specind AND T1.ls_id != T2.ls_id);
    
SELECT specind, COUNT(specind) FROM spec_legacysizes GROUP BY specind ORDER BY COUNT(specind) DESC limit 100;              

SELECT a.*, b.specind AS specind4 INTO TABLE nomatch FROM spec_nosdss_match AS a LEFT OUTER JOIN spec_legacysizes AS b ON a.specind=b.specind;

SELECT * INTO TABLE forvhs FROM nomatch WHERE specind2 is null AND specind3 is null AND specind4 is null;

\COPY (SELECT specind, ra_j2000, dec_j2000, z, survey FROM forvhs) TO /tmp/forvhs.csv WITH CSV HEADER;

== AGAIN SDSS ==

CREATE TABLE badobjid_spec_size (
specind int, ra_j2000 float, dec_j2000 float, ra float, dec float, objid bigint,
petrorad_r float, deVAB_r float, deVPhi_r float
);

COPY badobjid_spec_size FROM '/tmp/badobj_sdsssize.csv' WITH (FORMAT csv);
UPDATE badobjid_spec_size SET deVAB_r = null WHERE deVAB_r<0;
UPDATE badobjid_spec_size SET deVPhi_r = null WHERE ABS(deVPhi_r)>=360;

CREATE VIEW badobjid_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra*pi()/180.0,
        a.dec*pi()/180.0) AS crd2000
    FROM                                                       
        badobjid_spec_size AS a;

SELECT  *,
  sellipse(crd2000, 
petrorad_r/206265,
petrorad_r*deVAB_r/206265,
deVPhi_r*pi()/180.0) as gal 
INTO TABLE badobjid_sellipse
FROM badobjid_spoint; 

DELETE FROM badobjid_sellipse T1
    USING   badobjid_sellipse T2
WHERE   T1.specind > T2.specind  
    AND T1.objid  = T2.objid;    
    
CREATE TABLE nolegacy_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra_j2000*pi()/180.0,a.dec_j2000*pi()/180.0) AS crd2000
    FROM                                                       
        forvhs AS a;

CREATE INDEX index_nolegacy ON nolegacy_spoint using gist (crd2000);   

SELECT a.*, b.ra_j2000 AS ra, b.dec_j2000 AS dec, b.crd2000 AS match_crd2000, a.crd2000 <-> b.crd2000 AS dist, b.gal, petrorad_r, deVAB_r, deVPhi_r, objid INTO TABLE badobjid_sizes FROM nolegacy_spoint AS a, badobjid_sellipse AS b WHERE a.crd2000 @ b.gal;

DELETE FROM badobjid_sizes T1
    USING   badobjid_sizes T2
WHERE   T1.dist > T2.dist  
    AND T1.specind  = T2.specind;  
   
\COPY (SELECT * FROM badobjid_sizes WHERE dec_j2000>0) TO /tmp/badobjid.csv WITH CSV HEADER;   

==== VHS =====

CREATE TABLE vhs_sizes (
specind int, ra_j2000 float, dec_j2000 float, z float, survey varchar, distance float, ra_vhs float, dec_vhs float, objid bigint, petrorad float, ell float, pa float
);

COPY vhs_sizes FROM '/tmp/vhs_sizes.csv' WITH (FORMAT csv);

DELETE FROM vhs_sizes WHERE objid=0; 
SELECT row_number() OVER() AS line_num, * INTO TABLE vhs_sizes_idx fROM vhs_sizes;


DELETE FROM vhs_sizes_idx T1
    USING   vhs_sizes_idx T2
WHERE   T1.line_num > T2.line_num 
    AND T1.objid  = T2.objid; 
    
SELECT objid, COUNT(objid) FROM vhs_sellipse GROUP BY objid ORDER BY COUNT(objid) DESC limit 100;      
    
CREATE TABLE vhs_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra_vhs*pi()/180.0,
        a.dec_vhs*pi()/180.0) AS crd2000
    FROM                                                       
        vhs_sizes_idx AS a;
        
#petrorad in pixels, pixelsize 0.4"        
        
SELECT  *,
  sellipse(crd2000, 
petrorad*0.4*1.5/206265,     
petrorad*0.4*1.5*(1-ell)/206265,
(90-pa)*pi()/180.0) as gal 
INTO TABLE vhs_sellipse
FROM vhs_spoint;  
CREATE TABLE forvhs_spoint AS 
    SELECT 
        a.*,
        spoint(a.ra_j2000*pi()/180.0,
        a.dec_j2000*pi()/180.0) AS crd2000
    FROM                                                       
        forvhs AS a;      
 
CREATE INDEX index_vhs ON forvhs_spoint using gist (crd2000);  
 
SELECT a.*, b.ra_vhs, b.dec_vhs, b.distance, b.objid, b.petrorad, b.ell, b.pa, b.petrorad*0.4*1.5 AS rad, b.crd2000 AS match_crd2000, a.crd2000 <-> b.crd2000 AS dist, b.gal INTO TABLE spec_vhssizes FROM forvhs_spoint AS a, vhs_sellipse AS b WHERE a.crd2000 @ b.gal;
        
DELETE FROM spec_vhssizes T1
    USING   spec_vhssizes T2
WHERE  T1.dist > T2.dist  
    AND T1.specind  = T2.specind;    
    
SELECT a.*, b.specind AS specind5 INTO TABLE nomatch_vhs FROM nomatch AS a LEFT OUTER JOIN spec_vhssizes AS b ON a.specind=b.specind;

SELECT * INTO vhs_notfound FROM nomatch_vhs WHERE specind2 is null AND specind3 is null AND specind4 is null AND specind5 is null;

\COPY (SELECT * FROM vhs_notfound) TO /tmp/vhs_notfound.csv WITH CSV HEADER;   

SELECT a.*, b.specind AS specind5 INTO TABLE nomatch_vhs FROM forvhs AS a LEFT OUTER JOIN vhs_sizes AS b ON a.specind=b.specind;

SELECT * INTO vhs_notfound FROM nomatch_vhs WHERE specind5 is null;

\COPY (SELECT * FROM vhs_notfound) TO /tmp/vhs_notfound.csv WITH CSV HEADER;   


SELECT * INTO TABLE stripe82 FROM full_spec_spoint WHERE crd2000 @ sbox(spoint'(300d, -1.26d)', spoint'(60d, 1.26d)');
\COPY (SELECT * FROM stripe82) TO /data1/stripe82/spec_stripe82.csv WITH CSV HEADER;   


copy wise FROM PROGRAM 'head -n 10 /tmp/stripe82_wise.csv' WITH HEADER;

\COPY (SELECT specind, survey, ra_j2000, dec_j2000, z, z_err, z_q, lamost_obsid, lamost_planid, lamost_obsdate, lamost_lmjd, lamost_spid, lamost_fiberid FROM full_spec WHERE survey='lamost') TO /data1/rcsed_v2/redshift/lamost_spec_list_fullz.csv WITH CSV HEADER; 


===== NSC ======
CREATE TABLE nsc_raw_sizes (
specind int, ra_j2000 float, dec_j2000 float, z float, survey varchar, id float, class_star float, ra float, dec float, fwhm float, ellipticity float, theta float);

\COPY nsc_raw_sizes FROM '/data1/rcsed_v2/postgres/nomatch_nsc_sizes.csv' WITH csv header;

ALTER TABLE nsc_raw_sizes ADD COLUMN crd2000 spoint;
UPDATE nsc_raw_sizes SET crd2000=spoint(ra*pi()/180.0, dec*pi()/180.0);

ALTER TABLE nsc_raw_sizes ADD COLUMN gal sellipse;
UPDATE nsc_raw_sizes SET gal=sellipse(crd2000, fwhm*2/206265, fwhm*2*(1-ellipticity)/206265, theta*pi()/180.0);

ALTER TABLE noleda_nosdss_nolegacy ADD COLUMN crd2000 spoint;
UPDATE noleda_nosdss_nolegacy SET crd2000=spoint(ra_j2000*pi()/180.0, dec_j2000*pi()/180.0);

CREATE INDEX index4 ON noleda_nosdss_nolegacy using gist (crd2000);  

DELETE FROM nsc_raw_sizes T1
    USING   nsc_raw_sizes T2
WHERE   (T1.specind > T2.specind  
    AND T1.id = T2.id);

SELECT a.*, b.ra AS ra_nsc, b.dec AS dec_nsc, b.id, b.class_star, b.fwhm, b.ellipticity, b.theta, b.gal, b.crd2000 AS match_crd2000, a.crd2000 <-> b.crd2000 AS dist, b.gal AS gal_nsc INTO TABLE spec_nscsizes FROM noleda_nosdss_nolegacy AS a, nsc_raw_sizes AS b WHERE a.crd2000 @ b.gal;

DELETE FROM spec_nscsizes T1
    USING   spec_nscsizes T2
WHERE   (T1.specind = T2.specind  
    AND T1.dist > T2.dist);

SELECT a.*, b.specind AS specind5 INTO TABLE noleda_nosdss_nolegacy_nonsc FROM noleda_nosdss_nolegacy AS a LEFT OUTER JOIN spec_nscsizes AS b ON a.specind=b.specind;

DELETE FROM noleda_nosdss_nolegacy_nonsc WHERE specind5 is not null;

    
\COPY (SELECT * FROM noleda_nosdss_nolegacy_nonsc) TO /data1/rcsed_v2/postgres/for_vst.csv WITH CSV HEADER;     

===== VST ======

CREATE TABLE vst_raw_sizes (
specind int, ra_j2000 float, dec_j2000 float, SrcID bigint, ra_vst float, dec_vst float, Uamaj float, Uell float, UPA float, Gamaj float, Gell float, GPA float, Ramaj float, Rell float, RPA float, Iamaj float, Iell float, IPA float, Zamaj float, Zell float, ZPA float);

\COPY vst_raw_sizes FROM '/data1/rcsed_v2/postgres/vst_sizes.csv' WITH csv header;

ALTER TABLE vst_raw_sizes ADD COLUMN crd2000 spoint;
UPDATE vst_raw_sizes SET crd2000=spoint(ra_vst*pi()/180.0, dec_vst*pi()/180.0);

ALTER TABLE vst_raw_sizes ADD COLUMN amaj float;
UPDATE vst_raw_sizes SET amaj=Ramaj;
UPDATE vst_raw_sizes SET amaj=Gamaj WHERE amaj is null;
UPDATE vst_raw_sizes SET amaj=Iamaj WHERE amaj is null;
UPDATE vst_raw_sizes SET amaj=Uamaj WHERE amaj is null;
UPDATE vst_raw_sizes SET amaj=Zamaj WHERE amaj is null;
DELETE FROM vst_raw_sizes WHERE amaj is null;

ALTER TABLE vst_raw_sizes ADD COLUMN ell float;
UPDATE vst_raw_sizes SET ell=Rell;
UPDATE vst_raw_sizes SET ell=Gell WHERE ell is null;
UPDATE vst_raw_sizes SET ell=Iell WHERE ell is null;
UPDATE vst_raw_sizes SET ell=Uell WHERE ell is null;
UPDATE vst_raw_sizes SET ell=Zell WHERE ell is null;
DELETE FROM vst_raw_sizes WHERE ell is null;

ALTER TABLE vst_raw_sizes ADD COLUMN PA float;
UPDATE vst_raw_sizes SET PA=RPA;
UPDATE vst_raw_sizes SET PA=GPA WHERE PA is null;
UPDATE vst_raw_sizes SET PA=IPA WHERE PA is null;
UPDATE vst_raw_sizes SET PA=UPA WHERE PA is null;
UPDATE vst_raw_sizes SET PA=ZPA WHERE PA is null;
DELETE FROM vst_raw_sizes WHERE PA is null;

ALTER TABLE vst_raw_sizes ADD COLUMN gal sellipse;
UPDATE vst_raw_sizes SET gal=sellipse(crd2000, amaj*2/206265, amaj*2*(1-ell)/206265, PA*pi()/180.0);

CREATE INDEX index5 ON noleda_nosdss_nolegacy_nonsc using gist (crd2000);  

DELETE FROM vst_raw_sizes T1
    USING   vst_raw_sizes T2
WHERE   (T1.specind > T2.specind  
    AND T1.srcid = T2.srcid);

SELECT a.*, b.srcid, b.ra_vst, b.dec_vst, b.amaj, b.ell, b.PA, b.gal, b.crd2000 AS match_crd2000, a.crd2000 <-> b.crd2000 AS dist, b.gal AS gal_vst INTO TABLE spec_vstsizes FROM noleda_nosdss_nolegacy_nonsc AS a, vst_raw_sizes AS b WHERE a.crd2000 @ b.gal;

DELETE FROM spec_vstsizes T1
    USING   spec_vstsizes T2
WHERE   (T1.specind = T2.specind  
    AND T1.dist > T2.dist);

==== COMBINE FULL SIZE TABLE ====

CREATE TABLE size_table (objid_spec bigint, ra_j2000 float, dec_j2000 float, size_survey varchar, objid bigint, ra_obj float, dec_obj float, amaj float, ell float, pa float, objid_nsc float);

INSERT INTO size_table (objid_spec, ra_j2000, dec_j2000, objid, ra_obj, dec_obj, amaj, ell, pa) SELECT * FROM spec_ledasizes;
UPDATE size_table SET size_survey = 'hyperleda';

INSERT INTO size_table (objid_spec, ra_j2000, dec_j2000, objid, ra_obj, dec_obj, amaj, ell, pa) SELECT specind, ra_j2000, dec_j2000, objid, ra_sdss, dec_sdss, petrorad_r, 1-deVAB_r, deVPhi_r FROM spec_sdsssizes_allcolumns;
UPDATE size_table SET size_survey = 'sdss' WHERE size_survey is null;

INSERT INTO size_table (objid_spec, ra_j2000, dec_j2000, objid, ra_obj, dec_obj, amaj, ell, pa) SELECT specind, ra_j2000, dec_j2000, ls_id, ra_ls, dec_ls, rad, 1-ba, pa FROM spec_legacysizes;
UPDATE size_table SET size_survey = 'legacy' WHERE size_survey is null;

INSERT INTO size_table (objid_spec, ra_j2000, dec_j2000, objid_nsc, ra_obj, dec_obj, amaj, ell, pa) SELECT specind, ra_j2000, dec_j2000, id, ra_nsc, dec_nsc, fwhm*2, ellipticity, theta FROM spec_nscsizes;
UPDATE size_table SET size_survey = 'nsc' WHERE size_survey is null;

INSERT INTO size_table (objid_spec, ra_j2000, dec_j2000, objid, ra_obj, dec_obj, amaj, ell, pa) SELECT specind, ra_j2000, dec_j2000, srcid, ra_vst, dec_vst, amaj, ell, pa FROM spec_vstsizes;
UPDATE size_table SET size_survey = 'vst' WHERE size_survey is null;

SELECT a.specind, a.ra_j2000, a.dec_j2000, b.specind AS specind2 INTO TABLE no_any_match FROM noleda_nosdss_nolegacy_nonsc AS a LEFT JOIN spec_vstsizes AS b ON a.specind=b.specind;

DELETE FROM no_any_match WHERE specind2 is not null;
INSERT INTO size_table (objid_spec, ra_j2000, dec_j2000) SELECT specind, ra_j2000, dec_j2000 FROM no_any_match;
UPDATE size_table SET amaj = 3 WHERE amaj is null;
UPDATE size_table SET ell = 0 WHERE ell is null;
UPDATE size_table SET pa = 0 WHERE pa is null;


