/** * Finds geographical features around the object's location * * Searches the geonames database for a specified number of features of the specified type within the specified * distance of the object's position ordered by either closest or furthest first. All search criteria may be * omitted to return an unrestrained (very large and not recommended) result set. * * @uses LatLng::convertMetersToDegreesLatitude * @uses LatLng::convertMetersToDegreesLongitude * @uses DatabaseHelper::getInstance * @uses DatabaseHelper::query * @uses DistantGeographicalFeature * * @param LatLng $position The co-ordinates from which the search should be based. * @param null|string $featureType The type of feature to be searched for. Options are 'landmark', 'city' * (all city sizes), 'majorCity', 'minorCity', NULL (all feature types). * Default: NULL. * @param null|int $searchRadius The search radius in meters from the object's position. Default = NULL. * @param null|int $noOfResults The number of results to return. Default = NULL. * @param string $order The order of the search results. Input options are 'asc' (closest first) * or 'desc' furthest first. Default = 'asc. * * @return GeographicalFeature[] * @throws ArgumentOutOfRangeException For featureType, searchRadius, order, noOfResults. * @throws InvalidOperationException For executing the method without setting the database credentials at the * class level. * @throws MyInvalidArgumentException For searchRadius, noOfResults. */ public static final function findFeatures(LatLng $position, $featureType = NULL, $searchRadius = NULL, $noOfResults = NULL, $order = 'asc') { if (is_null(self::$databaseCredentials)) { throw new InvalidOperationException('Database credentials must be set at the class level to allow this action to take place.'); } // Validates user input and builds the syntax relevant to the type of feature to search for. switch (strtolower($featureType)) { case 'landmark': $featureTypeSyntax = " feature_class != 'P' "; break; case 'city': $featureTypeSyntax = " feature_class = 'P' "; break; case 'majorcity': $featureTypeSyntax = " feature_class = 'P' AND population = 1 "; break; case 'minorcity': $featureTypeSyntax = " feature_class = 'P' AND population = 0 "; break; case NULL: $featureTypeSyntax = ""; break; default: throw new ArgumentOutOfRangeException("INPUT: featureType. The supplied value type is invalid. Either 'landmark', 'city', 'majorCity',\n 'minorCity', or NULL expected.", $featureType); break; } // Validates user input then builds the syntax to set a search radius. if (isset($searchRadius)) { if (!is_numeric($searchRadius)) { throw new MyInvalidArgumentException("INPUT: searchRadius. The supplied value type is invalid. A numeric value is expected."); } if ($searchRadius <= 0) { throw new ArgumentOutOfRangeException('INPUT: searchRadius. The supplied value is out of acceptable range. >0 expected.', $searchRadius); } $maxFeatureSearchDistanceInDegreesLatitude = LatLng::convertMetersToDegreesLatitude($searchRadius, $position->getLatitude()); $maxFeatureSearchDistanceInDegreesLongitude = LatLng::convertMetersToDegreesLongitude($searchRadius, $position->getLatitude()); $searchRadiusSyntax = <<<MYSQL latitude BETWEEN :latitude - {$maxFeatureSearchDistanceInDegreesLatitude} AND :latitude + {$maxFeatureSearchDistanceInDegreesLatitude} AND longitude BETWEEN :longitude - {$maxFeatureSearchDistanceInDegreesLongitude} AND :longitude + {$maxFeatureSearchDistanceInDegreesLongitude} MYSQL; $havingSyntax = " HAVING distance < {$searchRadius} "; } else { $searchRadiusSyntax = ''; $havingSyntax = ''; } // Assembles the syntax for the WHERE clause combining feature and radius components built above. if ($featureTypeSyntax || $searchRadiusSyntax) { $whereSyntax = ' WHERE '; $whereSyntax .= $featureTypeSyntax; if ($featureTypeSyntax && $searchRadiusSyntax) { $whereSyntax .= ' AND '; } $whereSyntax .= $searchRadiusSyntax; } else { $whereSyntax = ''; } // Validates user input and builds the syntax to set the result order switch (strtolower($order)) { case 'asc': $orderSyntax = ' ASC '; break; case 'desc': $orderSyntax = ' DESC '; break; default: throw new ArgumentOutOfRangeException("INPUT: order. The supplied value is invalid. Order can be either 'asc' or 'desc'", $order); } // Validates user input and builds the syntax to limit the number of results. if (isset($noOfResults)) { if (is_numeric($noOfResults)) { if ($noOfResults > 0) { $limitSyntax = " LIMIT {$noOfResults}"; } else { throw new ArgumentOutOfRangeException("INPUT: noOfResults. The supplied value is invalid. Must be 1 or higher.", $noOfResults); } } else { throw new MyInvalidArgumentException("INPUT: noOfResults. The supplied value type is invalid. A numeric value is expected."); } } else { $limitSyntax = ''; } // Assemble the final query from literal and dynamic components (built and assembled above based on user // criteria). $findFeaturesQuery = <<<MYSQL SELECT f.latitude, f.longitude, f.name AS name, f.feature_class AS featureClass, ft.feature_type AS featureType, c.county_name AS county, s.state_abbreviation AS state, ROUND(6378137 * ACOS(COS(RADIANS(:latitude)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(:longitude)) + SIN(RADIANS(:latitude)) * SIN(RADIANS(latitude)))) AS distance FROM features f LEFT JOIN feature_types ft ON f.feature_type_id = ft.feature_type_id LEFT JOIN counties c ON f.county_id = c.county_id AND f.state_id = c.state_id LEFT JOIN states s ON f.state_id = s.state_id {$whereSyntax} {$havingSyntax} ORDER BY distance {$orderSyntax} {$limitSyntax} MYSQL; // The query parameters $findFeaturesParams = array('latitude' => $position->getLatitude(), 'longitude' => $position->getLongitude()); // Connect to the database, run the query, build and return an array of DistantGeographicalFeature objects. $DBH = call_user_func_array('DatabaseHelper::getInstance', self::$databaseCredentials); $findFeaturesResult = DatabaseHelper::query($DBH, $findFeaturesQuery, $findFeaturesParams); $features = array(); while ($feature = $findFeaturesResult->fetch(PDO::FETCH_ASSOC)) { try { if ($feature['featureClass'] == 'P') { $city = $feature['name']; } else { $featurePosition = new LatLng($feature['latitude'], $feature['longitude']); $cityArray = self::findFeatures($geonamesCredentials, $featurePosition, 'majorCity', $searchRadius, 1, 'asc'); if ($cityArray) { $city = $cityArray[0]->getCity(); } else { $city = NULL; } } $features[] = new GeographicalFeature(new LatLng($feature['latitude'], $feature['longitude']), $feature['name'], $feature['featureType'], $city, $feature['county'], $feature['state']); } catch (Exception $e) { continue; } } return $features; }
dune_id AS dId, latitude AS la, longitude AS lo, toe, crest FROM dunes WHERE site_id = :selectedSiteId MYSQL; $duneParams['selectedSiteId'] = $siteId; $duneResult = DatabaseHelper::query($DBH, $duneQuery, $duneParams); $dunes = $duneResult->fetchAll(PDO::FETCH_ASSOC); $duneCount = count($dunes); for ($i = 0; $i < $duneCount; $i++) { $latitudeBoundary = LatLng::convertMetersToDegreesLatitude(250, $dunes[$i]['la']); $longitudeBoundary = LatLng::convertMetersToDegreesLongitude(250, $dunes[$i]['lo']); $closestTimeSeriesQuery = <<<MYSQL SELECT time_series_id, ROUND(6378137 * ACOS(COS(RADIANS(:latitude)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(:longitude)) + SIN(RADIANS(:latitude)) * SIN(RADIANS(latitude)))) AS distance FROM twl_time_series WHERE latitude BETWEEN :latitudeSBoundary AND :latitudeNBoundary AND