SQL Data problem (Tidal data)

[jassing]

I have been stairing at a problem for some time now; it's a personal project that I'm gong to release as freeware/donationware.

Given some tide data (http://co-ops.nos.noaa.gov/data_menu.shtml?bdate=20081118&edate=20081119&unit=1&shift=g&mins=6&datum=6&stn=9446484+Tacoma%2C+WA&type=Tide+Predictions&format=View+Datahttp://co-ops.nos.noaa.gov/data_menu.shtml?bdate=20081118&edate=20081119&unit=1&shift=g&mins=6&datum=6&stn=9446484+Tacoma%2C+WA&type=Tide+Predictions&format=View+Data)
and a given time "now", I need to determine the next low tide and the next high tide.

For some reason I keep stairing at it and can't come up with a solution that reliably works.

For each day; there are (typically) two lows & two highs; so I acn't just find teh lowest or highest value for that day; moreover, "now" could be 11pm which means that the next low or high tide could be the next day....

Any ideas?

[Dermot]

So is the date stored in a db like this http://co-ops.nos.noaa.gov/data_menu.shtml?bdate=20081118&=10&=18&edate=20081119&=10&=19&unit=1&shift=g&mins=6&datum=6&stn=9446484+Tacoma,+WA&type=Tide+Predictions&format=View+Data
I would store the date and time together like this 200811191630 and then format the current date and time the same way. That will make it very easy to find the next tide after the current time. You can sort the results by date/time and then grab the lowest and highest.

local CurrentDateTime = 200811191630
"SELECT * FROM TideData WHERE DateTime > "..CurrentDateTime.." ORDER BY DateTime"

Then grad the first 4 rows returned and figure out the lowest and highest.

[jassing]

So is the date stored in a db like this http://co-ops.nos.noaa.gov/data_menu.shtml?bdate=20081118&=10&=18&edate=20081119&=10&=19&unit=1&shift=g&mins=6&datum=6&stn=9446484+Tacoma,+WA&type=Tide+Predictions&format=View+Data
I would store the date and time together like this 200811191630 and then format the current date and time the same way. That will make it very easy to find the next tide after the current time. You can sort the results by date/time and then grab the lowest and highest.

local CurrentDateTime = 200811191630
"SELECT * FROM TideData WHERE DateTime > "..CurrentDateTime.." ORDER BY DateTime"

Then grad the first 4 rows returned and figure out the lowest and highest.

I'm not sure I follow you -- that would pull out potentially 2000 records; the 1st four records are not guaranteed to be the next lowest or next highest . they could be going up or down; but not the peak or valley of the graph...

[Dermot]

Use LIMIT to only return a set number

"SELECT * FROM TideData WHERE DateTime > "..CurrentDateTime.." ORDER BY DateTime LIMIT 4"

What you could do is say return the next 10 records and loop through them. Each time setting a variable to the height. On each loop you would compare the height to the variable, if it is greater then keep going, but if it is smaller then you know the last one was the next highest so break out of the loop. Do the reverse to find the next lowest.

[jassing]

Use LIMIT to only return a set number

"SELECT * FROM TideData WHERE DateTime > "..CurrentDateTime.." ORDER BY DateTime LIMIT 4"

What you could do is say return the next 10 records and loop through them. Each time setting a variable to the height. On each loop you would compare the height to the variable, if it is greater then keep going, but if it is smaller then you know the last one was the next highest so break out of the loop. Do the reverse to find the next lowest.

Not bad.. I was stryiing to do it with one loop collecting both the low & high at the same time... this would be slower, but more simple.
(See I told you I was staring at the problem too long!)
Thanks
-josh

[Dermot]

Looping through 10 or so records is going to be very fast so doing it twice will not make much difference. Could definetly be done in one loop but it will be much easier using 2.