hsoy, "Hot stuff for one year", is a project to combine PPMXL, SuperCOSMOS, and
the Gaia DR1 source list to provide a catalog that gives nice and good
astrometry for the year until Gaia's second data release.

Sigfried Röser derived the equations to include this other position; this
is essentially algebra starting from eqs. 1 and 2 of the PPMXL paper.

The late development of this was a bit rough, and what's documented in
the RD doesn't quite match the catalog (e.g., some ra<0 were fixed
manually).  If you need to re-import this, start from the dump.

In other words: Don't use the data items in here any more.



Informationally, here's what the provenance looked like until svn rev.
6098, when importing was switched to using the dump:

<!-- first step: create an intermediate catalog consisting of
PPMXL stars that have a counterpart in SuperCOSMOS (not done in
the published version. -->

<STREAM id="core_columns">
	<column original="ipix" ucd="meta.id.cross"/>
	<LOOP listItems="raj2000 dej2000 e_raepRA e_deepDE
		pmRA pmDE e_pmRA e_pmDE epRA epDE Kmag">
		<events>
			<column original="\item" verbLevel="8"/>
		</events>
	</LOOP>
</STREAM>
<!-- in DaCHS svn revision 6098, MD removed the "provenance", i.e.,
the tables and the code that produced the HSOY.  See in svn or in the
README if you want to recover it. -->

<table id="cleanedppmxl" onDisk="True" namePath="ppmxl/q#main">
	<property key="internal">Usually dropped helper table</property>
	<meta name="description">A table containing PPMXL positions and
		proper motions, with those objects filtered out that
		SuperCOSMOS didn't find with their source extraction.   This
		means that most spurious detections are removed.</meta>
	<index columns="ipix"/>
	<FEED source="core_columns"/>
</table>

<data id="clean_ppmxl" auto="False">
	<make table="cleanedppmxl">
		<script type="preIndex" lang="SQL" name="delete non-supercosmos objects">
			INSERT INTO \curtable
			SELECT \colNames
			FROM (select * from ppmxl.main
				) as p
-					where q3c_join(raj2000, dej2000, 0, 90, 3)) as p
			WHERE EXISTS (
				SELECT 1 FROM  supercosmos.sources AS s
				WHERE q3c_join(p.raj2000, p.dej2000, s.raj2000, s.dej2000, 3/3600.))
		</script>
	</make>
</data>

<table id="ppmxlat2015" onDisk="True" namePath="cleanedppmxl">
	<!-- do *not* cluster this table â it's close enough, and the actual
	cluster takes ages; hence, no mixin, manual index def. -->
	<property key="internal">Usually dropped helper table</property>
	<index name="q3c_at2015">
		<columns>raj2000, dej2000</columns>
		q3c_ang2ipix(raj2000, dej2000)
	</index>
	<index columns="ipix"/>
	<column original="ipix"/>
	<column original="raj2000" description="PPMXL RA at epoch J2015"/>
	<column original="dej2000" description="PPMXL Dec at epoch J2015"/>
</table>

<data id="to_gaia_epoch" auto="False">
	<make table="ppmxlat2015">
		<script type="preIndex" lang="SQL"
			name="compute PPMXL positions for J2015.0">
		insert into \qName (
		  select ipix,
		  	p.raj2000+p.pmra/cos(radians(p.dej2000))*(2015-2000) as raj2000,
				p.dej2000+p.pmde*(2015-2000) as dej2000
			from ppmxl.main as p)
		</script>
	</make>
</data>

<table id="withgaia" onDisk="True" namePath="cleanedppmxl"
	mixin="//scs#q3cindex">
	<meta name="description">A table with positions and proper motions
		resulting from a combination of ppmxl with gaia DR1.

		Note that the column descriptions may not be accurate here.
	</meta>
	<property key="internal">Usually dropped helper table</property>
	<index columns="ipix"/>
	<FEED source="core_columns"/>
	<column original="gaia/q#dr1.source_id" name="gaia_id"
		ucd="meta.id.cross"/>
	<column name="comp" type="smallint"
		ucd="meta.code.multip"
		tablehead="Comp"
		description="If non-null this indicates that multiple Gaia
			objects matched the PPMXL object; this may indicate
			bona fide multiple stars, but more likely is due to failed
			matching of Gaia observations at different epochs.  In both
			cases, proper motions must be used with care. The index
			is artificial, i.e., no primary, secondary, etc, is implied.
			ipix+comp together are a primary key to hsoy.main."
		verbLevel="5">
		<values nullLiteral="0"/>
	</column>
	<column name="clone" type="smallint"
		ucd="meta.code.qual"
		tablehead="Clone"
		description="If 1, more than one PPMXL object matched to this Gaia object
			(i.e.: proper motion is probably wrong, any apparent duplicity is
			probably spurious). This is normally due to failed matching of
			objects from different plates in USNO-B."
		verbLevel="15" note="cl">
		<values nullLiteral="0"/>
	</column>
	<meta name="note" tag="cl">
			During the construction of USNO-B, numerous observations
			were not matched properly.  As a result, at least 10% of USNO-B objects
			are spurious.  Most of these carried over into PPMXL.  Since many
			of these objects have completely errneous proper motions, much fewer
			will be in HSOY.  However, they are present, and they are characterised
			by multiple PPMXL objects being matched to one Gaia object.
			Since PPMXL's resolution is much worse than Gaia's, these will almost
			always be spurious; also, since the Gaia position is fixed in
			both solutions, their paths will cross fairly near to J2015.0.
	</meta>
</table>

<data id="add_gaia" auto="False">
	<make table="withgaia">
		<!-- this works with a function directly inserting the combined position
		into the new table.

		The function first translates the incoming positions into the
		tangential plane around PPMXL's position, does the computations there,
		and finally transforms back. -->

<script type="preIndex" lang="SQL" name="compute combined catalog">
		<![CDATA[
REATE OR REPLACE FUNCTION \schema\+_merge_gaia(
a_ppmxl DOUBLE PRECISION, de_ppmxl DOUBLE PRECISION,
a_new DOUBLE PRECISION, de_new DOUBLE PRECISION,
mra_ppmxl DOUBLE PRECISION, pmde_ppmxl DOUBLE PRECISION,
eanep_ra DOUBLE PRECISION, meanep_de DOUBLE PRECISION,
poch_add DOUBLE PRECISION,
eightra_ppmxl DOUBLE PRECISION, weightde_ppmxl DOUBLE PRECISION,
eightra_add DOUBLE PRECISION, weightde_add DOUBLE PRECISION,
eightpmra_ppmxl DOUBLE PRECISION, weightpmde_ppmxl DOUBLE PRECISION,
pix BIGINT, id_add BIGINT
- all angles must be in radians. Epochs are in years.
- Weights are in 1/deg^2.
- The function inserts its results into the withgaia table
- (where values will be in degrees)

ETURNS VOID AS $$
ECLARE
weightra_new DOUBLE PRECISION;
weightde_new DOUBLE PRECISION;
weightpmra_new DOUBLE PRECISION;
weightpmde_new DOUBLE PRECISION;
wf DOUBLE PRECISION;
pmra_new DOUBLE PRECISION;
pmde_new DOUBLE PRECISION;
t_0_ra DOUBLE PRECISION;
t_0_de DOUBLE PRECISION;
t_new_ra DOUBLE PRECISION;
t_new_de DOUBLE PRECISION;
t_1_ra DOUBLE PRECISION;
t_1_de DOUBLE PRECISION;
x_new DOUBLE PRECISION;
y_new DOUBLE PRECISION;
plane_pos_1 POINT;
tangent_point SPOINT;
new_pos SPOINT;
rec RECORD;
EGIN
-- conversion to the tangential plane to the position at PPMXL mean
-- epoch.
--
-- x is for ra, y for dec in the tangential plane
-- t_ra, t_de, t_new are time wrt. mean ep.

tangent_point = SPOINT(
	ra_ppmxl+pmra_ppmxl/cos(de_ppmxl)*(meanep_ra-2000),
	de_ppmxl+pmde_ppmxl*(meanep_de-2000));

t_0_ra := 2000-meanep_ra;
t_0_de := 2000-meanep_de;
t_1_ra := epoch_add-meanep_ra;
t_1_de := epoch_add-meanep_de;

select * from gnomonic_proj(
	spoint(ra_new, de_new), tangent_point)
	into plane_pos_1;

-- This is according to Roeser, priv. comm.
-- First, RA
weightra_new := weightra_ppmxl + weightra_add;
wf := weightra_add/weightra_new;
t_new_ra := wf*t_1_ra;
x_new := wf*plane_pos_1[0];
weightpmra_new := weightpmra_ppmxl+weightra_ppmxl*wf*t_1_ra*t_1_ra;
pmra_new := (weightpmra_ppmxl*pmra_ppmxl
	+ weightra_ppmxl*wf*plane_pos_1[0]*t_1_ra)/weightpmra_new;

-- Then, Dec
weightde_new := weightde_ppmxl + weightde_add;
wf := weightde_add/weightde_new;
t_new_de := wf*t_1_de;
y_new := wf*plane_pos_1[1];
weightpmde_new := weightpmde_ppmxl+weightde_ppmxl*wf*t_1_de*t_1_de;
pmde_new := (weightpmde_ppmxl*pmde_ppmxl
	+ weightde_ppmxl*wf*plane_pos_1[1]*t_1_de)/weightpmde_new;

-- now bring positions to epoch J2000
x_new = x_new+pmra_new*(t_0_ra-t_new_ra);
y_new = y_new+pmde_new*(t_0_de-t_new_de);

select * from gnomonic_inv(POINT(x_new, y_new), tangent_point) into
	new_pos;

INSERT INTO \schema.withgaia (
		ipix, gaia_id, raj2000, dej2000,
		e_raepRA, e_deepDE,
		pmRA, pmDE, e_pmRA, e_pmDE, epRA, epDE)
	VALUES (ipix, id_add,
		DEGREES(long(new_pos)),
		DEGREES(lat(new_pos)),
		1/sqrt(weightra_new), 1/sqrt(weightde_new),
		DEGREES(pmra_new), DEGREES(pmde_new),
		1/sqrt(weightpmra_new), 1/sqrt(weightpmde_new),
		t_new_ra+meanep_ra, t_new_de+meanep_de);
ND;
$ LANGUAGE plpgsql;

ELECT \schema\+_merge_gaia(
		ra_ppmxl, de_ppmxl, ra_add, de_add,
		pmra, pmde,
		epra, epde, ep_add,
		weightra_ppmxl, weightde_ppmxl,
		weightra_add, weightde_add,
		weightpmra_ppmxl, weightpmde_ppmxl,
		ipix, id_add
	) FROM
		(SELECT p.ipix as ipix,
					radians(p.raj2000) AS ra_ppmxl, radians(p.dej2000) as de_ppmxl,
					radians(p.pmRA) as pmra, radians(p.pmDE) as pmde,
					p.epRA as epra, p.epDE as epde,
					2015 as ep_add,
					1/p.e_raepRA^2 as weightra_ppmxl, 1/p.e_deepDE^2 as weightde_ppmxl,
					1/p.e_pmRA^2 as weightpmra_ppmxl, 1/p.e_pmDE^2 as weightpmde_ppmxl,
					radians(g.raj2000) AS ra_add, radians(g.dej2000) as de_add,
					3.6e6*3.6e6/(ra_error*ra_error) as weightra_add,
					3.6e6*3.6e6/(dec_error*dec_error) as weightde_add,
					g.source_id as id_add
				FROM -- (SELECT * FROM gaia.dr1 LIMIT 5000 OFFSET 0) as g
           gaia.dr1 AS g
           JOIN hsoy.ppmxlat2015 AS a ON q3c_join(
             a.raj2000, a.dej2000,
             g.raj2000, g.dej2000,
             1/3600.)
           JOIN ppmxl.main AS p USING (ipix)
				OFFSET 0) as q
		]]></script>

		<script type="preIndex" lang="SQL" name="add component ids and clone
			flags">
			-- for about 2e6 objects, there are multiple gaia matches
			-- for a single PPMXL object.  Most of these are genuine
			-- multiple stars, but whatever they are, we need to somehow
			-- make unique identifiers.
			create index tmp_withgaia_ipix on hsoy.withgaia (ipix);
			create index tmp_withgaia_gaia_id on hsoy.withgaia (gaia_id);

			update hsoy.withgaia as target
			set comp=source.comp
			from (
				select * from (
					select
						ipix,
						gaia_id,
						row_number() over w as comp,
						count(*) over w as n
					from hsoy.withgaia
					window w as (partition by ipix order by ipix))  as q
					where n>1) as source
			where
				target.gaia_id=source.gaia_id
				and target.ipix=source.ipix;
		
			commit;
			-- without the reindex, pg 9.4 raises an "unexpected zero page"
			reindex table hsoy.withgaia;

			-- usno-b non-matched objects become weird colliding stars
			-- in hsoy.  Add a warning for the objects for which that
			-- happened.
			update hsoy.withgaia as target
				set clone=1
			where
				gaia_id in (
					select gaia_id from hsoy.withgaia
					group by gaia_id
					having count(*)>1);

			drop index hsoy.tmp_withgaia_gaia_id;
			drop index hsoy.tmp_withgaia_ipix;
		</script>
	</make>
</data>

	<data id="build_hsoy" auto="False">
		<!-- for hsoy, we turn Gaia flux errors into magnitude errors.
		This becomes tricky (asymmetry) for uncertain fluxes.  Thus,
		we don't give magnitudes when flux_err/flux > 0.1.  Otherwise,
		with s flux, σ flux_error, and δm the one-sided errors in magnitude,

			δm_+- = -2.5 log((s+/-σ)/s) = -2.5 log e ln(1+/-σ/s)
			 = +/-2.5 log e (σ/s -/+ (σ/s)**2 -/+ ...)

		Since σ/s is guaranteed small by our selection limit, our magnitude
		error is well-approximated (to at least about a percent) by
		1.09*σ/s. -->

		<make table="main">
			<script type="preIndex" lang="SQL" name="compute finished catalog">
			<![CDATA[
				insert into \schema.main
					SELECT
			 			h.ipix, h.comp,
			 			CASE WHEN h.raj2000>=360. THEN h.raj2000-360 ELSE h.raj2000 END,
			 			h.dej2000, h.e_raepRA, h.e_deepDE,
			 			h.pmRA, h.pmDE,
						h.e_pmRA, h.e_pmDE, h.epRA, h.epDE,
						p.Jmag, p.e_Jmag, p.Hmag, p.e_Hmag, p.Kmag,
						p.e_Kmag, p.b1mag, p.b2mag, p.r1mag, p.r2mag, p.imag, p.magSurveys,
						p.nobs+1 as nobs,
						h.gaia_id,
							CASE WHEN phot_g_mean_flux_error/phot_g_mean_flux>0.1
							THEN NULL
							ELSE g.phot_g_mean_mag END
						AS phot_g_mean_mag,
						CASE WHEN phot_g_mean_flux_error/phot_g_mean_flux>0.1
							THEN NULL
							ELSE 1.09*phot_g_mean_flux_error/phot_g_mean_flux END
						AS e_phot_g_mean_mag
					FROM
						\schema.withgaia AS h
						JOIN ppmxl.main AS p USING (ipix)
						JOIN gaia.dr1 AS g ON (g.source_id=h.gaia_id)
			]]></script>
		</make>
	</data>

	<data id="add_no_sc" auto="False" updating="True">
		<sources><item>ignored</item></sources>
		<nullGrammar/>
		<make table="main">
			<script type="newSource" lang="SQL" name="compute no_sc flag">
				update hsoy.main as h
					set no_sc=1
					where not exists (select 1 from hsoy.cleanedppmxl as p
						where h.ipix=p.ipix)
			</script>
		</make>
	</data>