Created
April 25, 2018 21:48
-
-
Save blindp/76acf0ef91c51eb404a07a5778977628 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | | | |
+-------+---------------+------+-----+---------+-------+ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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