troubleshooting Question

Filter MySQL stored coordinates to best in 24 hour cycle using PHP

Avatar of williamskellorn
williamskellorn asked on
PHPMySQL ServerJavaScript
19 Comments2 Solutions749 ViewsLast Modified:
I have a database of messages received from a number of satellite-transmitter tagged birds on migration.

Amongst other data, the key columns in the table are ptt (a unique number to identify each tag/bird), location_class (a value that indicates the quality / accuracy of the message, in ascending order of quality the possible values are Z,B,A,0,1,2,3), location_date ( a unix timestamp for the message) and latitude and longitude.

I am currently using PHP to make a MySQL query to retrieve position coordinates and timestamps for each message with a location class of 1, 2 or 3 to populate JS gmap coordinate and timestamp arrays and render and animate routes via Googlemaps.

I would like to further limit the number of coordinate / timestamp pairs returned for each ptt by filtering to a single pair of coordinates and timestamp for each 24 hour cycle (which can start with the value of the first timestamp returned for each bird), choosing the message with the best location_class and latest timestamp during that cycle.

The aim is to reduce the amount of 'noise' in the map routes (and improve loading time) by reducing the amount of data going into the JS arrays.

I am unsure how best to go about this - my SQL knowledge is limited - whether it is possible to do this as part of the SQL query or whether I need to do an intermediate operation in PHP after retrieving the query to slim down the coordinates and timestamps. The PHP will be cached, so I am not concerned about performance on the server side.

A simplified snippet (below and attached) shows the current code to produce the JS arrays.

<?php
//set default timezone for date conversion
date_default_timezone_set('UTC');

$connection = mysql_connect ("localhost", "user", "pass");
$db_selected = mysql_select_db("birds", $connection);

// get a unique list of all birds
$get_ptts = "
SELECT distinct ptt from bird_data 
ORDER BY ptt";
$result=mysql_query($get_ptts) or die ("Could not retrieve unique PTT numbers: ".mysql_error());
// set up fresh arrays for bird names, earliest and latest transmission date for each bird (to later calculate the browseable date range) 
$birdsArray = array();
$allEarliestDates = array();
$allLatestDates = array();
?>

<script type="text/javascript">

<?php
// Iterates through the MySQL results, creating JS arrays for each PTT.
while ($row = @mysql_fetch_assoc($result)) {

	
	// Var for unique PTT number of current bird
	$bird = $row['ptt'];

	//get ptt, location_date, location_class, latitude, longitude for best row from each transmission for current bird
	$get_best_locs = "
	SELECT ptt, location_date, location_class, latitude, longitude from bird_data  
	WHERE (ptt=$bird)
	and (location_class=1 OR location_class=2 OR location_class=3)
	GROUP BY location_date
	ORDER BY location_date";

	$result2=mysql_query($get_best_locs); //or die ("Could not retrieve all best locations data: ".mysql_error());
	if (mysql_num_rows($result2)) {  // if one or more non-empty rows are returned..
	
		// find total number of rows returned to work out latest coordinates
		$number = mysql_num_rows($result2); //or die ("Could not retrieve number of rows of locations data: ".mysql_error());
		$i = 1;
		//Create fresh coordinates and location timestamp arrays
		$coordinates = "";
		$positionTime = "";
		
		while ($row2 = @mysql_fetch_assoc($result2)) {
			// print javascript to push each coordinate and timestamp row on to arrays for each bird
			print "
			coordsAll_" . $cuckoo_names[$bird] . ".push(new google.maps.LatLng(" . $row2['latitude'] . "," . $row2['longitude'] . ")); timesAll_" . $cuckoo_names[$bird] . ".push(" . $row2['location_date'] . "); // date - " . date('d.m.Y', $row2['location_date']) . " ";
		
			// if this is the first entry, add the date to the earliest transmission dates array for all birds
			if($i === 1) {
				array_push($allEarliestDates, $row2['location_date']);
				}
			
			// Create variables with latest location and date
			if($i == $number)
				{
				$LatestCoordinates = "new google.maps.LatLng(" .$row2['latitude'] . "," . $row2['longitude'] . ")";
				$LatestTransDate = $row2['location_date'];
			
				// add the latest date to the latest transmission date array for all birds
				array_push($allLatestDates, $row2['location_date']);
				}
			$i++;
			}

		} // end if $result2 contains non-empty rows
			
	} // end while PTT unique list from db exists
 ?>
 // JS  script continues here to create map code .....
 </script>
best-in-cycle-example.php
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros