DBGrid - how to show the differnce between a column value in two adjacent rows as another column?

Question

Let's say I record my car's latitude & longitude every minute or so, adding a row to a table each time.

I want to have a DB grid with 4 columns

latitude
longitude
distance since last measurment
curent street address, if known

Number 4, I can try to retrieve from Google Maps and I either get a text or blank, so let's ignore that.

How do I get #3? Should I calculate it in my application or in MySql (using a stored procedure or just a complicated SELECT)?

Whichever answer, can someone provide some sample code, or a link to an example? Google is not my friend today :-(

Please define distance. As the crow flies? Or shortest route using roads? — David Heffernan, Apr 19 '13 at 06:48

score 5 · Accepted Answer · answered Apr 19 '13 at 03:10

5

You can do it either way:

Calculate it in your query, and display it in a calculated column (such as when showing line totals where you're displaying ITEM_PRICE, QUANTITY, ITEM_PRICE * QUANTITY AS LINE_COST.
Calculate it in your application, using a calculated field (double-click the TTable component to open the Fields Editor, add field, set the type to ftCalculated, and write code in the OnCalcEvent for that calculated field.

The first choice is usually preferable, because the DB is usually more efficient at doing those sorts of thing on sets of data than when your code does it by row instead. (Set operations are obviously more efficient than row operations.)

answered Apr 19 '13 at 03:10

Ken White

123,280
14
225
444

+1 Thanks (as always), @KenWhite, for the advice. Yes, I suspected that I wanted to push the work down onto the databse if I could. I have a formula to calcullate distance, given 2 @ lat/long pairs). I will continue to Gogole to see if some has already published a MySql stored procedure to do this. If so, I will post here for future reference. – Mawg says reinstate Monica Apr 19 '13 at 03:22
1

I would do the calculation in the application using a calculated field as described (in this case). – alzaimar Apr 19 '13 at 05:52
I would do the calculation on the client side, using calculated fields. This way the data transferred from the server is reduced. The client usually has plenty of processing power for simple calculations. Server side only makes sense if that reduces either the transferred data size or improves speed. – dummzeuch Apr 19 '13 at 07:20

score 1 · Answer 2 · edited May 23 '17 at 11:43

Ken’s answer is goes far as it goes, because the most difficult task of solving your problem is getting data from two rows at the same time in SQL. If you were using Oracle or SQL Server, you could use the analytical/windowing LAG function. With MySQL you have to do it yourself. Here are 2 articles on how to simulate LAG in MySQL

http://www.onlamp.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-ranking-functions-with-mysql.html?page=1

http://explainextended.com/2009/03/12/analytic-functions-optimizing-lag-lead-first_value-last_value/

Plus there are StackOverflow questions on how to get data from the previous row.

How do I lag columns in MySQL?

Calculate delta(difference of current and previous row) in sql

I cannot offer any opinion on any of them because I seldom work with MySQL.