/**
* Detect timezone of coords
* @author zbrown
*
* @param ApiRequestObject $apiRequest
* @return bool
* @throws \Doctrine\DBAL\DBALException
*/
public function detectTimezone(ApiRequestObject $apiRequest)
{
$qb = $this->getDoctrine()->getEntityManager()->getConnection();
$q = $qb->prepare('SELECT name, type, icao_code, iata_code, X(GeomFromText(coordinates_wkt)) AS latitude, Y(GeomFromText(coordinates_wkt)) AS longitude, SQRT( POW(69.1 * (X(GeomFromText(coordinates_wkt)) - :lat), 2) + POW(69.1 * (:lon - Y(GeomFromText(coordinates_wkt))) * COS(X(GeomFromText(coordinates_wkt)) / 57.3), 2)) AS distance FROM airports HAVING distance < :max ORDER BY distance ASC LIMIT ' . $apiRequest->getLimit());
$q->bindValue('lat', $apiRequest->getLatitude());
$q->bindValue('lon', $apiRequest->getLongitude());
$q->bindValue('max', $apiRequest->getMaximum());
$q->execute();
$result = $q->fetchAll();
if (empty($result)) {
return 'Unable to detect timezone.';
} else {
return $result;
}
}
/**
* Select airport data within constraints
* @author zbrown
*
* @param $lat
* @param $lon
* @param $limit
* @param $max
* @return bool
* @throws \Doctrine\DBAL\DBALException
*/
private function findNearAirport(ApiRequestObject $apiRequest)
{
$latitude = $apiRequest->getLatitude();
$longitude = $apiRequest->getLongitude();
$limit = $apiRequest->getLimit();
$max = $apiRequest->getMaximum();
$qb = $this->getDoctrine()->getEntityManager()->getConnection();
$q = $qb->prepare('SELECT name, type, icao_code, iata_code, X(GeomFromText(coordinates_wkt)) AS latitude, Y(GeomFromText(coordinates_wkt)) AS longitude, SQRT( POW(69.1 * (X(GeomFromText(coordinates_wkt)) - :lat), 2) + POW(69.1 * (:lon - Y(GeomFromText(coordinates_wkt))) * COS(X(GeomFromText(coordinates_wkt)) / 57.3), 2)) AS distance FROM airports HAVING distance < :max ORDER BY distance ASC LIMIT ' . $limit);
$q->bindValue('lat', $latitude);
$q->bindValue('lon', $longitude);
$q->bindValue('max', $max);
return $q->execute();
}
/**
* Find lakes near given cordinate set
* @author zbrown
*
* @param ApiRequestObject $apiRequest
* @return bool
* @throws \Doctrine\DBAL\DBALException
*/
public function findNearLake(ApiRequestObject $apiRequest)
{
$qb = $this->getDoctrine()->getEntityManager()->getConnection();
$q = $qb->prepare('SELECT name, name_alt, dam_name, X(GeomFromText(coordinates_wkt)) AS latitude, Y(GeomFromText(coordinates_wkt)) AS longitude, SQRT( POW(69.1 * (X(GeomFromText(coordinates_wkt)) - :lat), 2) + POW(69.1 * (:lon - Y(GeomFromText(coordinates_wkt))) * COS(X(GeomFromText(coordinates_wkt)) / 57.3), 2)) AS distance FROM lakes HAVING distance < :max ORDER BY distance ASC LIMIT ' . $apiRequest->getLimit());
$q->bindValue('lat', $apiRequest->getLatitude());
$q->bindValue('lon', $apiRequest->getLongitude());
$q->bindValue('max', $apiRequest->getMaximum());
return $q->execute();
}
