CREATE TABLE fast_lowz (fast_lowz_ind int, ra_j2000 float, dec_j2000 float, date_fast varchar,
dataset_fast varchar, spec_fast varchar, z float);
\COPY fast_lowz FROM /data1/rcsed_v2/postgres/fast_lowz.csv WITH CSV HEADER;

ALTER TABLE fast_lowz ADD COLUMN crd2000 spoint;
UPDATE fast_lowz SET crd2000=spoint(ra_j2000*pi()/180.0,dec_j2000*pi()/180.0);
VACUUM FULL fast_lowz;        
CREATE INDEX s_index_fast ON fast_lowz using gist (crd2000);
VACUUM ANALYZE fast_lowz; 

SELECT a.*, b.pgc, b.ra, b.dec, a.crd2000 <-> b.crd2000 AS dist, b.z AS redshift, b.objtype 
INTO TABLE fast_lowz_leda FROM fast_lowz AS a, hlgal_z_pa AS b WHERE (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 a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*0.0005;

DELETE FROM fast_lowz_leda T1
    USING   fast_lowz_leda T2
WHERE   T1.dist > T2.dist  
    AND T1.fast_lowz_ind  = T2.fast_lowz_ind; 
    
INSERT INTO lowz_leda_noQual (specind, ra_j2000, dec_j2000, date_fast, dataset_fast, spec_fast, z) SELECT fast_lowz_ind+10000000, ra_j2000, dec_j2000, date_fast, dataset_fast, spec_fast, z FROM fast_lowz_leda;

UPDATE lowz_leda_noQual SET survey='fast' WHERE survey is null;

CREATE TABLE highz_spec_2 (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_obsid int, lamost_planid varchar, lamost_obsdate varchar, lamost_lmjd 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, fast_date varchar, fast_dataset varchar, fast_spec varchar);

\COPY highz_spec_2 FROM /data1/rcsed_v2/postgres/speclist_highz_fastadded.csv WITH CSV HEADER;

SELECT * INTO full_spec_2 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_obsid, lamost_planid, lamost_obsdate, lamost_lmjd, lamost_spid,
lamost_fiberid, lega_c_spectid, deep2_objno, deep3_objno, wigglez_specfile, 
gama_specid, twodflens_target, fast_date, fast_dataset, fast_spec  FROM highz_spec_2 UNION SELECT (row_number() OVER(order by specind))+5598026, 
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_obsid, lamost_planid, lamost_obsdate, lamost_lmjd, lamost_spid,
lamost_fiberid, lega_c_spectid, deep2_objno, 
deep3_objno, wigglez_specfile, gama_specid, twodflens_target, date_fast, dataset_fast, spec_fast  FROM lowz_leda_noQual) AS tmp;

DELETE FROM full_spec_2 WHERE z<-9;

ALTER TABLE full_spec_2 ADD COLUMN crd2000 spoint;
UPDATE full_spec_2 SET crd2000=spoint(ra_j2000*pi()/180.0,dec_j2000*pi()/180.0);
VACUUM FULL full_spec_2;        
CREATE INDEX s_index_2 ON full_spec_2 using gist (crd2000);
VACUUM ANALYZE full_spec_2; 

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, b.crd2000 AS ledacrd2000 INTO TABLE fast_leda_sizes FROM full_spec_2 AS a, hlgal_z_pa AS b WHERE a.crd2000 @ b.gal AND ABS(a.z-b.z)<2*0.0005 AND true_ell=1; 

SELECT a.r2id_spec, a.ra_j2000, a.dec_j2000, a.z, a.z_err, a.z_q, a.fast_date, a.fast_dataset, a.fast_spec, b.r2id_spec AS r2id_spec2 INTO TABLE fast_noleda FROM full_spec_2 AS a LEFT JOIN fast_leda_sizes AS b ON a.r2id_spec=b.r2id_spec WHERE a.survey='fast';
DELETE FROM fast_noleda WHERE r2id_spec2 is not null;
\COPY (SELECT * FROM fast_noleda) TO /data1/rcsed_v2/postgres/fast_noleda.csv WITH CSV HEADER;

=====================================================

CREATE TABLE fast_sdss_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 fast_sdss_size FROM '/data1/rcsed_v2/postgres/fast_size_sdss.csv' WITH CSV HEADER;
UPDATE fast_sdss_size SET deVAB_r = null WHERE deVAB_r<0;
UPDATE fast_sdss_size SET deVPhi_r = null WHERE ABS(deVPhi_r)>=360;
ALTER TABLE fast_sdss_size ADD COLUMN crd2000 spoint;
UPDATE fast_sdss_size SET crd2000=spoint(ra*pi()/180.0,dec*pi()/180.0);
ALTER TABLE fast_sdss_size ADD COLUMN gal sellipse;
UPDATE fast_sdss_size SET gal=sellipse(crd2000, petrorad_r/206265, petrorad_r*deVAB_r/206265,
deVPhi_r*pi()/180.0);
DELETE FROM fast_sdss_size T1
    USING   fast_sdss_size T2
WHERE   T1.specind > T2.specind  
    AND T1.objid  = T2.objid;

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

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 fast_sdss_matched FROM fast_noleda AS a, fast_sdss_size AS b WHERE a.crd2000 @ b.gal;
DELETE FROM fast_sdss_matched T1
    USING   fast_sdss_matched T2
WHERE   T1.dist > T2.dist  
    AND T1.r2id_spec = T2.r2id_spec;

SELECT a.r2id_spec, a.ra_j2000, a.dec_j2000, a.z, a.z_err, a.z_q, b.r2id_spec AS r2id_spec2 INTO TABLE fast_noleda2 FROM fast_noleda AS a LEFT JOIN fast_sdss_matched AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM fast_noleda2 WHERE r2id_spec2 is not null;

=================================================

CREATE TABLE fast_legacy_sizes (
specind int, ra_j2000 float, dec_j2000 float, 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 fast_legacy_sizes FROM '/data1/rcsed_v2/postgres/fast_legacy.csv' WITH CSV HEADER;
DELETE FROM fast_legacy_sizes T1
    USING   fast_legacy_sizes T2
WHERE   T1.specind > T2.specind  
    AND T1.ls_id  = T2.ls_id; 

UPDATE fast_legacy_sizes SET shapedev_e1=null WHERE shapedev_e1=0;  
UPDATE fast_legacy_sizes SET shapedev_e2=null WHERE shapedev_e2=0;  
UPDATE fast_legacy_sizes SET shapedev_r=null WHERE shapedev_r=0;  
UPDATE fast_legacy_sizes SET shapeexp_e1=null WHERE shapeexp_e1=0;  
UPDATE fast_legacy_sizes SET shapeexp_e2=null WHERE shapeexp_e2=0;  
UPDATE fast_legacy_sizes SET shapeexp_r=null WHERE shapeexp_r=0;     
    
ALTER TABLE fast_legacy_sizes ADD COLUMN crd2000 spoint;
UPDATE fast_legacy_sizes SET crd2000=spoint(ra_ls*pi()/180.0,
        dec_ls*pi()/180.0);
ALTER TABLE fast_legacy_sizes ADD COLUMN rad float;
UPDATE fast_legacy_sizes SET rad=shapedev_r*2;     
ALTER TABLE fast_legacy_sizes ADD COLUMN pa float;
UPDATE fast_legacy_sizes SET pa=0.5*ATAN2(shapedev_e2,shapedev_e1)/pi()*180;  
ALTER TABLE fast_legacy_sizes ADD COLUMN ba float;
UPDATE fast_legacy_sizes SET ba=(1-SQRT(shapedev_e1^2+shapedev_e2^2))/(1+SQRT(shapedev_e1^2+shapedev_e2^2));                   

UPDATE fast_legacy_sizes 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 fast_legacy_sizes 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 fast_legacy_sizes SET rad=psfsize_r*2, ba=1, pa=0 WHERE type='PSF';        
UPDATE fast_legacy_sizes SET ba=1 WHERE rad is not null AND ba is null; 
UPDATE fast_legacy_sizes SET pa=0 WHERE rad is not null AND pa is null;

ALTER TABLE fast_legacy_sizes ADD COLUMN gal sellipse;
UPDATE fast_legacy_sizes SET gal=sellipse(crd2000, 
rad/206265,
rad*ba/206265,
pa*pi()/180.0);

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

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 fast_legacy_match FROM fast_noleda2 AS a, fast_legacy_sizes AS b WHERE a.crd2000 @ b.gal;

DELETE FROM fast_legacy_match T1
    USING   fast_legacy_match T2
WHERE   (T1.dist > T2.dist  
    AND T1.r2id_spec  = T2.r2id_spec) OR (T1.dist = T2.dist  
    AND T1.r2id_spec  = T2.r2id_spec AND T1.ls_id != T2.ls_id);
    
SELECT a.r2id_spec, a.ra_j2000, a.dec_j2000, a.z, a.z_err, a.z_q, b.r2id_spec AS r2id_spec2 INTO TABLE fast_noleda3 FROM fast_noleda2 AS a LEFT JOIN fast_legacy_match AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM fast_noleda3 WHERE r2id_spec2 is not null;

======================================================================

CREATE TABLE fast_nsc_sizes (
specind int, ra_j2000 float, dec_j2000 float, z float, id float, class_star float, ra float, dec float, fwhm float, ellipticity float, theta float);
COPY fast_nsc_sizes FROM '/data1/rcsed_v2/postgres/fast_nsc.csv' WITH CSV HEADER;

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

ALTER TABLE fast_nsc_sizes ADD COLUMN gal sellipse;
UPDATE fast_nsc_sizes SET gal=sellipse(crd2000, fwhm*2/206265, fwhm*2*(1-ellipticity)/206265, theta*pi()/180.0);
DELETE FROM fast_nsc_sizes T1
    USING   fast_nsc_sizes T2
WHERE   (T1.specind > T2.specind  
    AND T1.id = T2.id);

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

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 fast_nsc_match FROM fast_noleda3 AS a, fast_nsc_sizes AS b WHERE a.crd2000 @ b.gal;

DELETE FROM fast_nsc_match T1
    USING   fast_nsc_match T2
WHERE   (T1.r2id_spec = T2.r2id_spec  
    AND T1.dist > T2.dist);
    
SELECT a.r2id_spec, a.ra_j2000, a.dec_j2000, a.z, a.z_err, a.z_q, b.r2id_spec AS r2id_spec2 INTO TABLE fast_noleda4 FROM fast_noleda3 AS a LEFT JOIN fast_nsc_match AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM fast_noleda4 WHERE r2id_spec2 is not null;

===========================================================


CREATE TABLE fast_vst_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 fast_vst_sizes FROM '/data1/rcsed_v2/postgres/fast_size_vst.csv' WITH CSV HEADER;

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

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

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

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

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


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

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

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 fast_vst_match FROM fast_noleda4 AS a, fast_vst_sizes AS b WHERE a.crd2000 @ b.gal;

DELETE FROM fast_vst_match T1
    USING   fast_vst_match T2
WHERE   (T1.r2id_spec = T2.r2id_spec  
    AND T1.dist > T2.dist);

SELECT a.r2id_spec, a.ra_j2000, a.dec_j2000, a.z, a.z_err, a.z_q, b.r2id_spec AS r2id_spec2 INTO TABLE fast_nomatch FROM fast_noleda4 AS a LEFT JOIN fast_vst_match AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM fast_nomatch WHERE r2id_spec2 is not null;

==================================================

DELETE FROM fast_leda_sizes T1
    USING   fast_leda_sizes T2
WHERE   T1.dist > T2.dist  
    AND T1.r2id_spec  = T2.r2id_spec;

SELECT a.r2id_spec, 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 fast_leda_match FROM fast_leda_sizes AS a LEFT OUTER JOIN hldata_z_w1pa AS b ON a.pgc=b.pgc;

CREATE TABLE fast_size_table (r2id_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 fast_size_table (r2id_spec, ra_j2000, dec_j2000, objid, ra_obj, dec_obj, amaj, ell, pa) SELECT * FROM fast_leda_match;
UPDATE fast_size_table SET size_survey = 'hyperleda';

INSERT INTO fast_size_table (r2id_spec, ra_j2000, dec_j2000, objid, ra_obj, dec_obj, amaj, ell, pa) SELECT r2id_spec, ra_j2000, dec_j2000, objid, ra, dec, petrorad_r, 1-deVAB_r, deVPhi_r FROM fast_sdss_matched;
UPDATE fast_size_table SET size_survey = 'sdss' WHERE size_survey is null;

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

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

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

INSERT INTO fast_size_table (r2id_spec, ra_j2000, dec_j2000) SELECT r2id_spec, ra_j2000, dec_j2000 FROM fast_nomatch;
UPDATE fast_size_table SET amaj = 3 WHERE amaj is null;
UPDATE fast_size_table SET ell = 0 WHERE ell is null;
UPDATE fast_size_table SET pa = 0 WHERE pa is null;

CREATE TABLE fast_size_table_2 (r2id_obj bigint, r2id_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 fast_size_table_2 SELECT  dense_rank() over (order by objid), r2id_spec, ra_j2000, dec_j2000, size_survey, objid, ra_obj, dec_obj, amaj, ell, pa FROM fast_size_table WHERE size_survey is not null AND size_survey!='nsc';
INSERT INTO fast_size_table_2 SELECT  35599+dense_rank() over (order by objid_nsc), r2id_spec, ra_j2000, dec_j2000, size_survey, objid, ra_obj, dec_obj, amaj, ell, pa FROM fast_size_table WHERE size_survey='nsc';
INSERT INTO fast_size_table_2 SELECT  37853+dense_rank() over (order by r2id_spec), r2id_spec, ra_j2000, dec_j2000, size_survey, objid, ra_j2000, dec_j2000, amaj, ell, pa FROM fast_size_table WHERE size_survey is null;

CREATE TABLE tmp_obj_spec (r2id_spec bigint, r2id_obj bigint);
INSERT INTO tmp_obj_spec SELECT r2id_spec, r2id_obj FROM full_spec_spoint;
INSERT INTO tmp_obj_spec SELECT r2id_spec, r2id_obj+4776519 FROM fast_size_table_2;
SELECT a.*, b.r2id_obj INTO TABLE full_spec_3 FROM full_spec_2 AS a LEFT JOIN tmp_obj_spec AS b ON a.r2id_spec=b.r2id_spec;
DROP TABLE full_spec_2;
ALTER TABLE full_spec_3 RENAME TO full_spec_2;

DROP TABLE fast_size_table;

SELECT DISTINCT ON(r2id_obj) * INTO TABLE fast_size_table FROM fast_size_table_2;

ALTER TABLE fast_size_table ADD COLUMN crd2000 spoint;
UPDATE fast_size_table SET crd2000=spoint(ra_obj*pi()/180.0, dec_obj*pi()/180.0);

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

SELECT * INTO TABLE rcsed_galaxies_withfast FROM rcsed_galaxies;

INSERT INTO rcsed_galaxies_withfast SELECT size_survey, objid, ra_obj, dec_obj, amaj, ell, pa, objid_nsc, 4776519+r2id_obj, gal, crd2000 from fast_size_table;

ALTER TABLE rcsed_galaxies_withfast ADD COLUMN circle1 scircle;
UPDATE rcsed_galaxies_withfast SET circle1=scircle(crd2000, 1./206265.);
CREATE INDEX index_crd2000 ON rcsed_galaxies_withfast using gist (crd2000);
CREATE INDEX index_circle1 ON rcsed_galaxies_withfast using gist (circle1);
---------------------------------

SELECT a.r2id_obj, b.r2id_obj AS r2id_obj_new INTO TABLE matched_gal FROM rcsed_galaxies_withfast AS a LEFT JOIN rcsed_galaxies_withfast AS b ON a.circle1 && b.circle1;

SELECT r2id_obj, min(r2id_obj_new) FROM matched_gal GROUP BY (r2id_obj) order by (r2id_obj) limit 10;

SELECT r2id_obj, min(r2id_obj_new) INTO TABLE matched_gal_2 FROM matched_gal GROUP BY (r2id_obj);

SELECT a.*, b.min AS r2id_obj_new INTO TABLE full_spec_3 FROM full_spec_2 AS a LEFT JOIN matched_gal_2 AS b ON a.r2id_obj=b.r2id_obj;

SELECT a.*, b.min AS r2id_obj_new INTO TABLE rcsed_galaxies_withfast_2 FROM rcsed_galaxies_withfast AS a LEFT JOIN matched_gal_2 AS b ON a.r2id_obj=b.r2id_obj;

SELECT a.*, b.r2id_obj AS r2id_obj_fromspec INTO TABLE rcsed_galaxies_withfast_3 FROM rcsed_galaxies_withfast_2 AS a LEFT JOIN full_spec_3 AS b ON a.r2id_obj_new=b.r2id_obj_new;

DELETE FROM rcsed_galaxies_withfast_3 WHERE r2id_obj_fromspec is null or r2id_obj is null;

SELECT DISTINCT ON (r2id_obj_new) * INTO TABLE rcsed_galaxies_withfast_4 FROM rcsed_galaxies_withfast_3;

ALTER TABLE full_spec_3 RENAME TO rcsed_spectra_2;
ALTER TABLE rcsed_galaxies_withfast_4 RENAME TO rcsed_galaxies_2;

SELECT a.*, b.r2id_spec AS r2id_spec_spec INTO TABLE aper_phot_2 FROM aper_phot AS a LEFT JOIN rcsed_spectra_2 AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM aper_phot_2 WHERE r2id_spec_spec is null;
ALTER TABLE aper_phot_2 DROP COLUMN r2id_spec_spec;
ALTER TABLE aper_phot_2 ADD CONSTRAINT photaperfk_new FOREIGN KEY (r2id_spec) REFERENCES rcsed_spectra_2 (r2id_spec);

SELECT a.*, b.r2id_spec AS r2id_spec_spec INTO TABLE raw_aper_phot_2 FROM raw_aper_phot AS a LEFT JOIN rcsed_spectra_2 AS b ON a.r2id_spec=b.r2id_spec;
DELETE FROM raw_aper_phot_2 WHERE r2id_spec_spec is null;
ALTER TABLE raw_aper_phot_2 DROP COLUMN r2id_spec_spec;
ALTER TABLE raw_aper_phot_2 ADD CONSTRAINT photspecfk_new FOREIGN KEY (r2id_spec) REFERENCES rcsed_spectra_2 (r2id_spec);	

SELECT a.*, b.r2id_obj AS r2id_obj_obj INTO TABLE raw_tot_phot_2 FROM raw_tot_phot AS a LEFT JOIN rcsed_galaxies_2 AS b ON a.r2id_obj=b.r2id_obj;
DELETE FROM raw_tot_phot_2 WHERE r2id_obj_obj is null;
ALTER TABLE raw_tot_phot_2 DROP COLUMN r2id_obj_obj;
ALTER TABLE raw_tot_phot_2 ADD CONSTRAINT photobjfk_new FOREIGN KEY (r2id_obj) REFERENCES rcsed_galaxies_2 (r2id_obj);

SELECT a.*, b.r2id_obj AS r2id_obj_obj INTO TABLE tot_phot_2 FROM tot_phot AS a LEFT JOIN rcsed_galaxies_2 AS b ON a.r2id_obj=b.r2id_obj;
DELETE FROM tot_phot_2 WHERE r2id_obj_obj is null;
ALTER TABLE tot_phot_2 DROP COLUMN r2id_obj_obj;
ALTER TABLE tot_phot_2 ADD CONSTRAINT photfullfk_new FOREIGN KEY (r2id_obj) REFERENCES rcsed_galaxies_2 (r2id_obj);

CREATE INDEX crd_ind_new ON rcsed_galaxies_2 using gist (crd2000);
CREATE INDEX gal_ind_new ON rcsed_galaxies_2 using gist (gal);

CREATE INDEX aperspecindex_new ON aper_phot_2 (r2id_spec);

CREATE INDEX filter_ind_new ON raw_aper_phot_2 (filter_id);
CREATE INDEX spec_ind_new ON raw_aper_phot_2 (r2id_spec);

CREATE INDEX totobjindex_new ON tot_phot_2 (r2id_obj);

GRANT SELECT ON full_spec_spoint TO dj_user;
GRANT SELECT ON rcsed_galaxies TO dj_user;
GRANT SELECT ON tot_phot TO dj_user;
GRANT SELECT ON aper_phot TO dj_user;
GRANT SELECT ON raw_tot_phot TO dj_user;
GRANT SELECT ON raw_aper_phot TO dj_user;

ALTER TABLE full_spec_spoint RENAME TO full_spec_spoint_new;
ALTER TABLE rcsed_galaxies RENAME TO rcsed_galaxies_new;
ALTER TABLE tot_phot RENAME TO tot_phot_new;
ALTER TABLE aper_phot RENAME TO aper_phot_new;
ALTER TABLE raw_tot_phot RENAME TO raw_tot_phot_new;
ALTER TABLE raw_aper_phot RENAME TO raw_aper_phot_new;

ALTER TABLE full_spec_spoint_old RENAME TO full_spec_spoint;
ALTER TABLE rcsed_galaxies_old RENAME TO rcsed_galaxies;
ALTER TABLE tot_phot_old RENAME TO tot_phot;
ALTER TABLE aper_phot_old RENAME TO aper_phot;
ALTER TABLE raw_tot_phot_old RENAME TO raw_tot_phot;
ALTER TABLE raw_aper_phot_old RENAME TO raw_aper_phot;

