Skip to content

Instantly share code, notes, and snippets.

@blindp
Created April 25, 2018 21:48
Show Gist options
  • Save blindp/76acf0ef91c51eb404a07a5778977628 to your computer and use it in GitHub Desktop.
Save blindp/76acf0ef91c51eb404a07a5778977628 to your computer and use it in GitHub Desktop.
mysql> show columns from kurs;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| next | int(10) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
mysql> show columns from marina;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nazev | varchar(30) | YES | | NULL | |
| lat | decimal(10,8) | YES | | NULL | |
| lon | decimal(11,8) | YES | | NULL | |
+-------+---------------+------+-----+---------+----------------+
mysql> show columns from spot;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| ts | int(11) | YES | | NULL | |
| lat | decimal(10,8) | YES | | NULL | |
| lon | decimal(11,8) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
#!/usr/bin/perl
#import souradnic systemu SPOT
#Blid Pew pro LaGrace
#
use XML::Simple;
use Data::Dumper;
use DBI;
#######################################################
$databaze =
DBI->connect("DBI:mysql:blindp:localhost",
"user_name", "pass")
or die "Nelze otevrit databazi:",DBI->errstr, "\n";
#######################################################
#vzdalenost mezi souradnicemi
sub distance {
my ($lat_lg, $lon_lg, $lat_next, $lon_next) = @_;
my ($clat) = $lat_lg*(3.14159/180);
my ($dlat) = ($lat_lg-$lat_next)*60;
my ($dlon) = ($lon_lg-$lon_next)*60;
my ($nm) = sqrt(($dlat*$dlat)+($dlon*$dlon)*((cos($clat))*(cos($clat))));
return($nm);
}
#nacteni celeho zaznamu
$simple = XML::Simple->new( );
$feed = $simple->XMLin ("message.xml", KeyAttr => [ ]);
#posledni casove razitko aktualniho feedu
$posledni_znacka_akt = $feed->{'feedMessageResponse'}->{'messages'}->{'message'}[0]->{'unixTime'};
#sirka, delka posledni pozice
$lat_last = $feed->{'feedMessageResponse'}->{'messages'}->{'message'}[0]->{'latitude'};
$lon_last = $feed->{'feedMessageResponse'}->{'messages'}->{'message'}[0]->{'longitude'};
#nacteni casoveho razitka posledniho zaznamu
open(POSLEDNI_MIN ,"posledni.bod")
or die "Nemohu otevrit soubor s poslednim zaznamem\n";
$posledni_znacka_min = <POSLEDNI_MIN>;
close(POSLEDNI_MIN);
open(LOG,">>lagrace.log")
or die "Nemohu otevrit lagrace.log\n";
#otoci seznam od nejstarsi po nejmladsi a projde vsechny zaznamy
for $x (reverse @{$feed->{'feedMessageResponse'}->{'messages'}->{'message'}}) {
#vynechani uz jednou ulozenych zaznamu
$aktualni_znacka = $x->{'unixTime'};
#print $x->{'dateTime'},"\n";
#nacteni dne a mesice z posledniho zaznamu
if ($aktualni_znacka == $posledni_znacka_min) {
$den_l = substr($x->{'dateTime'},8,2);
$mesic_l = substr($x->{'dateTime'},5,2);
}
if ($aktualni_znacka > $posledni_znacka_min) {
#urceni dne a mesice pro rozliseni dalsiho dne na mori
#print $x->{'dateTime'},"\n";
$den = substr($x->{'dateTime'},8,2);
$mesic = substr($x->{'dateTime'},5,2);
#vystup
#print LOG $x->{'longitude'},",";
#print LOG &x->{'latitude'}, ",0\n";
#zapis do databaze
$databaze->do("INSERT INTO spot (ts, lat, lon) VALUES($x->{'unixTime'},$x->{'latitude'},$x->{'longitude'})");
if (($den > $den_l) or ($mesic > $mesic_l)) {
print LOG "#".$den."-".$mesic."\n";
$den_l = $den;
$mesic_l = $mesic;
}
print LOG $x->{'longitude'}, ",";
print LOG $x->{'latitude'}, ",0\n";
$importovano++;
}
}
#ulozeni casoveho razitka posledniho zaznamu do souboru
open(POSLEDNI,">posledni.bod")
or die "Nemohu otevrit soubor s poslednim zaznamem\n";
print POSLEDNI $posledni_znacka_akt;
close(POSLEDNI);
close(LOG);
#print "\nUloženo - ".$importovano." záznamů\n\n";
#zastavila lg na planovane zastavce?
$dotaz = $databaze->prepare("select kurs.id, lat, lon, nazev from kurs join marina on kurs.next = marina.id order by kurs.id limit 1");
$dotaz->execute();
if($dotaz->rows > 0) {
@odpoved = $dotaz->fetchrow_array;
print "\n";
print "Akt sirka: $lat_last\n";
print "Akt delka: $lon_last\n";
print "\n";
print "Dalsi zastavka\n";
print "ID: $odpoved[0]\n";
print "Sirka: $odpoved[1]\n";
print "Delka: $odpoved[2]\n";
print "Nazev: $odpoved[3]\n";
print "\n";
$do_cile = &distance($lat_last, $lon_last, $odpoved[1], $odpoved[2]);
print "Do cile zbyva: $do_cile NM\n";
if($do_cile < 1.5) {
#smazat zaznam v db
$databaze->do("delete from kurs where id = $odpoved[0]");
}
}
else {
print "Tabulka pristich zastavek je prazdna!\n";
}
$dotaz->finish();
$databaze->disconnect();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment