exoplanets.pl   exoplanets.pl 
skipping to change at line 40 skipping to change at line 40
# #
# Stage 1: connect to 'The Extrasolar Planets Encyclopaedia' at exoplanet.e u, fetch CSV data and store to MySQL # Stage 1: connect to 'The Extrasolar Planets Encyclopaedia' at exoplanet.e u, fetch CSV data and store to MySQL
# Stage 2: read MySQL catalog of exoplanets and store it to JSON # Stage 2: read MySQL catalog of exoplanets and store it to JSON
# #
$URL = "http://exoplanet.eu/catalog/csv/"; $URL = "http://exoplanet.eu/catalog/csv/";
$CSV = "./exoplanets.csv"; $CSV = "./exoplanets.csv";
$JSON = "./exoplanets.json"; $JSON = "./exoplanets.json";
$HCSV = "./habitable.csv"; $HCSV = "./habitable.csv";
$NCSV = "./names.csv";
$CNT = "./count"; $CNT = "./count";
$CATALOG_FORMAT_VERSION = 1; $CATALOG_FORMAT_VERSION = 1;
$dbname = "exoplanets"; $dbname = "exoplanets";
$dbhost = "localhost"; $dbhost = "localhost";
$dbuser = "exoplanet"; $dbuser = "exoplanet";
$dbpass = "exoplanet"; $dbpass = "exoplanet";
$UA = LWP::UserAgent->new(keep_alive => 1, timeout => 360); $UA = LWP::UserAgent->new(keep_alive => 1, timeout => 360);
$UA->agent("Mozilla/5.0 (Stellarium Exoplanets Catalog Updater 2.7; http:// stellarium.org/)"); $UA->agent("Mozilla/5.0 (Stellarium Exoplanets Catalog Updater 2.8; http:// stellarium.org/)");
$request = HTTP::Request->new('GET', $URL); $request = HTTP::Request->new('GET', $URL);
$responce = $UA->request($request); $responce = $UA->request($request);
if ($responce->is_success) { if ($responce->is_success) {
open(OUT, ">$CSV"); open(OUT, ">$CSV");
$data = $responce->content; $data = $responce->content;
binmode OUT; binmode OUT;
print OUT $data; print OUT $data;
close OUT; close OUT;
} else { } else {
skipping to change at line 82 skipping to change at line 83
%hs = (); %hs = ();
%hp = (); %hp = ();
for ($i=1;$i<scalar(@habitable);$i++) { for ($i=1;$i<scalar(@habitable);$i++) {
$status = $csvdata->parse($habitable[$i]); $status = $csvdata->parse($habitable[$i]);
@hdata = $csvdata->fields(); @hdata = $csvdata->fields();
%hs = (%hs, $hdata[1], 1); %hs = (%hs, $hdata[1], 1);
%hp = (%hp, $hdata[1]." ".$hdata[2], $habitable[$i]); %hp = (%hp, $hdata[1]." ".$hdata[2], $habitable[$i]);
} }
open (NCSV, "<$NCSV");
@propname = <NCSV>;
close NCSV;
%pns = ();
%pnp = ();
for ($i=1;$i<scalar(@propname);$i++) {
$status = $csvdata->parse($propname[$i]);
@pndata = $csvdata->fields();
if ($pndata[1] eq '') {
%pns = (%pns, $pndata[0], $propname[$i]);
} else {
%pnp = (%pnp, $pndata[0]." ".$pndata[1], $propname[$i]);
}
}
open (CSV, "<$CSV"); open (CSV, "<$CSV");
@catalog = <CSV>; @catalog = <CSV>;
close CSV; close CSV;
$dbh = DBI->connect($dsn, $dbuser, $dbpass, {'RaiseError' => 1}); $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'RaiseError' => 1});
$sth = $dbh->do(q{SET NAMES utf8}); $sth = $dbh->do(q{SET NAMES utf8});
$sth = $dbh->do(q{TRUNCATE stars}); $sth = $dbh->do(q{TRUNCATE stars});
$sth = $dbh->prepare(q{SELECT COUNT(pid) FROM planets}); $sth = $dbh->prepare(q{SELECT COUNT(pid) FROM planets});
$sth->execute(); $sth->execute();
@ipcnt = $sth->fetchrow_array(); @ipcnt = $sth->fetchrow_array();
skipping to change at line 194 skipping to change at line 211
$deg = 29; $min = 36; $sec = 57.9; $deg = 29; $min = 36; $sec = 57.9;
} }
$outRA = $hour."h".abs($mint)."m".abs($sect)."s"; $outRA = $hour."h".abs($mint)."m".abs($sect)."s";
$outDE = $deg."d".abs($min)."m".abs($sec)."s"; $outDE = $deg."d".abs($min)."m".abs($sec)."s";
# fixed bug for 24 Sex # fixed bug for 24 Sex
if ($starname =~ m/24\s+Sex/gi) { if ($starname =~ m/24\s+Sex/gi) {
$outDE = "-".$outDE; $outDE = "-".$outDE;
} }
$sname = $starname; # fixed proper names of stars
$starname =~ s/Fomalhaut/alpha PsA/gi;
$sname =~ s/^alpha/α/gi; $starname =~ s/Aldebaran/alpha Tau/gi;
$sname =~ s/^alf/α/gi; $starname =~ s/Pollux/beta Gem/gi;
$sname =~ s/^beta/β/gi; $starname =~ s/PSR 1257 12/PSR B1257+12/gi;
$sname =~ s/^gamma/γ/gi; $starname =~ s/PSR 1719-14/PSR B1719-14/gi;
$sname =~ s/^delta/δ/gi; # fixed designations of stars
$sname =~ s/^epsilon/ε/gi; $starname =~ s/Eridani/Eri/gi;
$sname =~ s/^eps/ε/gi; $starname =~ s/Cephei/Cep/gi;
$sname =~ s/^zeta/ζ/gi;
$sname =~ s/^theta/θ/gi;
$sname =~ s/^eta/η/gi;
$sname =~ s/^iota/ι/gi;
$sname =~ s/^kappa/κ/gi;
$sname =~ s/^lambda/λ/gi;
$sname =~ s/^mu/μ/gi;
$sname =~ s/^nu/ν/gi;
$sname =~ s/^xi/ξ/gi;
$sname =~ s/^ksi/ξ/gi;
$sname =~ s/^(omicron|omi)/ο/gi;
$sname =~ s/^pi/π/gi;
$sname =~ s/^rho/ρ/gi;
$sname =~ s/^(sigma|sig)/σ/gi;
$sname =~ s/^tau/τ/gi;
$sname =~ s/^upsilon/υ/gi;
$sname =~ s/^ups/υ/gi;
$sname =~ s/^phi/φ/gi;
$sname =~ s/^chi/χ/gi;
$sname =~ s/^psi/ψ/gi;
$sname =~ s/^omega/ω/gi;
$sname =~ s/^ome/ω/gi;
if (($sRA != 0.0) && ($sDec != 0.0) && ($sname ne '')) { if (($sRA != 0.0) && ($sDec != 0.0) && ($starname ne '')) {
# check star # check star
$sth = $dbh->prepare(q{SELECT sid,sname FROM stars WHERE ra_ coord=? AND dec_coord=?}); $sth = $dbh->prepare(q{SELECT sid,sname FROM stars WHERE ra_ coord=? AND dec_coord=?});
$sth->execute($outRA, $outDE); $sth->execute($outRA, $outDE);
@starDATA = $sth->fetchrow_array(); @starDATA = $sth->fetchrow_array();
# get star ID # get star ID
if (scalar(@starDATA)!=0) { if (scalar(@starDATA)!=0) {
$starID = @starDATA[0]; $starID = @starDATA[0];
$starName = @starDATA[1]; $starName = @starDATA[1];
} else { } else {
$HPflag = 0; $HPflag = 0;
if (exists($hs{$sname})) { if (exists($hs{$starname})) {
$HPflag = 1; $HPflag = 1;
} }
$sProperName = '';
if (exists($pns{$starname})) {
$status = $csvdata->parse($pns{$starname});
($hssname,$hspname,$sProperName) = $csvdata-
>fields();
}
# insert star data # insert star data
$sth = $dbh->do(q{INSERT INTO stars (ra_coord,dec_co ord,sname,distance,stype,smass,smetal,vmag,sradius,sefftemp,has_habit_plane t) VALUES (?,?,?,?,?,?,?,?,?,?,?)}, undef, $outRA, $outDE, $sname, $sdist, $sstype, $smass, $smetal, $sVmag, $sradius, $sefftemp, $HPflag); $sth = $dbh->do(q{INSERT INTO stars (ra_coord,dec_co ord,sname,propername,distance,stype,smass,smetal,vmag,sradius,sefftemp,has_ habit_planet) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)}, undef, $outRA, $outDE, $st arname, $sProperName, $sdist, $sstype, $smass, $smetal, $sVmag, $sradius, $ sefftemp, $HPflag);
$sth = $dbh->prepare(q{SELECT sid,sname FROM stars O RDER BY sid DESC LIMIT 0,1}); $sth = $dbh->prepare(q{SELECT sid,sname FROM stars O RDER BY sid DESC LIMIT 0,1});
$sth->execute(); $sth->execute();
@starDATA = $sth->fetchrow_array(); @starDATA = $sth->fetchrow_array();
$starID = @starDATA[0]; $starID = @starDATA[0];
$starName = @starDATA[1]; $starName = @starDATA[1];
} }
$hclass = ''; $hclass = '';
$hptype = ''; $hptype = '';
$mstemp = -1; $mstemp = -1;
$eqtemp = -1; $eqtemp = -1;
$esi = -1; $esi = -1;
$key = $starName." ".$pname; $key = $starName." ".$pname;
if (exists($hp{$key})) { if (exists($hp{$key})) {
$status = $csvdata->parse($hp{$key}); $status = $csvdata->parse($hp{$key});
($hsn,$hsname,$hpname,$hptype,$eqtemp,$esi) = $csvda ta->fields(); ($hsn,$hsname,$hpname,$hptype,$eqtemp,$esi) = $csvda ta->fields();
} }
$pProperName = '';
if (exists($pnp{$key})) {
$status = $csvdata->parse($pnp{$key});
($hsname,$hpname,$pProperName) = $csvdata->fields();
}
# insert planet data # insert planet data
$sth = $dbh->do(q{INSERT INTO planets (sid,pname,pmass,pradi us,pperiod,psemiaxis,pecc,pinc,padistance,discovered,hptype,eqtemp,esi) VAL UES (?,?,?,?,?,?,?,?,?,?,?,?,?)}, undef, $starID, $pname, $pmass, $pradius, $pperiod, $paxis, $pecc, $pincl, $angdist, $discovered, $hptype, $eqtemp, $esi); $sth = $dbh->do(q{INSERT INTO planets (sid,pname,propername, pmass,pradius,pperiod,psemiaxis,pecc,pinc,padistance,discovered,hptype,eqte mp,esi) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}, undef, $starID, $pname, $pPr operName, $pmass, $pradius, $pperiod, $paxis, $pecc, $pincl, $angdist, $dis covered, $hptype, $eqtemp, $esi);
} }
# else # else
# { # {
# print $sname.": ".$sRA.":".$sDec." [".$currdata."]\n"; # print $sname.": ".$sRA.":".$sDec." [".$currdata."]\n";
# } # }
} }
open (JSON, ">$JSON"); open (JSON, ">$JSON");
print JSON "{\n"; print JSON "{\n";
print JSON "\t\"version\": \"".$CATALOG_FORMAT_VERSION."\",\n"; print JSON "\t\"version\": \"".$CATALOG_FORMAT_VERSION."\",\n";
skipping to change at line 290 skipping to change at line 295
$scount = @scountraw[0]; $scount = @scountraw[0];
$i = 0; $i = 0;
$sth = $dbh->prepare(q{SELECT * FROM stars}); $sth = $dbh->prepare(q{SELECT * FROM stars});
$sth->execute(); $sth->execute();
while (@stars = $sth->fetchrow_array()) { while (@stars = $sth->fetchrow_array()) {
$sid = $stars[0]; $sid = $stars[0];
$RA = $stars[1]; $RA = $stars[1];
$DE = $stars[2]; $DE = $stars[2];
$sname = $stars[3]; $sname = $stars[3];
$sdist = $stars[4]; $spropname = $stars[4];
$sstype = $stars[5]; $sdist = $stars[5];
$smass = $stars[6]; $sstype = $stars[6];
$smetal = $stars[7]; $smass = $stars[7];
$sVmag = $stars[8]; $smetal = $stars[8];
$sradius = $stars[9]; $sVmag = $stars[9];
$sefftemp = $stars[10]; $sradius = $stars[10];
$hasHabitPl = $stars[11]; $sefftemp = $stars[11];
$hasHabitPl = $stars[12];
$sname =~ s/^alpha/α/gi;
$sname =~ s/^alf/α/gi;
$sname =~ s/^beta/β/gi;
$sname =~ s/^gamma/γ/gi;
$sname =~ s/^delta/δ/gi;
$sname =~ s/^epsilon/ε/gi;
$sname =~ s/^eps/ε/gi;
$sname =~ s/^zeta/ζ/gi;
$sname =~ s/^theta/θ/gi;
$sname =~ s/^eta/η/gi;
$sname =~ s/^iota/ι/gi;
$sname =~ s/^kappa/κ/gi;
$sname =~ s/^lambda/λ/gi;
$sname =~ s/^mu/μ/gi;
$sname =~ s/^nu/ν/gi;
$sname =~ s/^xi/ξ/gi;
$sname =~ s/^ksi/ξ/gi;
$sname =~ s/^(omicron|omi)/ο/gi;
$sname =~ s/^pi/π/gi;
$sname =~ s/^rho/ρ/gi;
$sname =~ s/^(sigma|sig)/σ/gi;
$sname =~ s/^tau/τ/gi;
$sname =~ s/^upsilon/υ/gi;
$sname =~ s/^ups/υ/gi;
$sname =~ s/^phi/φ/gi;
$sname =~ s/^chi/χ/gi;
$sname =~ s/^psi/ψ/gi;
$sname =~ s/^omega/ω/gi;
$sname =~ s/^ome/ω/gi;
if ($sname eq "Kapteyn's") { if ($sname eq "Kapteyn's") {
$sname .= " Star"; # cosmetic fix for translation support $sname .= " Star"; # cosmetic fix for translation support
} }
$out = "\t\t\"".$sname."\":\n"; $out = "\t\t\"".$sname."\":\n";
$out .= "\t\t{\n"; $out .= "\t\t{\n";
$out .= "\t\t\t\"exoplanets\":\n"; $out .= "\t\t\t\"exoplanets\":\n";
$out .= "\t\t\t[\n"; $out .= "\t\t\t[\n";
skipping to change at line 319 skipping to change at line 355
$stp->execute($sid); $stp->execute($sid);
@pcountraw = $stp->fetchrow_array(); @pcountraw = $stp->fetchrow_array();
$pcount = @pcountraw[0]; $pcount = @pcountraw[0];
$j = 0; $j = 0;
$stp = $dbh->prepare(q{SELECT * FROM planets WHERE sid=?}); $stp = $dbh->prepare(q{SELECT * FROM planets WHERE sid=?});
$stp->execute($sid); $stp->execute($sid);
while(@planets = $stp->fetchrow_array()) { while(@planets = $stp->fetchrow_array()) {
$pid = $planets[0]; $pid = $planets[0];
$pname = $planets[2]; $pname = $planets[2];
$pmass = $planets[3]; $ppropname = $planets[3];
$pradius = $planets[4]; $pmass = $planets[4];
$pperiod = $planets[5]; $pradius = $planets[5];
$psemiax = $planets[6]; $pperiod = $planets[6];
$pecc = $planets[7]; $psemiax = $planets[7];
$pinc = $planets[8]; $pecc = $planets[8];
$angdist = $planets[9]; $pinc = $planets[9];
$discovered = $planets[10]; $angdist = $planets[10];
$hpltype = $planets[11]; $discovered = $planets[11];
$eqktemp = $planets[12]; $hpltype = $planets[12];
$esindex = $planets[13]; $eqktemp = $planets[13];
$esindex = $planets[14];
$out .= "\t\t\t{\n"; $out .= "\t\t\t{\n";
if ($pmass ne '') { if ($pmass ne '') {
$out .= "\t\t\t\t\"mass\": ".$pmass.",\n"; $out .= "\t\t\t\t\"mass\": ".$pmass.",\n";
} }
if ($pradius ne '') { if ($pradius ne '') {
$out .= "\t\t\t\t\"radius\": ".$pradius.",\n"; $out .= "\t\t\t\t\"radius\": ".$pradius.",\n";
} }
if ($pperiod ne '') { if ($pperiod ne '') {
$out .= "\t\t\t\t\"period\": ".$pperiod.",\n"; $out .= "\t\t\t\t\"period\": ".$pperiod.",\n";
skipping to change at line 365 skipping to change at line 402
} }
if ($hpltype ne '') { if ($hpltype ne '') {
$out .= "\t\t\t\t\"pclass\": \"".$hpltype."\",\n"; $out .= "\t\t\t\t\"pclass\": \"".$hpltype."\",\n";
} }
if ($eqktemp > 0) { if ($eqktemp > 0) {
$out .= "\t\t\t\t\"EqTemp\": ".$eqktemp.",\n"; $out .= "\t\t\t\t\"EqTemp\": ".$eqktemp.",\n";
} }
if ($esindex > 0) { if ($esindex > 0) {
$out .= "\t\t\t\t\"ESI\": ".$esindex.",\n"; $out .= "\t\t\t\t\"ESI\": ".$esindex.",\n";
} }
if ($ppropname ne '') {
$out .= "\t\t\t\t\"planetProperName\": \"".$ppropnam
e."\",\n";
}
if ($pname eq '') { if ($pname eq '') {
$pname = "a"; $pname = "a";
} }
$out .= "\t\t\t\t\"planetName\": \"".$pname."\"\n"; $out .= "\t\t\t\t\"planetName\": \"".$pname."\"\n";
$out .= "\t\t\t}"; $out .= "\t\t\t}";
$j += 1; $j += 1;
if ($j<$pcount) { if ($j<$pcount) {
$out .= ","; $out .= ",";
} }
$out .= "\n"; $out .= "\n";
skipping to change at line 402 skipping to change at line 442
} }
if ($sradius ne '') { if ($sradius ne '') {
$out .= "\t\t\t\"sradius\": ".$sradius.",\n"; $out .= "\t\t\t\"sradius\": ".$sradius.",\n";
} }
if ($sefftemp ne '') { if ($sefftemp ne '') {
$out .= "\t\t\t\"effectiveTemp\": ".$sefftemp.",\n"; $out .= "\t\t\t\"effectiveTemp\": ".$sefftemp.",\n";
} }
if ($hasHabitPl > 0) { if ($hasHabitPl > 0) {
$out .= "\t\t\t\"hasHP\": true,\n"; $out .= "\t\t\t\"hasHP\": true,\n";
} }
if ($spropname ne '') {
$out .= "\t\t\t\"starProperName\": \"".$spropname."\",\n";
}
$out .= "\t\t\t\"RA\": \"".$RA."\",\n"; $out .= "\t\t\t\"RA\": \"".$RA."\",\n";
$out .= "\t\t\t\"DE\": \"".$DE."\"\n"; $out .= "\t\t\t\"DE\": \"".$DE."\"\n";
$out .= "\t\t}"; $out .= "\t\t}";
$i += 1; $i += 1;
if ($i<$scount) { if ($i<$scount) {
$out .= ","; $out .= ",";
} }
print JSON $out."\n"; print JSON $out."\n";
 End of changes. 14 change blocks. 
55 lines changed or deleted 100 lines changed or added

This html diff was produced by rfcdiff 1.41. The latest version is available from http://tools.ietf.org/tools/rfcdiff/