</p>
</fieldset>
<fieldset>
<legend>Sources location</legend>
<table class="sources">
<tr>
<th>Name</th>
<th>Latitude</th>
<th>Longitude</th>
<th>Altitude</th>
<th>City</th>
<th>Country</th>
</tr>
<?php
require_once '../require/class.Connection.php';
if (Connection::tableExists('source_location')) {
require_once '../require/class.Source.php';
$alllocations = Source::getAllLocationInfo();
foreach ($alllocations as $location) {
?>
<tr>
<input type="hidden" name="source_id[]" value="<?php
print $location['id'];
?>
" />
<td><input type="text" name="source_name[]" value="<?php
print $location['name'];
?>
" /></td>
<td><input type="text" name="source_latitude[]" value="<?php
print $location['latitude'];
public static function check_version($update = false)
{
global $globalDBname;
$version = 0;
if (Connection::tableExists('aircraft')) {
if (!Connection::tableExists('config')) {
$version = '1';
if ($update) {
return self::update_from_1();
} else {
return $version;
}
} else {
$Connection = new Connection();
$query = "SELECT value FROM config WHERE name = 'schema_version' LIMIT 1";
try {
$sth = Connection::$db->prepare($query);
$sth->execute();
} catch (PDOException $e) {
return "error : " . $e->getMessage() . "\n";
}
$result = $sth->fetch(PDO::FETCH_ASSOC);
if ($update) {
if ($result['value'] == '2') {
$error = self::update_from_2();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} elseif ($result['value'] == '3') {
$error = self::update_from_3();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} elseif ($result['value'] == '4') {
$error = self::update_from_4();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} elseif ($result['value'] == '5') {
$error = self::update_from_5();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} elseif ($result['value'] == '6') {
$error = self::update_from_6();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} elseif ($result['value'] == '7') {
$error = self::update_from_7();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} elseif ($result['value'] == '8') {
$error = self::update_from_8();
if ($error != '') {
return $error;
} else {
return self::check_version(true);
}
} else {
return '';
}
} else {
return $result['value'];
}
}
} else {
return $version;
}
}
<fieldset>
<legend>Sources location</legend>
<table class="sources">
<tr>
<th>Name</th>
<th>Latitude</th>
<th>Longitude</th>
<th>Altitude</th>
<th>City</th>
<th>Country</th>
</tr>
<?php
require_once '../require/class.Connection.php';
$Connection = new Connection();
if ($Connection->db != NULL) {
if ($Connection->tableExists('source_location')) {
require_once '../require/class.Source.php';
$Source = new Source();
$alllocations = $Source->getAllLocationInfo();
foreach ($alllocations as $location) {
?>
<tr>
<input type="hidden" name="source_id[]" value="<?php
print $location['id'];
?>
" />
<td><input type="text" name="source_name[]" value="<?php
print $location['name'];
?>
" /></td>
<td><input type="text" name="source_latitude[]" value="<?php
/**
* Gets a list of all countries (airline, departure airport & arrival airport)
*
* @return Array list of countries
*
*/
public function getAllCountries()
{
$Connection = new Connection();
if ($Connection->tableExists('countries')) {
$country_array = array();
$query = "SELECT countries.name AS airport_country\n\t\t\t\t\t\t\t\tFROM countries\n\t\t\t\t\t\t\t\tORDER BY countries.name ASC";
$sth = $this->db->prepare($query);
$sth->execute();
$temp_array = array();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$temp_array['country'] = $row['airport_country'];
$country_array[$row['airport_country']] = $temp_array;
}
} else {
$query = "SELECT DISTINCT spotter_output.departure_airport_country AS airport_country\n\t\t\t\t\t\t\t\tFROM spotter_output\n\t\t\t\t\t\t\t\tWHERE spotter_output.departure_airport_country <> '' \n\t\t\t\t\t\t\t\tORDER BY spotter_output.departure_airport_country ASC";
$sth = $this->db->prepare($query);
$sth->execute();
$temp_array = array();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$temp_array['country'] = $row['airport_country'];
$country_array[$row['airport_country']] = $temp_array;
}
$query = "SELECT DISTINCT spotter_output.arrival_airport_country AS airport_country\n\t\t\t\t\t\t\t\tFROM spotter_output\n\t\t\t\t\t\t\t\tWHERE spotter_output.arrival_airport_country <> '' \n\t\t\t\t\t\t\t\tORDER BY spotter_output.arrival_airport_country ASC";
$sth = $this->db->prepare($query);
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
if ($country_array[$row['airport_country']]['country'] != $row['airport_country']) {
$temp_array['country'] = $row['airport_country'];
$country_array[$row['country']] = $temp_array;
}
}
$query = "SELECT DISTINCT spotter_output.airline_country AS airline_country\n\t\t\t\t\t\t\t\tFROM spotter_output \n\t\t\t\t\t\t\t\tWHERE spotter_output.airline_country <> '' \n\t\t\t\t\t\t\t\tORDER BY spotter_output.airline_country ASC";
$sth = $this->db->prepare($query);
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
if (isset($country_array[$row['airline_country']]['country']) && $country_array[$row['airline_country']]['country'] != $row['airline_country']) {
$temp_array['country'] = $row['airline_country'];
$country_array[$row['country']] = $temp_array;
}
}
}
return $country_array;
}
/**
* Executes the SQL statements to get the spotter information
*
* @param String $query the SQL query
* @param String $limit the limit query
* @return Array the spotter information
*
*/
public static function getDataFromDB($query, $params = array(), $limitQuery = '')
{
global $globalSquawkCountry, $globalIVAO;
if (!isset($globalIVAO)) {
$globalIVAO = FALSE;
}
date_default_timezone_set('UTC');
if (!is_string($query)) {
return false;
}
if ($limitQuery != "") {
if (!is_string($limitQuery)) {
return false;
}
}
$Connection = new Connection();
try {
$sth = Connection::$db->prepare($query . $limitQuery);
$sth->execute($params);
} catch (PDOException $e) {
printf("Invalid query : %s\nWhole query: %s\n", $e->getMessage(), $query . $limitQuery);
exit;
}
// $num_rows = count($sth->fetchAll());
$num_rows = 0;
$spotter_array = array();
$temp_array = array();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$num_rows++;
$temp_array = array();
if (isset($row['spotter_live_id'])) {
$temp_array['spotter_id'] = $row['spotter_live_id'];
} elseif (isset($row['spotter_archive_id'])) {
$temp_array['spotter_id'] = $row['spotter_archive_id'];
} else {
$temp_array['spotter_id'] = $row['spotter_id'];
}
$temp_array['flightaware_id'] = $row['flightaware_id'];
if (isset($row['modes'])) {
$temp_array['modes'] = $row['modes'];
}
$temp_array['ident'] = $row['ident'];
if (isset($row['registration']) && $row['registration'] != '') {
$temp_array['registration'] = $row['registration'];
} elseif (isset($temp_array['modes'])) {
$temp_array['registration'] = Spotter::getAircraftRegistrationBymodeS($temp_array['modes']);
} else {
$temp_array['registration'] = '';
}
$temp_array['aircraft_type'] = $row['aircraft_icao'];
$temp_array['departure_airport'] = $row['departure_airport_icao'];
$temp_array['arrival_airport'] = $row['arrival_airport_icao'];
$temp_array['latitude'] = $row['latitude'];
$temp_array['longitude'] = $row['longitude'];
if (Connection::tableExists('countries')) {
$country_info = Spotter::getCountryFromLatitudeLongitude($temp_array['latitude'], $temp_array['longitude']);
if (is_array($country_info) && isset($country_info['name']) && isset($country_info['iso2'])) {
$temp_array['country'] = $country_info['name'];
$temp_array['country_iso2'] = $country_info['iso2'];
}
}
$temp_array['waypoints'] = $row['waypoints'];
if (isset($row['route_stop'])) {
$temp_array['route_stop'] = $row['route_stop'];
if ($row['route_stop'] != '') {
$allroute = explode(' ', $row['route_stop']);
foreach ($allroute as $route) {
$route_airport_array = Spotter::getAllAirportInfo($route);
if (isset($route_airport_array[0]['name'])) {
$route_stop_details['airport_name'] = $route_airport_array[0]['name'];
$route_stop_details['airport_city'] = $route_airport_array[0]['city'];
$route_stop_details['airport_country'] = $route_airport_array[0]['country'];
$route_stop_details['airport_icao'] = $route_airport_array[0]['icao'];
$temp_array['route_stop_details'][] = $route_stop_details;
}
}
}
}
$temp_array['altitude'] = $row['altitude'];
$temp_array['heading'] = $row['heading'];
$heading_direction = Spotter::parseDirection($row['heading']);
if (isset($heading_direction[0]['direction_fullname'])) {
$temp_array['heading_name'] = $heading_direction[0]['direction_fullname'];
}
$temp_array['ground_speed'] = $row['ground_speed'];
$temp_array['image'] = "";
$temp_array['image_thumbnail'] = "";
$temp_array['image_source'] = "";
$temp_array['image_copyright'] = "";
if (isset($row['highlight'])) {
$temp_array['highlight'] = $row['highlight'];
} else {
$temp_array['highlight'] = '';
}
$dateArray = Spotter::parseDateString($row['date']);
if ($dateArray['seconds'] < 10) {
$temp_array['date'] = "a few seconds ago";
} elseif ($dateArray['seconds'] >= 5 && $dateArray['seconds'] < 30) {
$temp_array['date'] = "half a minute ago";
} elseif ($dateArray['seconds'] >= 30 && $dateArray['seconds'] < 60) {
$temp_array['date'] = "about a minute ago";
} elseif ($dateArray['minutes'] < 5) {
$temp_array['date'] = "a few minutes ago";
} elseif ($dateArray['minutes'] >= 5 && $dateArray['minutes'] < 60) {
$temp_array['date'] = "about " . $dateArray['minutes'] . " minutes ago";
} elseif ($dateArray['hours'] < 2) {
$temp_array['date'] = "about an hour ago";
} elseif ($dateArray['hours'] >= 2 && $dateArray['hours'] < 24) {
$temp_array['date'] = "about " . $dateArray['hours'] . " hours ago";
} else {
$temp_array['date'] = date("M j Y, g:i a", strtotime($row['date'] . " UTC"));
}
$temp_array['date_minutes_past'] = $dateArray['minutes'];
$temp_array['date_iso_8601'] = date("c", strtotime($row['date'] . " UTC"));
$temp_array['date_rfc_2822'] = date("r", strtotime($row['date'] . " UTC"));
$temp_array['date_unix'] = strtotime($row['date'] . " UTC");
if (isset($row['aircraft_name']) && $row['aircraft_name'] != '' && isset($row['aircraft_shadow']) && $row['aircraft_shadow'] != '') {
$temp_array['aircraft_name'] = $row['aircraft_name'];
$temp_array['aircraft_manufacturer'] = $row['aircraft_manufacturer'];
if (isset($row['aircraft_shadow'])) {
$temp_array['aircraft_shadow'] = $row['aircraft_shadow'];
}
} else {
$aircraft_array = Spotter::getAllAircraftInfo($row['aircraft_icao']);
if (count($aircraft_array) > 0) {
$temp_array['aircraft_name'] = $aircraft_array[0]['type'];
$temp_array['aircraft_manufacturer'] = $aircraft_array[0]['manufacturer'];
if ($aircraft_array[0]['aircraft_shadow'] != NULL) {
$temp_array['aircraft_shadow'] = $aircraft_array[0]['aircraft_shadow'];
} else {
$temp_array['aircraft_shadow'] = 'default.png';
}
} else {
$temp_array['aircraft_shadow'] = 'default.png';
$temp_array['aircraft_name'] = 'N/A';
}
}
$airline_array = array();
if (!is_numeric(substr($row['ident'], 0, 3))) {
if (is_numeric(substr(substr($row['ident'], 0, 3), -1, 1))) {
$airline_array = Spotter::getAllAirlineInfo(substr($row['ident'], 0, 2));
} elseif (is_numeric(substr(substr($row['ident'], 0, 4), -1, 1))) {
$airline_array = Spotter::getAllAirlineInfo(substr($row['ident'], 0, 3));
} else {
$airline_array = Spotter::getAllAirlineInfo('NA');
}
} else {
$airline_array = Spotter::getAllAirlineInfo('NA');
}
if (count($airline_array) > 0) {
$temp_array['airline_icao'] = $airline_array[0]['icao'];
$temp_array['airline_iata'] = $airline_array[0]['iata'];
$temp_array['airline_name'] = $airline_array[0]['name'];
$temp_array['airline_country'] = $airline_array[0]['country'];
$temp_array['airline_callsign'] = $airline_array[0]['callsign'];
$temp_array['airline_type'] = $airline_array[0]['type'];
}
if (isset($temp_array['airline_iata'])) {
$acars_array = ACARS::getLiveAcarsData($temp_array['airline_iata'] . substr($temp_array['ident'], 3));
//$acars_array = ACARS::getLiveAcarsData('BA40YL');
if (count($acars_array) > 0) {
$temp_array['acars'] = $acars_array;
//print_r($acars_array);
}
}
if ($temp_array['registration'] != "" || $globalIVAO && $temp_array['aircraft_type'] != '') {
if ($globalIVAO) {
if (isset($temp_array['airline_icao'])) {
$image_array = Image::getSpotterImage('', $temp_array['aircraft_type'], $temp_array['airline_icao']);
} else {
$image_array = Image::getSpotterImage('', $temp_array['aircraft_type']);
}
} else {
$image_array = Image::getSpotterImage($temp_array['registration']);
}
if (count($image_array) > 0) {
$temp_array['image'] = $image_array[0]['image'];
$temp_array['image_thumbnail'] = $image_array[0]['image_thumbnail'];
$temp_array['image_source'] = $image_array[0]['image_source'];
$temp_array['image_source_website'] = $image_array[0]['image_source_website'];
if ($temp_array['image_source_website'] == '' && $temp_array['image_source'] == 'planespotters') {
$planespotter_url_array = explode("_", $temp_array['image']);
$planespotter_id = str_replace(".jpg", "", $planespotter_url_array[1]);
$temp_array['image_source_website'] = 'http://www.planespotters.net/Aviation_Photos/photo.show?id=' . $planespotter_id;
}
$temp_array['image_copyright'] = $image_array[0]['image_copyright'];
}
}
if (!isset($globalIVAO) || !$globalIVAO) {
$schedule_array = Schedule::getSchedule($temp_array['ident']);
//print_r($schedule_array);
if (count($schedule_array) > 0) {
if ($schedule_array['departure_airport_icao'] != '') {
$row['departure_airport_icao'] = $schedule_array['departure_airport_icao'];
$temp_array['departure_airport'] = $row['departure_airport_icao'];
}
if ($schedule_array['arrival_airport_icao'] != '') {
$row['arrival_airport_icao'] = $schedule_array['arrival_airport_icao'];
$temp_array['arrival_airport'] = $row['arrival_airport_icao'];
}
$temp_array['departure_airport_time'] = $schedule_array['departure_airport_time'];
$temp_array['arrival_airport_time'] = $schedule_array['arrival_airport_time'];
}
}
if ($row['departure_airport_icao'] != '') {
$departure_airport_array = Spotter::getAllAirportInfo($row['departure_airport_icao']);
if (!isset($departure_airport_array[0]['name'])) {
$departure_airport_array = Spotter::getAllAirportInfo('NA');
}
} else {
$departure_airport_array = Spotter::getAllAirportInfo('NA');
}
if (isset($departure_airport_array[0]['name'])) {
$temp_array['departure_airport_name'] = $departure_airport_array[0]['name'];
$temp_array['departure_airport_city'] = $departure_airport_array[0]['city'];
$temp_array['departure_airport_country'] = $departure_airport_array[0]['country'];
$temp_array['departure_airport_iata'] = $departure_airport_array[0]['iata'];
$temp_array['departure_airport_icao'] = $departure_airport_array[0]['icao'];
$temp_array['departure_airport_latitude'] = $departure_airport_array[0]['latitude'];
$temp_array['departure_airport_longitude'] = $departure_airport_array[0]['longitude'];
$temp_array['departure_airport_altitude'] = $departure_airport_array[0]['altitude'];
}
/*
if (isset($row['departure_airport_time'])) {
$temp_array['departure_airport_time'] = $row['departure_airport_time'];
}
*/
if ($row['arrival_airport_icao'] != '') {
$arrival_airport_array = Spotter::getAllAirportInfo($row['arrival_airport_icao']);
if (count($arrival_airport_array) == 0) {
$arrival_airport_array = Spotter::getAllAirportInfo('NA');
}
} else {
$arrival_airport_array = Spotter::getAllAirportInfo('NA');
}
if (count($arrival_airport_array) > 0) {
$temp_array['arrival_airport_name'] = $arrival_airport_array[0]['name'];
$temp_array['arrival_airport_city'] = $arrival_airport_array[0]['city'];
$temp_array['arrival_airport_country'] = $arrival_airport_array[0]['country'];
$temp_array['arrival_airport_iata'] = $arrival_airport_array[0]['iata'];
$temp_array['arrival_airport_icao'] = $arrival_airport_array[0]['icao'];
$temp_array['arrival_airport_latitude'] = $arrival_airport_array[0]['latitude'];
$temp_array['arrival_airport_longitude'] = $arrival_airport_array[0]['longitude'];
$temp_array['arrival_airport_altitude'] = $arrival_airport_array[0]['altitude'];
}
/*
if (isset($row['arrival_airport_time'])) {
$temp_array['arrival_airport_time'] = $row['arrival_airport_time'];
}
*/
if (isset($row['pilot_id']) && $row['pilot_id'] != '') {
$temp_array['pilot_id'] = $row['pilot_id'];
}
if (isset($row['pilot_name']) && $row['pilot_name'] != '') {
$temp_array['pilot_name'] = $row['pilot_name'];
}
if (isset($row['squawk'])) {
$temp_array['squawk'] = $row['squawk'];
if ($row['squawk'] != '' && isset($temp_array['country_iso2'])) {
$temp_array['squawk_usage'] = Spotter::getSquawkUsage($row['squawk'], $temp_array['country_iso2']);
if ($temp_array['squawk_usage'] == '' && isset($globalSquawkCountry)) {
$temp_array['squawk_usage'] = Spotter::getSquawkUsage($row['squawk'], $globalSquawkCountry);
}
} elseif ($row['squawk'] != '' && isset($globalSquawkCountry)) {
$temp_array['squawk_usage'] = Spotter::getSquawkUsage($row['squawk'], $globalSquawkCountry);
}
}
$temp_array['query_number_rows'] = $num_rows;
$spotter_array[] = $temp_array;
}
if ($num_rows == 0) {
return array();
}
$spotter_array[0]['query_number_rows'] = $num_rows;
return $spotter_array;
}
<?php
require 'require/class.Connection.php';
require 'require/class.Spotter.php';
include_once 'require/libs/geoPHP/geoPHP.inc';
if (isset($_GET['download'])) {
header('Content-disposition: attachment; filename="airspace.geojson"');
}
header('Content-Type: text/javascript');
$Connection = new Connection();
if (!Connection::tableExists('airspace')) {
die;
}
if (isset($_GET['coord'])) {
$coords = explode(',', $_GET['coord']);
$query = "SELECT *, AsWKB(SHAPE) AS wkb FROM airspace WHERE ST_Intersects(SHAPE, envelope(linestring(point(:minlon,:minlat), point(:maxlon,:maxlat))))";
try {
$sth = Connection::$db->prepare($query);
$sth->execute(array(':minlon' => $coords[0], ':minlat' => $coords[1], ':maxlon' => $coords[2], ':maxlat' => $coords[3]));
} catch (PDOException $e) {
return "error";
}
} else {
$query = "SELECT *, AsWKB(SHAPE) AS wkb FROM airspace";
try {
$sth = Connection::$db->prepare($query);
$sth->execute();
} catch (PDOException $e) {
return "error";
}
}
<?php
require 'require/class.Connection.php';
include_once 'require/libs/geoPHP/geoPHP.inc';
if (isset($_GET['download'])) {
header('Content-disposition: attachment; filename="airspace.geojson"');
}
header('Content-Type: text/javascript');
$Connection = new Connection();
if (!$Connection->tableExists('airspace')) {
die;
}
if (isset($_GET['coord'])) {
$coords = explode(',', $_GET['coord']);
$query = "SELECT *, AsWKB(SHAPE) AS wkb FROM airspace WHERE ST_Intersects(SHAPE, envelope(linestring(point(:minlon,:minlat), point(:maxlon,:maxlat))))";
try {
$sth = $Connection->db->prepare($query);
$sth->execute(array(':minlon' => $coords[0], ':minlat' => $coords[1], ':maxlon' => $coords[2], ':maxlat' => $coords[3]));
} catch (PDOException $e) {
return "error";
}
} else {
$query = "SELECT *, AsWKB(SHAPE) AS wkb FROM airspace";
try {
$sth = $Connection->db->prepare($query);
$sth->execute();
} catch (PDOException $e) {
return "error";
}
}
function wkb_to_json($wkb)
