exoplanets.pl   exoplanets.pl 
skipping to change at line 29 skipping to change at line 29
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL T HE # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL T HE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING F ROM, # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING F ROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE. # SOFTWARE.
# #
use DBI(); use DBI();
use LWP::UserAgent(); use LWP::UserAgent();
use Text::CSV;
# #
# 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";
$HCSV = "./";
$JSON = "./exoplanets.json"; $JSON = "./exoplanets.json";
$HCSV = "./habitable.csv";
$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 0.4; http:// stellarium.org/)"); $UA->agent("Mozilla/5.0 (Stellarium Exoplanets Catalog Updater 1.2; 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 {
print "Can't connect to URL: $URL\n"; print "Can't connect to URL: $URL\n";
exit; # exit;
} }
$dsn = "DBI:mysql:database=$dbname;host=$dbhost"; $dsn = "DBI:mysql:database=$dbname;host=$dbhost";
$csvdata = Text::CSV->new();
open (HCSV, "<$HCSV");
@habitable = <HCSV>;
close HCSV;
%hs = ();
%hp = ();
for ($i=1;$i<scalar(@habitable);$i++) {
$status = $csvdata->parse($habitable[$i]);
@hdata = $csvdata->fields();
%hs = (%hs, $hdata[0], 1);
%hp = (%hp, $hdata[0]." ".$hdata[1], $habitable[$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->execute();
@ipcnt = $sth->fetchrow_array();
$initCnt = @ipcnt[0];
$sth = $dbh->do(q{TRUNCATE planets}); $sth = $dbh->do(q{TRUNCATE planets});
for ($i=1;$i<scalar(@catalog);$i++) { for ($i=1;$i<scalar(@catalog);$i++) {
$currdata = $catalog[$i]; $currdata = $catalog[$i];
$currdata =~ s/\".*?\"//gi; $currdata =~ s/nan//gi;
$currdata =~ s/\r//gi;
$currdata =~ s/\n//gi;
@cpname = (); $status = $csvdata->parse($currdata);
(@cpname) = split(",",$currdata); @psdata = ();
@psdata = $csvdata->fields();
@cfname = (); @cfname = ();
@cfname = split(" ",$cpname[0]); @cfname = split(" ",$psdata[0]);
if (scalar(@cfname)==4) { if (scalar(@cfname)==4) {
$csname = $cfname[0]." ".$cfname[1]." ".$cfname[2]; $csname = $cfname[0]." ".$cfname[1]." ".$cfname[2];
$pname = $cfname[3]; $pname = $cfname[3];
} elsif (scalar(@cfname)==3) { } elsif (scalar(@cfname)==3) {
$csname = $cfname[0]." ".$cfname[1]; $csname = $cfname[0]." ".$cfname[1];
$pname = $cfname[2]; $pname = $cfname[2];
} else { } else {
$csname = $cfname[0]; $csname = $cfname[0];
$pname = $cfname[1]; $pname = $cfname[1];
} }
($aname,$pmass,$pradius,$pperiod,$psemiax,$pecc,$pincl,$angdist,$psl $pmass = $psdata[1]; # planet mass
,$discovered,$updated,$pomega,$pt,$dtype,$mol,$starname,$sRA,$sDec,$sVmag,$ $pradius = $psdata[4]; # planet radius
sImag,$sHmag,$sJmag,$sKmag,$sdist,$smetal,$smass,$sradius,$sstype,$sage,$se $pperiod = $psdata[7]; # planet period
fftemp) = split(",", $currdata); $paxis = $psdata[10]; # planet axis
$pecc = $psdata[13]; # planet eccentricity
$pincl = $psdata[21]; # planet inclination
$angdist = $psdata[16]; # planet angular distance
$discovered = $psdata[37]; # planet discovered
$starname = $psdata[48]; # star name
$sRA = $psdata[49]; # star RA
$sDec = $psdata[50]; # star dec
$sVmag = $psdata[51]; # star v magnitude
$sdist = $psdata[56]; # star distance
$smetal = $psdata[57]; # star metallicity
$smass = $psdata[58]; # star mass
$sradius = $psdata[59]; # star radius
$sstype = $psdata[60]; # star spectral type
$sefftemp = $psdata[62]; # star effective temperature
$part = $sRA/15;
$hour = int($part);
$mint = int(($part-$hour)*60);
$sect = int((($part-$hour)*3600-60*$mint)*10)/10;
$deg = int($sDec);
$min = int(($sDec-$deg)*60);
$sec = int((($sDec-$deg)*3600-60*$min)*10)/10;
($hour,$mint,$sect) = split(":",$sRA);
($deg,$min,$sec) = split(":",$sDec);
# fixed bug in raw data
$sec =~ s/-//gi;
# fixed bug for Kepler-68 # fixed bug for Kepler-68
if ($starname =~ m/kepler-68/gi) { if ($starname =~ m/kepler-68/gi) {
$hour = 19; $hour = 19;
} }
# fixed bug for omi CrB # fixed bug for omi CrB
if ($starname =~ m/omi\s+CrB/gi) { if ($starname =~ m/omi\s+CrB/gi) {
$hour = 15; $mint = 20; $sect = 8.4; $hour = 15; $mint = 20; $sect = 8.4;
$deg = 29; $min = 36; $sec = 57.9; $deg = 29; $min = 36; $sec = 57.9;
} }
$sec =~ s/-//gi; $outRA = $hour."h".abs($mint)."m".abs($sect)."s";
$sect =~ s/-//gi; $outDE = $deg."d".abs($min)."m".abs($sec)."s";
$outRA = $hour."h".$mint."m".$sect."s";
$outDE = $deg."d".$min."m".$sec."s";
$sname = $starname; $sname = $starname;
$sname =~ s/^alpha/α/gi; $sname =~ s/^alpha/α/gi;
$sname =~ s/^alf/α/gi; $sname =~ s/^alf/α/gi;
$sname =~ s/^beta/β/gi; $sname =~ s/^beta/β/gi;
$sname =~ s/^gamma/γ/gi; $sname =~ s/^gamma/γ/gi;
$sname =~ s/^delta/δ/gi; $sname =~ s/^delta/δ/gi;
$sname =~ s/^epsilon/ε/gi; $sname =~ s/^epsilon/ε/gi;
$sname =~ s/^eps/ε/gi; $sname =~ s/^eps/ε/gi;
skipping to change at line 141 skipping to change at line 181
$sname =~ s/^iota/ι/gi; $sname =~ s/^iota/ι/gi;
$sname =~ s/^kappa/κ/gi; $sname =~ s/^kappa/κ/gi;
$sname =~ s/^lambda/λ/gi; $sname =~ s/^lambda/λ/gi;
$sname =~ s/^mu/μ/gi; $sname =~ s/^mu/μ/gi;
$sname =~ s/^nu/ν/gi; $sname =~ s/^nu/ν/gi;
$sname =~ s/^xi/ξ/gi; $sname =~ s/^xi/ξ/gi;
$sname =~ s/^ksi/ξ/gi; $sname =~ s/^ksi/ξ/gi;
$sname =~ s/^(omicron|omi)/ο/gi; $sname =~ s/^(omicron|omi)/ο/gi;
$sname =~ s/^pi/π/gi; $sname =~ s/^pi/π/gi;
$sname =~ s/^rho/ρ/gi; $sname =~ s/^rho/ρ/gi;
$sname =~ s/^sigma/σ/gi; $sname =~ s/^(sigma|sig)/σ/gi;
$sname =~ s/^tau/τ/gi; $sname =~ s/^tau/τ/gi;
$sname =~ s/^upsilon/υ/gi; $sname =~ s/^upsilon/υ/gi;
$sname =~ s/^ups/υ/gi; $sname =~ s/^ups/υ/gi;
$sname =~ s/^phi/φ/gi; $sname =~ s/^phi/φ/gi;
$sname =~ s/^chi/χ/gi; $sname =~ s/^chi/χ/gi;
$sname =~ s/^psi/ψ/gi; $sname =~ s/^psi/ψ/gi;
$sname =~ s/^omega/ω/gi; $sname =~ s/^omega/ω/gi;
$sname =~ s/^ome/ω/gi; $sname =~ s/^ome/ω/gi;
if (($sRA ne '00:00:00.0') && ($sDec ne '+00:00:00.0') && ($sname ne '')) { if (($sRA != 0.0) && ($sDec != 0.0) && ($sname ne '')) {
# check star # check star
$sth = $dbh->prepare(q{SELECT sid 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];
} else { } else {
$HPflag = 0;
if (exists($hs{$sname})) {
$HPflag = 1;
}
# insert star data # insert star data
$sth = $dbh->do(q{INSERT INTO stars (ra_coord,dec_co $sth = $dbh->do(q{INSERT INTO stars (ra_coord,dec_co
ord,sname,distance,stype,smass,smetal,vmag,sradius,sefftemp) VALUES (?,?,?, ord,sname,distance,stype,smass,smetal,vmag,sradius,sefftemp,has_habit_plane
?,?,?,?,?,?,?)}, undef, $outRA, $outDE, $sname, $sdist, $sstype, $smass, $s t) VALUES (?,?,?,?,?,?,?,?,?,?,?)}, undef, $outRA, $outDE, $sname, $sdist,
metal, $sVmag, $sradius, $sefftemp); $sstype, $smass, $smetal, $sVmag, $sradius, $sefftemp, $HPflag);
$sth = $dbh->prepare(q{SELECT sid FROM stars ORDER B $sth = $dbh->prepare(q{SELECT sid,sname FROM stars O
Y sid DESC LIMIT 0,1}); 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];
}
$hclass = '';
$mstemp = -1;
$esi = -1;
$key = $starName." ".$pname;
if (exists($hp{$key})) {
$status = $csvdata->parse($hp{$key});
($hsname,$hpname,$hclass,$mstemp,$esi) = $csvdata->f
ields();
} }
# insert planet data # insert planet data
$sth = $dbh->do(q{INSERT INTO planets (sid,pname,pmass,pradi us,pperiod,psemiaxis,pecc,pinc,padistance,discovered) VALUES (?,?,?,?,?,?,? ,?,?,?)}, undef, $starID, $pname, $pmass, $pradius, $pperiod, $psemiax, $pe cc, $pincl, $angdist, $discovered); $sth = $dbh->do(q{INSERT INTO planets (sid,pname,pmass,pradi us,pperiod,psemiaxis,pecc,pinc,padistance,discovered,hclass,mstemp,esi) VAL UES (?,?,?,?,?,?,?,?,?,?,?,?,?)}, undef, $starID, $pname, $pmass, $pradius, $pperiod, $paxis, $pecc, $pincl, $angdist, $discovered, $hclass, $mstemp, $esi);
} }
# else
# {
# 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";
print JSON "\t\"shortName\": \"A catalogue of stars with exoplanets\",\n"; print JSON "\t\"shortName\": \"A catalogue of stars with exoplanets\",\n";
print JSON "\t\"stars\":\n"; print JSON "\t\"stars\":\n";
print JSON "\t{\n"; print JSON "\t{\n";
$sth = $dbh->prepare(q{SELECT COUNT(sid) FROM stars}); $sth = $dbh->prepare(q{SELECT COUNT(sid) FROM stars});
skipping to change at line 200 skipping to change at line 260
$RA = $stars[1]; $RA = $stars[1];
$DE = $stars[2]; $DE = $stars[2];
$sname = $stars[3]; $sname = $stars[3];
$sdist = $stars[4]; $sdist = $stars[4];
$sstype = $stars[5]; $sstype = $stars[5];
$smass = $stars[6]; $smass = $stars[6];
$smetal = $stars[7]; $smetal = $stars[7];
$sVmag = $stars[8]; $sVmag = $stars[8];
$sradius = $stars[9]; $sradius = $stars[9];
$sefftemp = $stars[10]; $sefftemp = $stars[10];
$hasHabitPl = $stars[11];
$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";
$stp = $dbh->prepare(q{SELECT COUNT(pid) FROM planets WHERE sid=?}); $stp = $dbh->prepare(q{SELECT COUNT(pid) FROM planets WHERE sid=?});
$stp->execute($sid); $stp->execute($sid);
@pcountraw = $stp->fetchrow_array(); @pcountraw = $stp->fetchrow_array();
$pcount = @pcountraw[0]; $pcount = @pcountraw[0];
skipping to change at line 225 skipping to change at line 286
$pid = $planets[0]; $pid = $planets[0];
$pname = $planets[2]; $pname = $planets[2];
$pmass = $planets[3]; $pmass = $planets[3];
$pradius = $planets[4]; $pradius = $planets[4];
$pperiod = $planets[5]; $pperiod = $planets[5];
$psemiax = $planets[6]; $psemiax = $planets[6];
$pecc = $planets[7]; $pecc = $planets[7];
$pinc = $planets[8]; $pinc = $planets[8];
$angdist = $planets[9]; $angdist = $planets[9];
$discovered = $planets[10]; $discovered = $planets[10];
$habitclass = $planets[11];
$meanstemp = $planets[12];
$esindex = $planets[13];
$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 251 skipping to change at line 315
} }
if ($pinc ne '') { if ($pinc ne '') {
$out .= "\t\t\t\t\"inclination\": ".$pinc.",\n"; $out .= "\t\t\t\t\"inclination\": ".$pinc.",\n";
} }
if ($angdist ne '') { if ($angdist ne '') {
$out .= "\t\t\t\t\"angleDistance\": ".$angdist.",\n" ; $out .= "\t\t\t\t\"angleDistance\": ".$angdist.",\n" ;
} }
if ($discovered ne '') { if ($discovered ne '') {
$out .= "\t\t\t\t\"discovered\": ".$discovered.",\n" ; $out .= "\t\t\t\t\"discovered\": ".$discovered.",\n" ;
} }
if ($habitclass ne '') {
$out .= "\t\t\t\t\"hclass\": \"".$habitclass."\",\n"
;
}
if ($meanstemp > 0) {
$out .= "\t\t\t\t\"MSTemp\": ".$meanstemp.",\n";
}
if ($esindex > 0) {
$out .= "\t\t\t\t\"ESI\": ".$esindex.",\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 285 skipping to change at line 358
} }
if ($sVmag ne '') { if ($sVmag ne '') {
$out .= "\t\t\t\"Vmag\": ".$sVmag.",\n"; $out .= "\t\t\t\"Vmag\": ".$sVmag.",\n";
} }
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) {
$out .= "\t\t\t\"hasHP\": true,\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";
} }
print JSON "\t}\n}"; print JSON "\t}\n}\n";
close JSON; close JSON;
$sth = $dbh->prepare(q{SELECT COUNT(pid) FROM planets});
$sth->execute();
@ipcnt = $sth->fetchrow_array();
$lastCnt = @ipcnt[0];
open (COUNTD, ">$CNT");
print COUNTD $lastCnt-$initCnt;
close COUNTD;
# LOG
print "Planets in DB (Old/New): ".$initCnt."/".$lastCnt."\n";
 End of changes. 28 change blocks. 
36 lines changed or deleted 110 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/