Solved

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

Posted on 2013-06-11
19
571 Views
Last Modified: 2014-05-02
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>

Open in new window

best-in-cycle-example.php
0
Comment
Question by:williamskellorn
  • 8
  • 7
  • 4
19 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39237358
Words like "filter" and "best" are not really terms of art in location-aware programming.  But with some discovery we might be able to suggest a good approach.

How many tables and rows are involved in the data base?  I only see bird_data so far.  Is that the only one?  How many rows in the table?  How many unique bird identities ("ptt")?

You might consider using a "down-select" process.  In this process, you would create a temporary table, perhaps using ENGINE=MEMORY, that would contain one row per bird.  The table would be populated in a first query, then the temporary table would be used to generate the JavaScript.

If you want to post some of the representative data, perhaps a hundred lines showing a few birds, we might be able to experiment with different queries to help winnow the results set down to just what you want.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39237393
Ok so if I understand you correctly you want this

For Ptt: 1 whose first message was at 10am 10 June 2013 the next value for this PTT you want to choose is the one with the best location class before 10am 11 June 2013 and the next with the best location class before 10am 12 June 2013 - is that correct?

Just an asside you can do this in MySQL

SELECT * from bird_data where location class in (1,2,3)

Otherwise something like this might work

select max(location_class), ptt, year(location_date), month(location_date), day(location_date) from bird_data
where location_class in (1,2,3)
group by ptt, year(location_date), month(location_date), day(location_date);

This won't give you a 24 hour period starting from the actual time of the first message but will group the records by day showing the highest location class per ptt for each day.

The query will also not contain the lattitude and longitude of the record so you would need to use the results from this query to retrieve the data you are looking for with co-ordinates. See next post
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39237431
query will also not contain the lattitude and longitude
Those data elements are part of the bird_data rows.  Just add them to the SELECT query.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39237449
(deleted)
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39237457
This should work
SELECT MAX(location_class) AS location_class, ptt, lattitude, longitude, location_date FROM bird_data
WHERE location_class IN (1,2,3)
GROUP BY ptt, YEAR(location_date), MONTH(location_date), DAY(location_date);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39237477
Spell check: latitude vs lattitude?  That's why I want the author to give us some test data, so we can shake the typos out and give tested answers.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39237681
With correct spelling of latitude

SELECT MAX(location_class) AS location_class, ptt, latitude, longitude, location_date FROM bird_data
WHERE location_class IN (1,2,3)
GROUP BY ptt, YEAR(location_date), MONTH(location_date), DAY(location_date);

Open in new window

0
 

Author Comment

by:williamskellorn
ID: 39237822
Thanks for the swift responses.

I've attached a sample of 200 rows exported via phpMyAdmin with standard settings to give a clear idea of the structure of the table. I'm aware some of the column types could be more appropriately defined - I inherited this project.

Some further clarification, thanks for requesting Ray -

bird_data is the only table in the DB.
32 columns including the column types mentioned above.
Currently 34,000 rows with 500 - 700 being added daily. This will tail off as we lose contact with some birds.
Currently 30 unique 'ptt' birds.
Over the next year the number of rows and unique ptts are likely to double - project may extend for another two years.

Thanks for the suggestions so far.
bird-data.sql
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39237908
Excellent - thanks for posting the data.  I have business meetings for much of the rest of the day, but I'll return to it later.  Don't worry about column types - we can fix those ;-)  One thing that immediately comes to mind would be to set the lat/lon columns to DECIMAL data type - facilitates arithmetic.

More to follow, ~Ray
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39237999
Your table has location_date as a varchar - is this correct?

How do we interpret that data?
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 300 total points
ID: 39238184
Assuming location_date is a unix time value you should be able to use this query
SELECT MAX(location_class) AS location_class, ptt, latitude, longitude, FROM_UNIXTIME(location_date) FROM bird_data
WHERE location_class IN (1,2,3)
GROUP BY ptt, YEAR(FROM_UNIXTIME(location_date)), MONTH(FROM_UNIXTIME(location_date)), DAY(FROM_UNIXTIME(location_date));

Open in new window


How important is it that each cycle is exactly 24 hours from the first record?
Is the best match per day an alternative (as the query above returns)?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 39241010
Please see http://www.laprbass.com/RAY_temp_williamskellorn.php where I have implemented julianH query.  Please look at the results objects and tell us if this is getting you what you want.

I redacted the data slightly.  Suggested change to LAT/LON data type may facilitate query computation.  I would also suggest changing the date and time information to DATETIME data type, but I didn't have much luck getting SQL to cooperate with that.

Moving parts start at line 322.

<?php // RAY_temp_williamskellorn.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THIS SCRIPT DEMONSTRATES MANY OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


$sql_create = <<<EOD
CREATE TEMPORARY TABLE `bird_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file` varchar(50) NOT NULL,
  `program` varchar(50) NOT NULL DEFAULT '',
  `ptt` varchar(50) NOT NULL DEFAULT '',
  `satellite` varchar(50) NOT NULL DEFAULT '',
  `location_date` varchar(50) NOT NULL DEFAULT '',
  `location_class` varchar(50) NOT NULL DEFAULT '',
  `latitude`              DECIMAL(12,6) NOT NULL DEFAULT '0.0',
  `longitude`             DECIMAL(12,6) NOT NULL DEFAULT '0.0',
  `latitude_solution_2`   DECIMAL(12,6) NOT NULL DEFAULT '0.0',
  `longitude_solution_2`  DECIMAL(12,6) NOT NULL DEFAULT '0.0',
  `message_number` varchar(50) NOT NULL DEFAULT '',
  `nbr_messages_gt_120db` varchar(50) NOT NULL DEFAULT '',
  `best_level` varchar(50) NOT NULL DEFAULT '',
  `pass_duration` varchar(50) NOT NULL DEFAULT '',
  `nopc` varchar(50) NOT NULL DEFAULT '',
  `location_index` varchar(50) NOT NULL DEFAULT '',
  `frequency` varchar(50) NOT NULL DEFAULT '',
  `altitude` varchar(50) NOT NULL DEFAULT '',
  `error_radius` varchar(50) NOT NULL DEFAULT '',
  `semi_major_axis` varchar(50) NOT NULL DEFAULT '',
  `semi_minor_axis` varchar(50) NOT NULL DEFAULT '',
  `ellipse_orientation` varchar(50) NOT NULL DEFAULT '',
  `gdop` varchar(50) NOT NULL DEFAULT '',
  `message_date` varchar(50) NOT NULL DEFAULT '',
  `compression_index` varchar(50) NOT NULL DEFAULT '',
  `_1` int(11) NOT NULL,
  `_2` int(11) NOT NULL,
  `_3` int(11) NOT NULL,
  `_4` int(11) NOT NULL,
  `platform_name` varchar(50) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=41596 ;
EOD;

$sql_insert = <<<EOD
INSERT INTO `bird_data` (`id`, `file`, `program`, `ptt`, `satellite`, `location_date`, `location_class`, `latitude`, `longitude`, `latitude_solution_2`, `longitude_solution_2`, `message_number`, `nbr_messages_gt_120db`, `best_level`, `pass_duration`, `nopc`, `location_index`, `frequency`, `altitude`, `error_radius`, `semi_major_axis`, `semi_minor_axis`, `ellipse_orientation`, `gdop`, `message_date`, `compression_index`, `_1`, `_2`, `_3`, `_4`, `platform_name`) VALUES
(139, '13.TXT', '4921', '62626', 'NL', '1306309626', '3', '52.717', '1.600', '55.480', '-12.888', '6', '0', '-126', '482', '2', '60', '401679660.400', '0.046', '208', '636', '67', '124', '473', '1306309746', '2', 168, 164, 3, 0, ''),
(140, '13.TXT', '4921', '62626', 'NM', '', '', '', '', '', '', '5', '0', '-122', '361', '', '', '401679661.740', '0.000', '', '', '', '', '', '1306309987', '1', 169, 164, 3, 0, ''),
(141, '13.TXT', '4921', '62626', 'MA', '1306312134', 'A', '52.712', '1.611', '49.246', '19.878', '3', '1', '-120', '426', '3', '7', '401679653.370', '0.046', '0', '0', '0', '101', '452', '1306312226', '1', 7, 1, 0, 1, ''),
(142, '13.TXT', '4921', '62626', 'MA', '1306318101', '1', '52.747', '1.593', '58.135', '-26.711', '4', '0', '-122', '499', '3', '57', '401679615.610', '0.046', '883', '1617', '481', '83', '412', '1306318102', '1', 186, 148, 3, 0, ''),
(143, '13.TXT', '4921', '62626', 'NP', '1306320441', '1', '52.709', '1.594', '57.381', '26.278', '7', '0', '-126', '470', '3', '56', '401679556.720', '0.046', '611', '1230', '303', '90', '299', '1306320647', '1', 195, 156, 0, 0, ''),
(144, '13.TXT', '4921', '62626', 'NM', '', '', '', '', '', '', '1', '0', '-136', '0', '', '', '401679610.940', '0.000', '', '', '', '', '', '1306321823', '1', 183, 152, 1, 0, ''),
(145, '13.TXT', '4921', '62626', 'MA', '1306324209', '1', '52.704', '1.588', '65.824', '-76.670', '7', '0', '-128', '430', '4', '67', '401679610.940', '0.046', '543', '916', '321', '45', '792', '1306324424', '2', 10, 1, 0, 1, ''),
(146, '14.TXT', '4921', '62620', 'NK', '', '', '', '', '', '', '1', '0', '-131', '0', '', '', '401680023.530', '0.000', '', '', '', '', '', '1306412084', '1', 228, 200, 3, 0, ''),
(147, '14.TXT', '4921', '62620', 'NP', '1306412227', '1', '52.395', '0.788', '49.436', '-14.639', '9', '0', '-124', '574', '3', '50', '401680025.980', '0.076', '518', '1345', '199', '79', '240', '1306412600', '2', 1, 4, 0, 1, ''),
(148, '14.TXT', '4921', '62620', 'NN', '', '', '', '', '', '', '3', '0', '-125', '287', '', '', '401680007.510', '0.000', '', '', '', '', '', '1306412830', '1', 1, 4, 0, 1, ''),
(149, '14.TXT', '4921', '62620', 'NK', '1306417839', '0', '52.393', '0.736', '56.989', '27.046', '4', '0', '-124', '464', '3', '57', '401679999.090', '0.074', '1654', '3743', '731', '122', '681', '1306417723', '1', 2, 4, 0, 1, ''),
(150, '14.TXT', '4921', '62620', 'NP', '1306418448', '0', '52.331', '0.714', '40.369', '-60.021', '5', '0', '-135', '290', '4', '58', '401680029.410', '0.081', '4096', '22302', '752', '70', '1954', '1306418536', '1', 166, 192, 2, 0, ''),
(151, '14.TXT', '4921', '62620', 'NN', '1306418999', '1', '52.387', '0.754', '42.873', '-46.774', '5', '0', '-128', '465', '4', '58', '401680007.510', '0.076', '1236', '1859', '822', '27', '646', '1306419115', '1', 3, 4, 0, 1, ''),
(152, '14.TXT', '4921', '62620', 'NK', '1306423826', '3', '52.388', '0.767', '48.735', '-19.221', '6', '0', '-124', '399', '3', '66', '401680044.930', '0.077', '199', '272', '145', '95', '384', '1306424026', '1', 20, 4, 0, 1, ''),
(153, '14.TXT', '4921', '62620', 'NL', '', '', '', '', '', '', '1', '0', '-128', '0', '', '', '401680041.040', '0.000', '', '', '', '', '', '1306424768', '1', 160, 188, 1, 0, ''),
(154, '14.TXT', '4921', '62620', 'NL', '1306430688', 'A', '52.401', '0.794', '53.616', '6.586', '3', '0', '-125', '463', '2', '8', '401680025.110', '0.075', '0', '0', '0', '61', '2169', '1306430862', '1', 162, 172, 2, 0, ''),
(155, '14.TXT', '4921', '62620', 'MA', '1306432827', 'B', '52.422', '0.704', '61.045', '48.197', '2', '0', '-123', '227', '2', '0', '401680050.020', '0.076', '0', '0', '0', '15', '825', '1306432714', '1', 143, 168, 2, 0, ''),
(156, '14.TXT', '4921', '62620', 'NM', '1306436102', '2', '52.390', '0.783', '49.939', '-13.012', '5', '0', '-122', '427', '3', '68', '401680050.020', '0.076', '277', '969', '79', '76', '400', '1306436072', '1', 8, 4, 0, 1, ''),
(157, '14.TXT', '4921', '62620', 'NL', '1306436800', '1', '52.404', '0.767', '44.429', '-39.482', '7', '0', '-128', '728', '4', '58', '401680053.750', '0.075', '1230', '2990', '506', '88', '374', '1306436801', '2', 8, 4, 0, 1, ''),
(158, '14.TXT', '4921', '62620', 'MA', '', '', '', '', '', '', '6', '1', '-120', '425', '', '', '401680050.020', '0.000', '', '', '', '', '', '1306438681', '1', 152, 160, 3, 0, ''),
(159, '14.TXT', '4921', '62620', 'NM', '1306442156', '1', '52.389', '0.770', '41.480', '-59.054', '8', '0', '-129', '489', '4', '58', '401680059.710', '0.076', '500', '636', '393', '5', '609', '1306442401', '3', 9, 4, 0, 1, ''),
(160, '14.TXT', '4921', '62620', 'MA', '1306444694', '1', '52.393', '0.757', '43.737', '-44.981', '9', '1', '-120', '675', '4', '58', '401680056.600', '0.076', '544', '891', '332', '84', '320', '1306445032', '3', 158, 156, 2, 0, ''),
(161, '14.TXT', '4921', '62622', 'NL', '', '', '', '', '', '', '3', '0', '-130', '115', '', '', '401679771.090', '0.000', '', '', '', '', '', '1306431091', '1', 171, 200, 2, 0, ''),
(162, '14.TXT', '4921', '62622', 'MA', '1306432754', 'B', '52.619', '1.646', '60.659', '47.143', '2', '0', '-124', '459', '2', '0', '401679800.540', '0.046', '0', '0', '0', '4', '748', '1306432525', '1', 167, 196, 3, 0, ''),
(163, '14.TXT', '4921', '62622', 'NM', '1306436181', '0', '52.588', '1.718', '49.752', '-13.739', '8', '0', '-126', '453', '3', '40', '401679800.540', '0.046', '1797', '5138', '628', '81', '231', '1306436239', '2', 2, 4, 0, 1, ''),
(164, '14.TXT', '4921', '62622', 'NL', '1306436691', '0', '52.574', '1.684', '44.672', '-39.789', '6', '0', '-132', '342', '2', '56', '401679796.680', '0.046', '1666', '5973', '464', '108', '704', '1306436748', '1', 163, 184, 1, 0, ''),
(165, '14.TXT', '4921', '62622', 'MA', '1306438586', 'A', '52.602', '1.668', '52.435', '0.745', '3', '1', '-118', '115', '2', '6', '401679761.410', '0.046', '0', '0', '0', '80', '18612', '1306438587', '1', 168, 180, 1, 0, ''),
(166, '14.TXT', '4921', '62622', 'NM', '1306442178', '1', '52.607', '1.703', '41.272', '-59.927', '6', '0', '-134', '517', '4', '58', '401679770.510', '0.046', '627', '790', '498', '21', '696', '1306442379', '2', 165, 176, 3, 0, ''),
(167, '14.TXT', '4921', '62622', 'MA', '1306444735', '2', '52.605', '1.721', '43.572', '-45.782', '8', '0', '-127', '575', '1', '50', '401679771.810', '0.046', '421', '662', '268', '72', '371', '1306444736', '2', 4, 4, 0, 1, ''),
(168, '14.TXT', '4921', '62622', 'NP', '1306456426', '3', '52.609', '1.704', '49.048', '20.857', '5', '0', '-129', '461', '3', '66', '401679767.590', '0.046', '210', '385', '113', '86', '309', '1306456426', '2', 166, 156, 3, 0, ''),
(169, '14.TXT', '4921', '62622', 'NN', '', '', '', '', '', '', '1', '0', '-128', '0', '', '', '401679767.490', '0.000', '', '', '', '', '', '1306457118', '1', 166, 156, 3, 0, ''),
(170, '14.TXT', '4921', '62622', 'NP', '1306462435', 'A', '52.602', '1.693', '57.566', '-26.024', '3', '0', '-130', '348', '3', '8', '401679759.150', '0.046', '0', '0', '0', '24', '1080', '1306462551', '1', 168, 148, 1, 0, ''),
(171, '14.TXT', '4921', '62622', 'NN', '1306462956', 'A', '52.618', '1.654', '55.099', '-11.247', '3', '0', '-131', '115', '2', '8', '401679758.680', '0.046', '0', '0', '0', '106', '4327', '1306462957', '1', 10, 4, 0, 1, ''),
(172, '15.TXT', '4921', '62628', 'NP', '1306497982', '1', '52.737', '1.666', '50.343', '-10.919', '7', '0', '-123', '587', '2', '50', '401679944.630', '0.046', '1483', '6418', '342', '79', '303', '1306498159', '3', 1, 2, 0, 1, ''),
(173, '15.TXT', '4921', '62628', 'NN', '1306498714', '1', '52.733', '1.647', '52.986', '2.936', '5', '0', '-126', '526', '1', '66', '401679954.800', '0.046', '1229', '60227', '25', '78', '3455', '1306498860', '2', 1, 2, 0, 1, ''),
(174, '15.TXT', '4921', '62628', 'MA', '', '', '', '', '', '', '1', '0', '-133', '0', '', '', '401679944.630', '0.000', '', '', '', '', '', '1306500386', '1', 169, 192, 2, 0, ''),
(175, '15.TXT', '4921', '62628', 'NK', '1306503045', 'B', '52.674', '1.510', '59.398', '35.986', '2', '0', '-125', '294', '2', '0', '401679942.450', '0.046', '0', '0', '0', '5', '1253', '1306502899', '1', 170, 188, 1, 0, ''),
(176, '15.TXT', '4921', '62628', 'NP', '1306504023', '2', '52.704', '1.606', '41.591', '-57.041', '10', '0', '-129', '594', '4', '68', '401679946.430', '0.046', '413', '496', '343', '152', '606', '1306504262', '2', 3, 2, 0, 1, ''),
(177, '15.TXT', '4921', '62628', 'NN', '1306504705', '1', '52.740', '1.621', '43.980', '-43.038', '7', '0', '-128', '532', '3', '56', '401679942.410', '0.046', '996', '1308', '757', '104', '398', '1306504972', '3', 3, 2, 0, 1, ''),
(178, '15.TXT', '4921', '62628', 'NK', '1306508923', '2', '52.736', '1.622', '50.680', '-9.640', '7', '0', '-121', '592', '4', '68', '401679949.210', '0.046', '322', '2270', '45', '73', '814', '1306508924', '1', 171, 172, 3, 0, ''),
(179, '15.TXT', '4921', '62628', 'NL', '1306510771', 'A', '52.633', '1.451', '63.713', '61.442', '3', '0', '-134', '120', '2', '7', '401679904.260', '0.050', '0', '0', '0', '43', '32769', '1306510771', '1', 179, 172, 2, 0, ''),
(180, '15.TXT', '4921', '62628', 'NK', '1306514893', '1', '52.724', '1.677', '41.879', '-55.077', '11', '0', '-128', '655', '4', '58', '401679917.110', '0.046', '993', '1304', '756', '68', '365', '1306515221', '2', 6, 2, 0, 1, ''),
(181, '15.TXT', '4921', '62628', 'NM', '1306515250', '2', '52.729', '1.614', '60.393', '44.044', '4', '0', '-126', '535', '4', '68', '401679921.870', '0.046', '336', '679', '166', '91', '452', '1306515399', '1', 173, 168, 3, 0, ''),
(182, '15.TXT', '4921', '62628', 'NL', '1306516551', '0', '52.732', '1.571', '54.737', '11.601', '8', '1', '-119', '647', '4', '58', '401679945.310', '0.046', '1598', '4824', '529', '80', '329', '1306516816', '1', 168, 164, 3, 0, ''),
(183, '15.TXT', '4921', '62628', 'MA', '1306517904', 'B', '52.833', '1.763', '62.662', '57.842', '2', '0', '-122', '59', '2', '0', '401679942.450', '0.046', '0', '0', '0', '41', '1768', '1306517875', '1', 166, 164, 1, 2, ''),
(184, '15.TXT', '4921', '62628', 'NM', '1306521057', '0', '52.733', '1.622', '51.915', '-2.968', '10', '0', '-124', '766', '2', '58', '401679960.100', '0.046', '1891', '22877', '156', '82', '599', '1306521059', '2', 166, 160, 3, 0, ''),
(185, '15.TXT', '4921', '62628', 'NL', '1306522501', '0', '52.737', '1.649', '45.540', '-34.675', '8', '0', '-125', '765', '4', '58', '401679962.880', '0.046', '1971', '5009', '775', '93', '295', '1306522884', '2', 162, 160, 2, 0, ''),
(186, '15.TXT', '4921', '62628', 'MA', '1306523850', '1', '52.753', '1.628', '54.299', '9.955', '8', '4', '-118', '763', '1', '50', '401679971.970', '0.046', '817', '5190', '128', '77', '374', '1306523703', '2', 8, 2, 0, 1, ''),
(187, '15.TXT', '4921', '62628', 'NM', '1306527277', '2', '52.748', '1.613', '43.281', '-48.724', '7', '0', '-128', '414', '4', '68', '401679969.850', '0.046', '366', '1148', '116', '66', '671', '1306527485', '2', 9, 2, 0, 1, ''),
(188, '15.TXT', '4921', '62628', 'NL', '1306528695', 'A', '52.749', '1.625', '37.057', '-80.411', '3', '0', '-136', '177', '2', '50', '401679971.230', '0.046', '0', '0', '0', '105', '0', '1306528725', '1', 163, 148, 1, 0, ''),
(189, '15.TXT', '4921', '62628', 'MA', '1306529964', '2', '52.751', '1.618', '45.495', '-36.036', '8', '0', '-122', '590', '4', '58', '401679971.230', '0.046', '380', '777', '185', '72', '324', '1306530259', '2', 162, 148, 3, 0, ''),
(190, '15.TXT', '4921', '62626', 'NN', '1306498637', '0', '52.587', '1.323', '53.020', '3.593', '8', '0', '-124', '729', '1', '40', '401679664.680', '0.056', '13576', '300433', '613', '78', '1255', '1306499002', '3', 1, 2, 0, 1, ''),
(191, '15.TXT', '4921', '62626', 'NK', '1306503235', 'B', '49.573', '0.483', '59.351', '37.722', '2', '0', '-133', '58', '1', '0', '401679681.390', '0.049', '0', '0', '0', '39', '9450', '1306503207', '1', 144, 152, 2, 0, ''),
(192, '15.TXT', '4921', '62626', 'NP', '1306504054', '0', '52.670', '1.357', '41.487', '-56.821', '8', '0', '-132', '531', '3', '56', '401679738.830', '0.052', '2179', '2365', '2007', '135', '514', '1306504261', '3', 2, 2, 0, 1, ''),
(193, '15.TXT', '4921', '62626', 'NL', '1306504651', 'B', '52.618', '1.711', '71.024', '115.102', '2', '0', '-130', '181', '1', '0', '401679696.900', '0.049', '0', '0', '0', '35', '1132', '1306504561', '1', 175, 152, 1, 0, ''),
(194, '15.TXT', '4921', '62626', 'NN', '1306504798', '0', '52.640', '1.675', '43.719', '-42.797', '7', '0', '-126', '596', '4', '46', '401679712.040', '0.046', '4507', '8819', '2303', '65', '427', '1306504978', '2', 2, 2, 0, 1, ''),
(195, '15.TXT', '4921', '62626', 'NK', '1306508804', '3', '52.631', '1.493', '50.715', '-9.102', '8', '0', '-122', '580', '3', '66', '401679680.080', '0.048', '236', '825', '67', '71', '528', '1306508804', '2', 3, 2, 0, 1, ''),
(196, '15.TXT', '4921', '62626', 'NM', '', '', '', '', '', '', '1', '0', '-134', '0', '', '', '401679696.900', '0.000', '', '', '', '', '', '1306509431', '1', 175, 144, 2, 0, ''),
(197, '15.TXT', '4921', '62626', 'NL', '1306510653', '0', '52.660', '1.460', '63.819', '60.975', '4', '0', '-126', '365', '2', '56', '401679667.650', '0.049', '3491', '35705', '341', '75', '2237', '1306510775', '1', 4, 2, 0, 1, ''),
(198, '15.TXT', '4921', '62626', 'MA', '1306512062', 'A', '52.855', '1.611', '70.238', '113.090', '3', '0', '-123', '250', '3', '5', '401679631.230', '0.000', '0', '0', '0', '90', '4719', '1306511999', '1', 4, 2, 0, 1, ''),
(199, '15.TXT', '4921', '62626', 'NK', '1306514849', '2', '52.604', '1.511', '42.132', '-55.347', '6', '0', '-129', '603', '3', '68', '401679677.550', '0.046', '461', '1126', '188', '25', '1231', '1306515151', '2', 165, 140, 3, 0, ''),
(200, '15.TXT', '4921', '62626', 'NM', '1306515271', '1', '52.650', '1.544', '60.348', '44.361', '4', '0', '-125', '484', '3', '56', '401679689.430', '0.046', '1209', '3536', '413', '87', '492', '1306515453', '2', 5, 2, 0, 1, ''),
(201, '15.TXT', '4921', '62626', 'NL', '1306516425', '3', '52.631', '1.497', '54.599', '11.824', '7', '0', '-122', '608', '2', '66', '401679678.180', '0.049', '221', '560', '87', '89', '396', '1306516791', '1', 167, 136, 2, 0, ''),
(202, '15.TXT', '4921', '62626', 'MA', '1306518104', 'B', '52.805', '1.765', '63.258', '55.910', '2', '0', '-123', '61', '2', '0', '401679696.900', '0.049', '0', '0', '0', '37', '3131', '1306518074', '1', 174, 136, 3, 0, ''),
(203, '15.TXT', '4921', '62626', 'NM', '1306521266', '0', '52.662', '1.463', '51.887', '-2.735', '4', '0', '-124', '544', '2', '58', '401679689.720', '0.049', '1648', '12861', '211', '80', '722', '1306521538', '2', 7, 2, 0, 1, ''),
(204, '15.TXT', '4921', '62626', 'NL', '1306522607', '1', '52.641', '1.529', '45.400', '-34.251', '6', '0', '-128', '552', '4', '58', '401679684.680', '0.048', '583', '1537', '220', '79', '356', '1306522822', '1', 168, 116, 1, 0, ''),
(205, '16.TXT', '4921', '62620', 'NL', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401680041.940', '0.000', '', '', '', '', '', '1306614434', '1', 160, 172, 1, 8, ''),
(206, '16.TXT', '4921', '62620', 'MA', '1306614954', '2', '52.392', '0.770', '47.473', '-25.671', '8', '1', '-118', '430', '2', '50', '401680041.940', '0.076', '460', '881', '239', '99', '254', '1306615108', '2', 1, 5, 0, 1, ''),
(207, '16.TXT', '4921', '62620', 'MA', '1306621051', '0', '52.388', '0.756', '38.899', '-71.544', '4', '0', '-134', '348', '4', '58', '401680043.670', '0.076', '2830', '5724', '1399', '174', '1800', '1306621168', '1', 161, 160, 2, 2, ''),
(208, '16.TXT', '4921', '62620', 'NP', '1306627711', 'A', '52.388', '0.773', '46.546', '30.516', '3', '0', '-125', '407', '3', '8', '401680040.020', '0.076', '0', '0', '0', '99', '652', '1306627799', '1', 20, 5, 0, 1, ''),
(209, '16.TXT', '4921', '62620', 'NP', '1306634035', '1', '52.400', '0.766', '55.473', '-15.248', '6', '0', '-128', '704', '1', '50', '401680034.160', '0.076', '1276', '2917', '557', '126', '485', '1306634329', '2', 6, 5, 0, 1, ''),
(210, '16.TXT', '4921', '62620', 'NN', '1306634766', '0', '52.403', '0.708', '52.610', '-0.395', '6', '0', '-125', '524', '1', '56', '401680040.610', '0.071', '10158', '369995', '278', '101', '2771', '1306635029', '3', 6, 5, 0, 1, ''),
(211, '16.TXT', '4921', '62620', 'NP', '1306639815', 'A', '52.384', '0.742', '64.229', '-65.238', '3', '0', '-131', '117', '2', '8', '401680020.100', '0.076', '0', '0', '0', '97', '8620', '1306639815', '1', 166, 140, 2, 0, ''),
(212, '16.TXT', '4921', '62620', 'NN', '1306640604', 'B', '52.383', '0.752', '61.314', '-48.012', '2', '0', '-132', '176', '2', '0', '401680034.610', '0.077', '0', '0', '0', '51', '599', '1306640517', '1', 160, 140, 2, 0, ''),
(213, '16.TXT', '4921', '62620', 'NK', '1306643514', '2', '52.384', '0.799', '53.412', '-4.760', '7', '0', '-124', '470', '4', '58', '401680034.610', '0.077', '400', '2118', '75', '99', '438', '1306643632', '1', 163, 136, 3, 0, ''),
(214, '16.TXT', '4921', '62620', 'NN', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401680034.610', '0.000', '', '', '', '', '', '1306646437', '1', 167, 132, 3, 0, ''),
(215, '16.TXT', '4921', '62622', 'NP', '1306634126', '1', '52.588', '1.740', '55.909', '-16.593', '5', '0', '-127', '229', '2', '67', '401679775.700', '0.000', '1224', '16324', '91', '101', '1653', '1306634183', '1', 2, 5, 0, 1, ''),
(216, '16.TXT', '4921', '62622', 'NN', '', '', '', '', '', '', '5', '0', '-125', '574', '', '', '401679759.210', '0.000', '', '', '', '', '', '1306634872', '2', 2, 5, 0, 1, ''),
(217, '16.TXT', '4921', '62622', 'NN', '1306640625', '0', '52.563', '1.815', '61.628', '-47.819', '5', '0', '-129', '672', '2', '50', '401679800.720', '0.000', '2571', '5944', '1111', '89', '507', '1306640906', '1', 137, 156, 1, 0, ''),
(218, '16.TXT', '4921', '62622', 'NK', '1306643537', '3', '52.707', '1.717', '53.968', '-5.252', '5', '0', '-126', '348', '3', '66', '401679759.210', '0.000', '217', '531', '88', '101', '446', '1306643596', '2', 4, 5, 0, 1, ''),
(219, '16.TXT', '4921', '62622', 'NN', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401679759.210', '0.000', '', '', '', '', '', '1306646731', '1', 160, 148, 3, 0, ''),
(220, '16.TXT', '4921', '62622', 'NM', '', '', '', '', '', '', '1', '0', '-130', '0', '', '', '401679759.210', '0.000', '', '', '', '', '', '1306649470', '1', 181, 148, 1, 0, ''),
(221, '16.TXT', '4921', '62622', 'NL', '1306652307', 'Z', '51.241', '9.356', '52.744', '1.533', '3', '0', '-125', '176', '0', '20', '401679748.020', '0.350', '0', '0', '0', '100', '575', '1306652338', '1', 175, 144, 1, 0, ''),
(222, '1.TXT', '4921', '62626', 'NL', '', '', '', '', '', '', '1', '0', '-125', '0', '', '', '401679685.230', '0.000', '', '', '', '', '', '1306738078', '1', 171, 120, 1, 0, ''),
(223, '1.TXT', '4921', '62626', 'NN', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401679685.230', '0.000', '', '', '', '', '', '1306738144', '1', 171, 120, 2, 0, ''),
(224, '17.TXT', '4921', '62628', 'NM', '1306703128', '0', '52.719', '1.594', '39.329', '-73.634', '4', '0', '-130', '178', '2', '60', '401679957.640', '0.046', '2238', '9358', '535', '161', '6313', '1306703158', '1', 168, 156, 1, 0, ''),
(225, '17.TXT', '4921', '62628', 'MA', '1306706093', '0', '52.717', '1.645', '40.980', '-62.987', '6', '0', '-123', '295', '3', '58', '401679959.570', '0.046', '4599', '20260', '1043', '125', '1960', '1306706241', '2', 164, 152, 1, 0, ''),
(226, '17.TXT', '4921', '62628', 'NP', '1306713418', '2', '52.725', '1.596', '46.282', '34.684', '4', '0', '-126', '357', '2', '68', '401679960.670', '0.046', '491', '3768', '63', '90', '1209', '1306713538', '1', 4, 3, 0, 1, ''),
(227, '17.TXT', '4921', '62628', 'NN', '1306714043', 'A', '52.720', '1.611', '43.188', '49.463', '3', '0', '-122', '299', '2', '8', '401679957.220', '0.046', '0', '0', '0', '97', '2502', '1306714015', '1', 4, 3, 0, 1, ''),
(228, '17.TXT', '4921', '62628', 'NP', '1306719556', '2', '52.723', '1.608', '55.177', '-11.158', '8', '0', '-125', '838', '4', '58', '401679955.430', '0.046', '392', '2095', '73', '100', '438', '1306719976', '1', 6, 3, 0, 1, ''),
(229, '17.TXT', '4921', '62628', 'NN', '1306720214', '1', '52.733', '1.533', '52.187', '4.319', '8', '0', '-124', '597', '1', '56', '401679955.420', '0.046', '845', '10242', '69', '101', '867', '1306720453', '3', 6, 3, 0, 1, ''),
(230, '17.TXT', '4921', '62628', 'NK', '1306722216', 'A', '52.729', '1.575', '43.216', '51.074', '3', '0', '-123', '299', '2', '7', '401679965.740', '0.046', '0', '0', '0', '96', '2607', '1306722186', '1', 164, 116, 1, 0, ''),
(231, '17.TXT', '4921', '62622', 'NK', '', '', '', '', '', '', '1', '0', '-129', '0', '', '', '401679759.210', '0.000', '', '', '', '', '', '1306637177', '1', 3, 5, 0, 1, ''),
(232, '17.TXT', '4921', '62622', 'NN', '1306646701', 'B', '52.670', '1.400', '69.636', '-101.261', '2', '0', '-137', '58', '2', '0', '401679759.210', '0.000', '0', '0', '0', '43', '4892', '1306646673', '1', 158, 148, 2, 0, ''),
(233, '17.TXT', '4921', '62622', 'NK', '1306649167', 'B', '52.436', '1.957', '63.177', '-50.823', '2', '0', '-134', '121', '2', '0', '401679759.210', '0.000', '0', '0', '0', '59', '1880', '1306649107', '1', 179, 148, 3, 0, ''),
(234, '17.TXT', '4921', '62622', 'NL', '1306652307', 'A', '52.744', '1.533', '51.241', '9.356', '3', '0', '-125', '176', '2', '7', '401679737.080', '0.046', '0', '0', '0', '91', '565', '1306652338', '1', 175, 144, 1, 0, ''),
(235, '17.TXT', '4921', '62622', 'NK', '1306655213', 'A', '52.756', '1.592', '69.927', '-108.303', '3', '0', '-130', '176', '2', '8', '401679745.310', '0.046', '0', '0', '0', '95', '8299', '1306655184', '1', 173, 144, 2, 0, ''),
(236, '17.TXT', '4921', '62622', 'NM', '1306655741', '0', '52.744', '1.531', '53.176', '-0.731', '9', '0', '-126', '645', '2', '58', '401679750.530', '0.046', '5005', '44598', '561', '101', '1229', '1306656005', '2', 8, 5, 0, 1, ''),
(237, '17.TXT', '4921', '62622', 'NP', '', '', '', '', '', '', '1', '0', '-133', '0', '', '', '401679759.210', '0.000', '', '', '', '', '', '1306657704', '1', 172, 140, 1, 0, ''),
(238, '17.TXT', '4921', '62622', 'NL', '1306658383', 'A', '52.511', '1.920', '60.230', '-37.102', '3', '0', '-130', '639', '1', '40', '401679761.620', '0.000', '0', '0', '0', '138', '0', '1306658183', '1', 169, 140, 1, 0, ''),
(239, '17.TXT', '4921', '62622', 'MA', '1306658875', 'A', '52.679', '1.591', '50.822', '11.697', '3', '0', '-124', '231', '3', '8', '401679761.620', '0.046', '0', '0', '0', '94', '432', '1306658875', '1', 162, 144, 1, 0, ''),
(240, '17.TXT', '4921', '62622', 'NM', '1306661637', '1', '52.715', '1.709', '61.675', '-49.498', '4', '0', '-130', '238', '2', '68', '401679757.120', '0.000', '1500', '11618', '193', '110', '2289', '1306661697', '1', 176, 140, 1, 0, ''),
(241, '17.TXT', '4921', '62622', 'MA', '', '', '', '', '', '', '1', '0', '-129', '0', '', '', '401679761.620', '0.000', '', '', '', '', '', '1306664778', '1', 10, 5, 1, 1, ''),
(242, '17.TXT', '4921', '62626', 'NP', '1306713737', '2', '52.641', '1.536', '46.345', '35.291', '5', '0', '-126', '303', '2', '60', '401679685.230', '0.047', '394', '1332', '116', '69', '587', '1306713829', '2', 2, 3, 0, 1, ''),
(243, '18.TXT', '4921', '62620', 'NN', '1306817911', 'B', '52.410', '0.762', '67.944', '-88.204', '2', '0', '-128', '58', '1', '0', '401680034.610', '0.077', '0', '0', '0', '49', '2779', '1306817882', '1', 164, 156, 1, 0, ''),
(244, '18.TXT', '4921', '62620', 'NK', '1306819260', 'B', '52.516', '0.668', '57.988', '-28.116', '2', '0', '-127', '59', '1', '0', '401680034.610', '0.077', '0', '0', '0', '57', '1607', '1306819231', '1', 1, 6, 0, 1, ''),
(245, '18.TXT', '4921', '62620', 'NL', '1306823822', '1', '52.389', '0.844', '48.654', '20.464', '4', '0', '-123', '184', '1', '60', '401679960.390', '0.079', '747', '10612', '52', '97', '2086', '1306823854', '1', 187, 148, 2, 0, ''),
(246, '18.TXT', '4921', '62620', 'NK', '1306825314', 'B', '52.513', '0.503', '65.776', '-78.735', '2', '0', '-134', '58', '1', '0', '401680034.610', '0.077', '0', '0', '0', '47', '2407', '1306825285', '1', 155, 148, 2, 0, ''),
(247, '18.TXT', '4921', '62620', 'NM', '1306825940', 'B', '52.404', '0.981', '48.799', '20.996', '2', '0', '-127', '120', '1', '0', '401680034.610', '0.077', '0', '0', '0', '23', '776', '1306825880', '1', 3, 6, 0, 1, ''),
(248, '19.TXT', '4921', '62628', 'NL', '1306909031', 'B', '52.376', '0.661', '45.363', '27.690', '2', '0', '-130', '219', '1', '0', '401679956.210', '0.046', '0', '0', '0', '76', '1786', '1306908922', '1', 1, 132, 0, 1, ''),
(249, '19.TXT', '4921', '62628', 'NK', '1306910470', 'A', '52.807', '1.340', '64.396', '-65.840', '3', '0', '-130', '171', '1', '50', '401679832.320', '0.054', '0', '0', '0', '101', '0', '1306910499', '1', 1, 4, 0, 1, ''),
(250, '19.TXT', '4921', '62628', 'NM', '1306910843', '1', '52.760', '1.538', '46.912', '31.861', '8', '0', '-125', '806', '2', '50', '401679833.810', '0.046', '527', '1178', '235', '63', '533', '1306910728', '2', 1, 4, 0, 1, ''),
(251, '19.TXT', '4921', '62620', 'MA', '', '', '', '', '', '', '1', '0', '-123', '0', '', '', '401680034.610', '0.000', '', '', '', '', '', '1306829076', '1', 175, 144, 0, 0, ''),
(252, '19.TXT', '4921', '62620', 'NM', '', '', '', '', '', '', '1', '0', '-129', '0', '', '', '401679903.240', '0.000', '', '', '', '', '', '1306831780', '1', 167, 140, 2, 0, ''),
(253, '19.TXT', '4921', '62620', 'MA', '1306835210', 'A', '52.376', '0.829', '55.306', '-14.989', '3', '1', '-118', '356', '2', '6', '401679997.560', '0.080', '0', '0', '0', '111', '469', '1306835152', '1', 170, 136, 2, 0, ''),
(254, '19.TXT', '4921', '62620', 'NL', '1306835714', 'B', '52.091', '0.580', '65.697', '-75.590', '2', '0', '-136', '297', '2', '0', '401679903.240', '0.080', '0', '0', '0', '48', '662', '1306835566', '1', 167, 136, 1, 0, ''),
(255, '19.TXT', '4921', '62620', 'NM', '1306837776', '0', '52.357', '0.714', '64.960', '-75.078', '4', '0', '-134', '356', '3', '55', '401679975.270', '0.077', '4600', '12407', '1705', '62', '1122', '1306837776', '1', 169, 136, 2, 0, ''),
(256, '19.TXT', '4921', '62620', 'NP', '1306840989', '0', '52.390', '0.858', '53.992', '9.453', '6', '1', '-120', '421', '3', '45', '401679903.240', '0.080', '2806', '13089', '601', '78', '379', '1306841139', '1', 209, 136, 2, 0, ''),
(257, '19.TXT', '4921', '62620', 'MA', '1306841382', 'A', '52.450', '0.588', '63.319', '-61.960', '3', '0', '-132', '122', '1', '50', '401679962.700', '0.047', '0', '0', '0', '93', '0', '1306841382', '1', 208, 136, 2, 0, ''),
(258, '19.TXT', '4921', '62620', 'NN', '1306841741', '0', '52.336', '0.861', '56.900', '24.425', '5', '0', '-126', '413', '2', '56', '401679926.450', '0.089', '2589', '9663', '693', '85', '439', '1306841919', '1', 183, 136, 3, 0, ''),
(259, '19.TXT', '4921', '62620', 'NP', '1306846983', '1', '52.386', '0.721', '45.425', '-36.857', '7', '0', '-131', '425', '3', '56', '401679942.030', '0.072', '1159', '3751', '358', '109', '482', '1306847013', '2', 177, 132, 3, 0, ''),
(260, '19.TXT', '4921', '62620', 'NN', '1306847653', '1', '52.398', '0.859', '47.966', '-22.265', '4', '0', '-129', '300', '2', '56', '401679953.220', '0.080', '1283', '2508', '656', '92', '368', '1306847684', '1', 173, 132, 2, 0, ''),
(261, '19.TXT', '4921', '62620', 'NK', '', '', '', '', '', '', '1', '0', '-124', '0', '', '', '401679942.030', '0.000', '', '', '', '', '', '1306848633', '1', 163, 132, 2, 0, ''),
(262, '19.TXT', '4921', '62620', 'NP', '1306853310', 'B', '52.455', '0.735', '36.878', '-83.218', '2', '0', '-137', '61', '2', '0', '401679942.030', '0.072', '0', '0', '0', '43', '4551', '1306853280', '1', 176, 128, 3, 0, ''),
(263, '19.TXT', '4921', '62620', 'NN', '1306853616', 'B', '52.396', '0.785', '39.242', '-68.338', '2', '0', '-136', '62', '2', '0', '401679942.030', '0.072', '0', '0', '0', '48', '4596', '1306853586', '1', 10, 6, 0, 1, ''),
(264, '19.TXT', '4921', '62622', 'NM', '1306831642', '0', '52.734', '1.493', '57.715', '-26.464', '4', '0', '-130', '262', '2', '46', '401679775.290', '0.046', '29238', '478124', '1787', '112', '2831', '1306831773', '2', 1, 6, 0, 1, ''),
(265, '19.TXT', '4921', '62622', 'MA', '', '', '', '', '', '', '1', '0', '-124', '0', '', '', '401679615.990', '0.000', '', '', '', '', '', '1306835071', '1', 2, 6, 0, 1, ''),
(266, '19.TXT', '4921', '62622', 'NN', '', '', '', '', '', '', '1', '0', '-134', '0', '', '', '401679705.270', '0.000', '', '', '', '', '', '1306835942', '1', 2, 6, 2, 13, ''),
(267, '19.TXT', '4921', '62622', 'NM', '1306837771', '0', '52.640', '1.505', '65.395', '-75.636', '8', '0', '-129', '417', '3', '55', '401679617.800', '0.049', '1693', '4150', '690', '64', '731', '1306837980', '2', 3, 6, 0, 1, ''),
(268, '19.TXT', '4921', '62622', 'NP', '1306840898', '1', '52.694', '1.635', '53.965', '8.412', '6', '0', '-125', '645', '3', '55', '401679704.800', '0.046', '1222', '9431', '158', '75', '559', '1306841162', '1', 179, 172, 2, 0, ''),
(269, '19.TXT', '4921', '62622', 'NN', '1306841572', '2', '52.683', '1.587', '56.765', '23.432', '5', '0', '-124', '351', '3', '56', '401679705.270', '0.046', '362', '727', '180', '61', '356', '1306841748', '2', 4, 6, 0, 1, ''),
(270, '19.TXT', '4921', '62622', 'NK', '', '', '', '', '', '', '1', '0', '-130', '0', '', '', '401679701.150', '0.000', '', '', '', '', '', '1306842565', '1', 177, 172, 2, 0, ''),
(271, '19.TXT', '4921', '62622', 'NP', '1306847068', '0', '52.740', '1.433', '45.217', '-37.920', '6', '0', '-129', '637', '4', '48', '401679720.190', '0.048', '4345', '9087', '2077', '95', '352', '1306847209', '1', 177, 168, 2, 0, ''),
(272, '19.TXT', '4921', '62622', 'NN', '1306847654', '0', '52.658', '1.596', '47.940', '-23.064', '5', '0', '-125', '296', '4', '58', '401679698.900', '0.046', '1683', '3043', '930', '100', '364', '1306847803', '2', 5, 6, 0, 1, ''),
(273, '19.TXT', '4921', '62622', 'NL', '', '', '', '', '', '', '1', '0', '-134', '0', '', '', '401679701.150', '0.000', '', '', '', '', '', '1306853371', '1', 175, 164, 3, 0, ''),
(274, '19.TXT', '4921', '62622', 'NN', '1306853818', '0', '52.670', '1.562', '38.939', '-68.941', '4', '0', '-131', '423', '4', '58', '401679712.240', '0.046', '2798', '10629', '736', '18', '2366', '1306853970', '1', 180, 164, 1, 0, ''),
(275, '19.TXT', '4921', '62622', 'NK', '1306854718', '1', '52.693', '1.623', '50.360', '-10.738', '10', '0', '-124', '663', '1', '50', '401679701.150', '0.046', '831', '3621', '190', '82', '249', '1306855050', '2', 180, 164, 3, 0, ''),
(276, '19.TXT', '4921', '62622', 'NL', '', '', '', '', '', '', '1', '0', '-134', '0', '', '', '401679701.150', '0.000', '', '', '', '', '', '1306859516', '1', 168, 160, 3, 0, ''),
(277, '19.TXT', '4921', '62622', 'NK', '1306860737', '0', '52.645', '1.695', '41.498', '-56.438', '6', '0', '-130', '492', '4', '46', '401679768.560', '0.046', '4787', '7704', '2974', '55', '636', '1306860737', '2', 172, 160, 3, 0, ''),
(278, '19.TXT', '4921', '62622', 'NM', '1306861393', 'B', '52.833', '1.741', '60.601', '38.243', '2', '0', '-132', '64', '2', '0', '401679701.150', '0.046', '0', '0', '0', '35', '4288', '1306861361', '1', 182, 164, 1, 0, ''),
(279, '19.TXT', '4921', '62622', 'NL', '1306865362', '0', '52.606', '1.795', '49.794', '-12.271', '5', '0', '-127', '429', '4', '47', '401679761.770', '0.000', '3514', '18047', '684', '75', '416', '1306865577', '2', 165, 160, 2, 0, ''),
(280, '19.TXT', '4921', '62629', 'NN', '1306903854', '1', '52.465', '0.686', '66.881', '-82.903', '5', '0', '-130', '242', '2', '60', '401680068.660', '0.074', '1233', '4819', '315', '58', '2155', '1306903976', '2', 2, 1, 0, 1, ''),
(281, '19.TXT', '4921', '62629', 'NK', '1306904308', 'B', '52.464', '0.675', '55.790', '-17.494', '2', '0', '-125', '61', '1', '0', '401680068.660', '0.074', '0', '0', '0', '58', '860', '1306904278', '1', 156, 188, 1, 0, ''),
(282, '19.TXT', '4921', '62629', 'NK', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401680068.660', '0.000', '', '', '', '', '', '1306910478', '1', 177, 176, 3, 0, ''),
(283, '19.TXT', '4921', '62629', 'NM', '', '', '', '', '', '', '1', '0', '-129', '0', '', '', '401680068.660', '0.000', '', '', '', '', '', '1306910905', '1', 206, 180, 2, 0, ''),
(284, '20.TXT', '4921', '62628', 'NK', '1306910298', '0', '52.670', '1.546', '64.503', '-66.574', '8', '0', '-130', '514', '3', '56', '401679798.780', '0.046', '3459', '6293', '1900', '63', '505', '1306910499', '3', 1, 4, 0, 1, ''),
(285, '20.TXT', '4921', '62628', 'NL', '1306915341', '1', '52.738', '1.502', '56.998', '-20.002', '4', '0', '-124', '481', '4', '56', '401679864.880', '0.046', '968', '2397', '391', '115', '416', '1306915582', '3', 3, 4, 0, 1, ''),
(286, '20.TXT', '4921', '62628', 'NM', '1306916807', '1', '52.766', '1.533', '55.715', '-14.845', '5', '0', '-127', '301', '3', '52', '401679896.700', '0.046', '738', '1901', '286', '97', '320', '1306916835', '1', 185, 192, 1, 0, ''),
(287, '20.TXT', '4921', '62628', 'MA', '1306920318', '0', '52.690', '1.790', '54.135 ', '-6.009', '7', '1', '-118', '457', '1', '58', '401679862.650', '0.275', '1960', '8061', '476', '100', '347', '1306920434', '1', 184, 184, 3, 0, ''),
(288, '20.TXT', '4921', '62628', 'NL', '1306921366', 'B', '52.613', '1.521', '65.406', '-69.670', '2', '0', '-137', '297', '2', '0', '401679864.880', '0.046', '0', '0', '0', '54', '583', '1306921218', '1', 177, 176, 1, 0, ''),
(289, '20.TXT', '4921', '62628', 'NN', '', '', '', '', '', '', '1', '0', '-136', '0', '', '', '401679864.880', '0.000', '', '', '', '', '', '1306921575', '1', 178, 180, 3, 0, ''),
(290, '20.TXT', '4921', '62628', 'MA', '1306926294', '0', '52.892', '1.529', '62.889', '-52.822', '5', '0', '-128', '583', '3', '48', '401679837.210', '0.000', '13237', '39493', '4436', '44', '925', '1306926527', '1', 204, 176, 2, 0, ''),
(291, '20.TXT', '4921', '62628', 'NP', '1306926876', '0', '52.670', '1.478', '54.943', '13.506', '7', '0', '-123', '465', '2', '47', '401679819.690', '0.048', '6693', '22303', '2008', '78', '287', '1306927109', '2', 6, 4, 0, 1, ''),
(292, '20.TXT', '4921', '62628', 'NL', '1306927195', '0', '52.700', '1.526', '72.494', '-129.753', '4', '0', '-133', '174', '3', '68', '401679830.300', '0.046', '2504', '25247', '248', '87', '7071', '1306927225', '1', 188, 172, 2, 0, ''),
(293, '20.TXT', '4921', '62628', 'NN', '1306927480', 'A', '52.594', '1.575', '58.227', '28.105', '3', '0', '-129', '628', '3', '8', '401679889.250', '0.047', '0', '0', '0', '114', '4214', '1306927739', '1', 178, 172, 3, 0, ''),
(294, '20.TXT', '4921', '62628', 'NM', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401679864.880', '0.000', '', '', '', '', '', '1306928662', '1', 187, 172, 3, 0, ''),
(295, '20.TXT', '4921', '62628', 'MA', '', '', '', '', '', '', '1', '0', '-132', '0', '', '', '401679893.790', '0.000', '', '', '', '', '', '1306932132', '1', 8, 4, 0, 1, ''),
(296, '20.TXT', '4921', '62628', 'NP', '1306932915', '2', '52.733', '1.622', '46.015', '-33.144', '6', '0', '-127', '481', '4', '58', '401679893.790', '0.046', '316', '582', '171', '87', '346', '1306932916', '2', 179, 168, 1, 0, ''),
(297, '20.TXT', '4921', '62628', 'NN', '1306933397', '1', '52.724', '1.627', '48.881', '-18.263', '7', '0', '-124', '724', '3', '56', '401679895.940', '0.046', '536', '1832', '156', '87', '269', '1306933760', '2', 180, 164, 3, 0, ''),
(298, '20.TXT', '4921', '62628', 'NP', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401679893.790', '0.000', '', '', '', '', '', '1306939035', '1', 182, 164, 1, 0, ''),
(299, '20.TXT', '4921', '62628', 'NK', '', '', '', '', '', '', '3', '0', '-131', '606', '', '', '401679893.790', '0.000', '', '', '', '', '', '1306939702', '1', 10, 4, 0, 1, ''),
(300, '20.TXT', '4921', '62628', 'NN', '1306939549', '1', '52.704', '1.612', '39.864', '-64.207', '5', '0', '-134', '547', '4', '58', '401679896.700', '0.046', '1453', '1655', '1274', '36', '740', '1306939702', '2', 10, 4, 0, 1, ''),
(301, '20.TXT', '4921', '62628', 'NM', '', '', '', '', '', '', '1', '0', '-131', '0', '', '', '401679893.790', '0.000', '', '', '', '', '', '1306940363', '1', 176, 160, 3, 0, ''),
(302, '20.TXT', '4921', '62629', 'NK', '1306910302', 'B', '52.289', '0.245', '63.684', '-65.710', '2', '0', '-133', '352', '2', '0', '401679916.660', '0.062', '0', '0', '0', '61', '516', '1306910126', '1', 169, 172, 1, 0, ''),
(303, '20.TXT', '4921', '62629', 'NL', '1306915185', 'A', '52.449', '0.779', '56.580', '-20.504', '3', '0', '-128', '225', '2', '6', '401679887.990', '0.083', '0', '0', '0', '116', '2624', '1306915298', '2', 5, 1, 0, 1, ''),
(304, '20.TXT', '4921', '62629', 'NM', '1306916795', '0', '52.556', '0.445', '55.177', '-13.982', '6', '0', '-129', '475', '3', '46', '401679916.660', '0.062', '5575', '22588', '1375', '104', '340', '1306916855', '1', 191, 196, 3, 0, ''),
(305, '20.TXT', '4921', '62629', 'MA', '1306920366', '2', '52.467', '0.694', '53.578', '-5.377', '6', '1', '-118', '364', '3', '68', '401679929.970', '0.077', '306', '1520', '61', '105', '512', '1306920488', '2', 7, 1, 0, 1, ''),
(306, '20.TXT', '4921', '62629', 'NL', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401679922.420', '0.000', '', '', '', '', '', '1306921486', '1', 177, 196, 1, 0, ''),
(307, '20.TXT', '4921', '62629', 'NM', '1306922765', 'A', '52.407', '0.751', '63.425', '-63.162', '3', '0', '-134', '300', '3', '8', '401679929.870', '0.074', '0', '0', '0', '63', '2010', '1306922676', '1', 186, 196, 1, 0, ''),
(308, '20.TXT', '4921', '62629', 'MA', '', '', '', '', '', '', '1', '0', '-131', '0', '', '', '401679922.420', '0.000', '', '', '', '', '', '1306926567', '1', 175, 188, 3, 0, ''),
(309, '20.TXT', '4921', '62629', 'NP', '1306926976', 'A', '52.459', '0.681', '55.124', '14.745', '3', '0', '-130', '234', '2', '6', '401679975.110', '0.072', '0', '0', '0', '72', '1576', '1306926918', '1', 143, 188, 1, 0, ''),
(310, '20.TXT', '4921', '62629', 'NP', '1306932750', '1', '52.474', '0.690', '46.415', '-32.073', '7', '0', '-131', '682', '4', '58', '401679912.300', '0.080', '630', '2225', '178', '104', '460', '1306932950', '2', 10, 1, 0, 1, ''),
(311, '20.TXT', '4921', '62629', 'NN', '1306933432', '1', '52.464', '0.678', '48.973', '-17.282', '7', '0', '-125', '624', '4', '58', '401679918.410', '0.073', '889', '3181', '248', '79', '282', '1306933688', '1', 186, 200, 1, 0, ''),
(312, '20.TXT', '4921', '62626', 'MA', '1306920303', 'A', '52.633', '1.379', '53.980', '-5.812', '3', '1', '-117', '185', '2', '6', '401679608.860', '0.052', '0', '0', '0', '90', '602', '1306920272', '1', 1, 4, 0, 1, ''),
(313, '20.TXT', '4921', '62626', 'NP', '', '', '', '', '', '', '1', '0', '-135', '0', '', '', '401679645.700', '0.000', '', '', '', '', '', '1306921021', '1', 2, 4, 0, 1, ''),
(314, '20.TXT', '4921', '62626', 'NL', '', '', '', '', '', '', '1', '0', '-137', '0', '', '', '401679645.700', '0.000', '', '', '', '', '', '1306921143', '1', 182, 192, 2, 0, ''),
(315, '20.TXT', '4921', '62626', 'NN', '1306921597', 'B', '52.723', '1.787', '66.393', '77.243', '2', '0', '-130', '61', '1', '0', '401679685.230', '0.047', '0', '0', '0', '39', '2720', '1306921567', '1', 177, 192, 1, 0, ''),
(316, '20.TXT', '4921', '62626', 'NM', '1306922840', '1', '52.576', '1.337', '63.279', '-63.388', '4', '0', '-133', '370', '2', '60', '401679568.930', '0.056', '707', '1728', '288', '45', '1132', '1306922964', '1', 2, 4, 0, 1, ''),
(317, '20.TXT', '4921', '62626', 'MA', '1306926294', '0', '52.636', '1.403', '62.547', '-52.944', '7', '0', '-125', '658', '3', '56', '401679654.370', '0.051', '1561', '2457', '991', '53', '459', '1306926564', '2', 170, 188, 3, 0, ''),
(318, '20.TXT', '4921', '62626', 'NP', '1306927075', 'B', '51.468', '-0.020', '55.020', '13.713', '2', '0', '-129', '60', '1', '0', '401679594.400', '0.052', '0', '0', '0', '31', '6969', '1306927046', '1', 175, 188, 3, 0, ''),
(319, '20.TXT', '4921', '62626', 'NN', '1306927555', '1', '52.622', '1.373', '57.954', '28.949', '7', '0', '-125', '420', '3', '56', '401679644.560', '0.053', '633', '2388', '167', '81', '404', '1306927705', '1', 171, 188, 2, 0, ''),
(320, '20.TXT', '4921', '62626', 'NP', '1306932828', '2', '52.613', '1.406', '46.207', '-32.810', '5', '0', '-128', '313', '3', '66', '401679594.400', '0.052', '308', '691', '136', '124', '554', '1306932923', '2', 187, 180, 2, 0, ''),
(321, '20.TXT', '4921', '62626', 'NK', '', '', '', '', '', '', '1', '0', '-127', '0', '', '', '401679594.400', '0.000', '', '', '', '', '', '1306933482', '1', 183, 176, 3, 0, ''),
(322, '20.TXT', '4921', '62626', 'NN', '1306933326', '1', '52.621', '1.422', '48.880', '-18.075', '6', '0', '-125', '742', '3', '56', '401679599.720', '0.051', '950', '3567', '252', '86', '357', '1306933790', '3', 5, 4, 0, 1, ''),
(323, '20.TXT', '4921', '62626', 'NK', '1306939594', '0', '52.663', '1.809', '52.413', '0.462', '8', '0', '-122', '731', '2', '47', '401679672.600', '0.000', '14462', '424883', '492', '79', '1585', '1306939475', '2', 180, 172, 1, 0, ''),
(324, '20.TXT', '4921', '62626', 'NN', '1306939535', 'A', '52.607', '1.468', '40.046', '-63.855', '3', '0', '-134', '122', '3', '8', '401679616.120', '0.050', '0', '0', '0', '179', '9477', '1306939536', '1', 180, 172, 2, 0, ''),
(325, '20.TXT', '4921', '62626', 'MA', '', '', '', '', '', '', '1', '0', '-132', '0', '', '', '401679594.400', '0.000', '', '', '', '', '', '1306943891', '1', 176, 168, 2, 0, ''),
(326, '20.TXT', '4921', '62626', 'NL', '1306944854', '2', '52.645', '1.541', '59.895', '40.188', '4', '0', '-126', '242', '3', '67', '401679654.390', '0.047', '285', '351', '230', '153', '687', '1306944975', '2', 8, 4, 0, 1, ''),
(327, '20.TXT', '4921', '62626', 'NK', '1306945641', '1', '52.646', '1.520', '43.640', '-45.638', '6', '0', '-129', '606', '4', '57', '401679646.570', '0.048', '581', '979', '344', '89', '407', '1306945944', '2', 8, 4, 0, 1, ''),
(328, '20.TXT', '4921', '62626', 'NM', '', '', '', '', '', '', '1', '0', '-136', '0', '', '', '401679672.600', '0.000', '', '', '', '', '', '1306946427', '1', 8, 4, 0, 1, ''),
(329, '20.TXT', '4921', '62626', 'MA', '1306949931', 'A', '52.601', '1.549', '63.170', '61.295', '3', '0', '-126', '121', '2', '7', '401679672.600', '0.046', '0', '0', '0', '53', '20387', '1306949932', '1', 169, 160, 2, 0, ''),
(330, '20.TXT', '4921', '62626', 'NL', '1306950923', '1', '52.628', '1.483', '51.007', '-6.755', '12', '0', '-124', '781', '3', '56', '401679674.070', '0.050', '947', '5388', '166', '81', '298', '1306951254', '4', 10, 4, 0, 1, ''),
(331, '20.TXT', '4921', '62626', 'NM', '1306951976', '0', '52.623', '1.452', '53.255', '4.976', '8', '0', '-125', '602', '2', '56', '401679670.010', '0.050', '1644', '16477', '164', '77', '697', '1306952218', '2', 10, 4, 0, 1, ''),
(332, '21.TXT', '4921', '62620', 'NK', '1307024622', 'A', '52.377', '0.780', '54.557', '12.523', '3', '0', '-125', '360', '1', '0', '401679960.470', '0.077', '0', '0', '0', '84', '719', '1307024503', '1', 182, 168, 3, 0, ''),
(333, '21.TXT', '4921', '62620', 'NN', '1307025218', 'B', '52.432', '0.738', '41.147', '-58.259', '2', '0', '-136', '119', '1', '0', '401679960.470', '0.077', '0', '0', '0', '49', '1509', '1307025159', '1', 178, 164, 2, 0, ''),
(334, '21.TXT', '4921', '62620', 'NK', '1307030548', 'B', '52.413', '0.811', '46.055', '-33.504', '2', '0', '-134', '59', '1', '0', '401679942.030', '0.072', '0', '0', '0', '44', '1273', '1307030519', '1', 177, 160, 3, 0, '');
EOD;

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
if (!$res= $mysqli->query($sql_create))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// LOADING OUR DATA INTO THE TABLE
if (!$res = $mysqli->query($sql_insert))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}



// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28153645.html#a39238184
$sql = <<<EOD
SELECT MAX(location_class) AS location_class, ptt, latitude, longitude, FROM_UNIXTIME(location_date) FROM bird_data
WHERE location_class IN (1,2,3)
GROUP BY ptt, YEAR(FROM_UNIXTIME(location_date)), MONTH(FROM_UNIXTIME(location_date)), DAY(FROM_UNIXTIME(location_date));
EOD;

if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

while ($row = $res->fetch_object())
{
    print_r($row);
}

Open in new window

0
 

Author Comment

by:williamskellorn
ID: 39241689
Julian H - yes, the location_date column stores unix timestamps, though only as varchar strings - inappropriately, as I'm aware.

Where the cycle begins and ends is arbitrary - as long as all rows are assessed and for each ptt number, for each period of 24 hours, we have the coordinates and timestamp of the row with the best location class, and if there are more than one with that class, the one with the latest timestamp - one row per cycle, no alternates..I hope this is what you mean when you refer to alternates.

Many thanks for actually putting a script together. I will hopefully have time to give this a go within the next two days - tied up on another project.

I'll report back then and dole out some points.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39241955
You can run the script on my server.   Just click the link and see if it's giving you what you expect.
http://www.laprbass.com/RAY_temp_williamskellorn.php

Best regards, ~Ray
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39242094
In that case the query I posted should do the trick.

The storing of timestamp as varchar is not a major issue - it will result in a slight performance hit over having it as a native datetime but I don't think it is enough to put special effort into changing a working process. The MySQL FROM_UNIXTIME seems to do the job.
0
 

Author Comment

by:williamskellorn
ID: 39253370
Great, that query was spot-on.

In using FROM_UNIXTIME to convert the timestamp I believe the output date / time value will be given in the timezone of the SQL server - I'm now looking at ensuring it stays in UTC instead, but this shouldn't be too hard.

Many thanks!
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39253526
You are welcome - thanks for the points.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39253885
I believe the output date / time value will be given in the timezone of the SQL server
Yes, the server time in PHP is not connected to the SQL server.
0
 

Author Comment

by:williamskellorn
ID: 40037259
Revisiting this issue as I've come around again to a point where I can implement this. If more appropriate I'll ask as a new question.

In testing I've found that the query suggested consistently returns one timestamp and set of coordinates per 24 hour period, for each bird, as expected. However, the coordinates returned are those for the table row first encountered with a location_class value of 1,2 or 3 within the time window, rather than the row with the max location_class within the time window. When investigating this, printing out the 'location_class' value always gives the max value during the period, regardless of whether it is taken from same row as the coordinates chosen (which are always from the first row encountered with a location_class of 1,2 or 3).

Do I need to have another query (possibly using a temporary table in memory) to somehow ensure the location_class, coordinates and timestamp are chosen from the same row, which has the max value for location_class? At this point I care less if this is the first row encountered rather than the preferred latest row with the max location_class, as long as it is the max!

If necessary I'll provide simplified versions of the code in use and an examples of the input and output data where I can see this occurring.

Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now