I'm an astronomer who is basically self taught on mysql.
I'm trying to use mysql to match star lists.
I have each star list in a table, and part of the information
is the position of each star both as a RA/Dec pair (think lat/long),
as well as a unit vector x,y,z triplet.
Ideally I'd just code up a match of one star list verses the other,
but with one list being 2.5 million stars, and the list being 36
million stars, that takes a while. Since star matching is a N^2
algorithm, I'm trying to cut the run time by having an indexed value
obtained from NASA "Healpix", which can be described as an integer
value that depends on the star's position, stars near the north pole
have numbers near 0, stars near the south pole have a large value.

My ideal query would be

select h.prikey,t.pts_key,h.rad,h.ded,
h.btmag,h.vtmag,t.rad,t.decd,t.j,t.h,t.k
from hiptyc as h join twomass_b as t
where
h.x*t.x+h.y*t.y+h.z*t.z >= @DIST
and h.nest4 = t.nest4
;

which explains as:

mysql> explain select h.prikey,t.pts_key,h.rad,h.ded,
-> h.btmag,h.vtmag,t.rad,t.decd,t.j,t.h,t.k
-> from hiptyc as h join twomass_b as t
-> where
-> h.x*t.x+h.y*t.y+h.z*t.z >= @DIST
-> and h.nest4 = t.nest4
-> ;
+----+-------------+-------+------+---------------+-------+---------+---------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+---------------+---------+-------------+
| 1 | SIMPLE | h | ALL | nest4 | NULL | NULL |
NULL | 2563737 | |
| 1 | SIMPLE | t | ref | nest4 | nest4 | 5 |
ucac3.h.nest4 | 11904 | Using where |
+----+-------------+-------+------+---------------+-------+---------+---------------+---------+-------------+
2 rows in set (0.00 sec)


I can get reasonable subsamples by adding a

nest4 between X and Y

to the query, and then repeating the query with
new values for X and Y untill I get full coverage.

If I were writing this in Fortran or C, I'd obviously set up a loop
to cover all I need, but is there a way in SQL to do the equivalent
loop?

Does anyone have any other idea on how to get mysql to do what I want?

Thanks for any sugguestions.

> I'm an astronomer who is basically self taught on mysql.
> I'm trying to use mysql to match star lists.
> I have each star list in a table, and part of the information
> is the position of each star both as a RA/Dec pair (think lat/long),
> as well as a unit vector x,y,z triplet.
> Ideally I'd just code up a match of one star list verses the other,
> but with one list being 2.5 million stars, and the list being 36
> million stars, that takes a while. Since star matching is a N^2
> algorithm, I'm trying to cut the run time by having an indexed value
> obtained from NASA "Healpix", which can be described as an integer
> value that depends on the star's position, stars near the north pole
> have numbers near 0, stars near the south pole have a large value.
> My ideal query would be
>
> select h.prikey,t.pts_key,h.rad,h.ded,
> h.btmag,h.vtmag,t.rad,t.decd,t.j,t.h,t.k
> from hiptyc as h join twomass_b as t
> where
> h.x*t.x+h.y*t.y+h.z*t.z >= @DIST
> and h.nest4 = t.nest4
> ;
> which explains as:
>
mysql>> explain select h.prikey,t.pts_key,h.rad,h.ded,
mysql>>
> -> h.btmag,h.vtmag,t.rad,t.decd,t.j,t.h,t.k
> -> from hiptyc as h join twomass_b as t
> -> where
> -> h.x*t.x+h.y*t.y+h.z*t.z >= @DIST
> -> and h.nest4 = t.nest4
> -> ;
> +----+-------------+-------+------+---------------+-------+---------+-
> --------------+---------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+-------+------+---------------+-------+---------+-
> --------------+---------+-------------+
> | 1 | SIMPLE | h | ALL | nest4 | NULL | NULL |
> NULL | 2563737 | |
> | 1 | SIMPLE | t | ref | nest4 | nest4 | 5 |
> ucac3.h.nest4 | 11904 | Using where |
> +----+-------------+-------+------+---------------+-------+---------+-
> --------------+---------+-------------+
> 2 rows in set (0.00 sec)
> I can get reasonable subsamples by adding a
>
> nest4 between X and Y
>
> to the query, and then repeating the query with
> new values for X and Y untill I get full coverage.
> If I were writing this in Fortran or C, I'd obviously set up a loop to
> cover all I need, but is there a way in SQL to do the equivalent loop?
>
> Does anyone have any other idea on how to get mysql to do what I want?
>
> Thanks for any sugguestions.
>

Greg,

I think that you might be able to solve this with a simple sorted select
on the x,y,and z coordinates. A star in the first database that is the same
or close to a star in the second database will be sorted close to each other
in a sorted select statement. Put this list in a table.

I'm kinda new to MySQL, so I don't know if it has cursors. What I'm thinking
is that you could use a stored procedure to go through the sorted list of
stars with a cursor and compute the dot product of each row with the next.
Then put any dot product >= MyLimit into a new table.

I hope that s.