source_to_arrays_ERR.sql

-- script to generate GAIA intermediate arrays and info

begin;

-- explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
with
qsum as (
select *
,trunc(ra_hp_min * 10000)*0.0001 as ra_base
,trunc((90.0+dec_hp_min) * 10000)*0.0001 - 90.0 as dec_base
,case when ra_hp_min < 90.0 and ra_hp_max > 270.0 then 4 else 0 end as iflag
from (
select 
 count(*) as nsource
,(source_id >> 35+12)&((1<<16)-1) as ibhp
,bit_or(source_id) as source_ored
,sum(source_id&(1::bigint<<(7+25)))::bigint as norg_one
,min(ra)  as  ra_hp_min
,max(ra)  as  ra_hp_max
,min(dec) as dec_hp_min
,max(dec) as dec_hp_max
,min(l) as gal_lon_hp_min
,max(l) as gal_lon_hp_max
,min(b) as gal_lat_hp_min
,max(b) as gal_lat_hp_max
,min(ecl_lon) as ecl_lon_hp_min
,max(ecl_lon) as ecl_lon_hp_max
,min(ecl_lat) as ecl_lat_hp_min
,max(ecl_lat) as ecl_lat_hp_max
from source_imp
group by ibhp
) qs
)
-- select count(*) from qsum;
-- select * from qsum;
,qoffs as (
--select (row_number () over (partition by source_id>>(35+12))-1)::int as ioffs, qseq.*
--from (
select
 (source_id >> 35+12+12)&((1<<4)-1) as il0_hp
,(source_id >> 35+12)&((1<<16)-1) as ibhp
,(source_id >>  7)&((1<<25)-1) as iseq
,(source_id >> 35)&((1<<12)-1)::smallint as isubhp
,(source_id >> 7+25) as iorg
,((source_id >>  7)&((1<<28)-1))::int as isource_seq
,source_id
,ra
,dec
,(((((ra -(select ra_base from qsum))*1000000000000)::bigint)>>15)&((1::bigint<<31)-1))::int as ira
,((((dec-(select dec_base from qsum))*1000000000000)::bigint)>>15)::int as idec
,parallax
,pmra
,pmdec
--,astrometric_n_good_obs_al
,pseudocolour
,phot_g_mean_mag
,phot_bp_mean_mag
,phot_rp_mean_mag
,bp_rp
,bp_g
,g_rp
,radial_velocity
,l
,b
,ecl_lon
,ecl_lat
,teff_gspphot
from source_imp
order by ibhp,iseq,iorg 
--) qseq
)
-- select * from qoffs limit 100;
,qupd_info as (
update gaia_hp_info set
 ghpi_nsource = nsource
,ghpi_norg_one = norg_one>>(25+7)
,ghpi_id_ored = source_ored
,ghpi_ra_hp_min = ra_hp_min
,ghpi_ra_hp_max = ra_hp_max
,ghpi_dec_hp_min = dec_hp_min
,ghpi_dec_hp_max = dec_hp_max
,ghpi_ra_substract_base = ra_base
,ghpi_dec_substract_base = dec_base
,ghpi_gal_lon_hp_min = gal_lon_hp_min
,ghpi_gal_lon_hp_max = gal_lon_hp_max
,ghpi_gal_lat_hp_min = gal_lat_hp_min
,ghpi_gal_lat_hp_max = gal_lat_hp_max
,ghpi_ecl_lon_hp_min = ecl_lon_hp_min
,ghpi_ecl_lon_hp_max = ecl_lon_hp_max
,ghpi_ecl_lat_hp_min = ecl_lat_hp_min
,ghpi_ecl_lat_hp_max = ecl_lat_hp_max
,ghpi_imp_date = current_date
,ghpi_flags = ghpi_flags | iflag
from qsum
where id_ghpi=ibhp
)
-- select sum(nsource) as total_sources from qsum;
,qagg_id_arr as (
select
 ibhp
,il0_hp
-- array_agg(ioffs) as ioffs_agg
,array_agg(isubhp) as isubhp_agg
,array_agg(isource_seq) as isource_seq_agg
,array_agg(ira) as ira_agg
,array_agg(idec) as idec_agg
--,case when (select iflag from qsum) = 4 then array_agg(ra) else NULL end as ra_agg
,array_agg(ra) as ra_agg
,array_agg(dec) as dec_agg
,array_agg(l) as l_agg
,array_agg(b) as b_agg
,array_agg(ecl_lon) as ecl_lon_agg
,array_agg(ecl_lat) as ecl_lat_agg
from qoffs
group by il0_hp,ibhp
)
-- select ibhp,isubhp_agg[1:20],isource_seq_agg[1:20] from qagg_id_arr limit 20;
-- select ibhp,isubhp_agg[1:20],isource_seq_agg[1:20],ira_agg[1:20],idec_agg[1:200] from qagg_id_arr;
,qupd_id_arr as (
update gaia_id_arr set
-- ioffs_arr = ioffs_agg
 isubhp_arr = isubhp_agg
,isource_seq_arr = isource_seq_agg
,ira_arr = ira_agg
,idec_arr = idec_agg
from qagg_id_arr
where l0_hp=il0_hp and id=ibhp
)
-- select sum(nsource) as total_sources from qsum;
,qupd_coord_arr as (
update gaia_coord_arr set
 ra_arr = ra_agg
,dec_arr = dec_agg
,l_arr = l_agg
,b_arr = b_agg
,ecl_lon_arr = ecl_lon_agg
,ecl_lat_arr = ecl_lat_agg
from qagg_id_arr
where l0_hp=il0_hp and id=ibhp
)
,qagg_astro_arr as (
select
 ibhp
,il0_hp
,array_agg(parallax) parallax_agg
,array_agg(pmra) pmra_agg
,array_agg(pmdec) pmdec_agg
,array_agg(pseudocolour) pseudo_color_agg
,array_agg(phot_g_mean_mag) g_mean_mag_agg
,array_agg(phot_bp_mean_mag) bp_mean_mag_agg
,array_agg(phot_rp_mean_mag) rp_mean_mag_agg
,array_agg(bp_rp) bp_rp_agg
,array_agg(bp_g) bp_g_agg
,array_agg(g_rp) g_rp_agg
,array_agg(radial_velocity) radial_vel_agg
,array_agg(teff_gspphot) teff_val_agg
from qoffs
group by il0_hp,ibhp
)
,qupd_astro_arr as (
update gaia_astro_arr set
 parallax_arr = parallax_agg
,pmra_arr = pmra_agg
,pmdec_arr = pmdec_agg
,pseudo_color_arr = pseudo_color_agg
,g_mean_mag_arr = g_mean_mag_agg
,bp_mean_mag_arr = bp_mean_mag_agg
,rp_mean_mag_arr = rp_mean_mag_agg
,bp_rp_arr = bp_rp_agg
,bp_g_arr = bp_g_agg
,g_rp_arr = g_rp_agg
,radial_vel_arr = radial_vel_agg
,teff_val_arr = teff_val_agg
from qagg_astro_arr
where l0_hp=il0_hp and id=ibhp
)
select sum(nsource) as total_sources from qsum
;

commit;