> 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.