#!/usr/bin/perl

use DBI;

# Database setup
$host = 'localhost';
$dbname = 'stardb';
$dbuser = '';
$pass = '';

# Connect to database
($dbh = DBI->connect("dbi:mysql:database=$dbname;host=$host",$dbuser,$pass)) or die("\tConnect not ok: $DBI::errstr\n");

my $statement = qq(
   SELECT StarID, RA, Declination, Distance
   FROM tblHYG
   WHERE StarID > 0
);

my $sth = $dbh->prepare( $statement );
my $rv = $sth->execute();
my @lookup_row;
while (@lookup_row = $sth->fetchrow_array) {
   my ($id, $ra, $dec, $dist) = @lookup_row;
   $ra = $ra * 15.0;
   $dist = $dist * 3.26;
   my ($x,$y,$z) = galactic($ra,$dec,$dist);

   print "$id: ($x, $y, $z)\n";

   my $statement2 = qq(
      INSERT INTO tblGalactic(StarID,X,Y,Z)
      VALUES('$id','$x','$y','$z')
   );
   my $sth2 = $dbh->prepare( $statement2 );
   my $rv2 = $sth2->execute();
}
$dbh->disconnect();
exit;


# Calculate galactic cartesian coordinates given:
#
# $phi_d = RA in degrees
# $theta_d = declination in degrees
# $rho = distance in light years

sub galactic {
   my $phi_d = shift;
   my $theta_d = shift;
   my $rho = shift;

   # Convert to radians
   my $phi = $phi_d * 0.0174532925;
   my $theta = $theta_d * 0.0174532925;

   # Find Equatorial cartesian coordinates

   my $rvect = $rho * cos($theta);

   my $x = $rvect * cos($phi);
   my $y = $rvect * sin($phi);
   my $z = $rho * sin($theta);

   # Find Galactic cartesian coordinates

   my $xg = -(.055 * $x) - (.8734 * $y) - (.4839 * $z);
   my $yg =  (.494 * $x) - (.4449 * $y) + (.747 * $z);
   my $zg = -(.8677 * $x) - (.1979 * $y) + (.4560 * $z);

   my @galactic = ($xg, $yg, $zg);

   return @galactic;

}