Giving the following table (Position)
+--------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+----------------+
| position_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| vehicle_id | int(11) unsigned | YES | MUL | NULL | |
| location | point | NO | | NULL | |
| gps_time | datetime | NO | | NULL | |
+--------------------+---------------------+------+-----+---------+----------------+
I want to construct a lineString from the points which is location column to calculate the distance.
How to construct lineString from location column ? Something like
select vehicle_id, lineString(location) from position group by vehicle_id order by gps_time;
Here is the data
select position_id,vehicle_id,astext(location),gps_time from position order by gps_time;
+-------------+------------+-----------------------------+---------------------+
| position_id | vehicle_id | astext(location) | gps_time |
+-------------+------------+-----------------------------+---------------------+
| 3 | 1 | POINT(44.23912 15.307647) | 2014-11-23 06:02:17 |
| 2 | 1 | POINT(44.240063 15.307788) | 2014-11-23 08:54:11 |
| 1 | 1 | POINT(44.23884 15.307585) | 2014-11-23 11:50:10 |
| 4 | 1 | POINT(44.232296 15.304604) | 2014-11-24 07:24:29 |
| 5 | 1 | POINT(44.2332 15.304803) | 2014-11-24 07:24:39 |
| 6 | 1 | POINT(44.2332 15.304803) | 2014-11-24 07:24:49 |
| 7 | 1 | POINT(44.23428 15.3052) | 2014-11-24 07:24:59 |
+-------------+------------+-----------------------------+---------------------+
I want to calculate the distance between the points during specific gps_time. That is the distance between point of position_id=3 and the point of position_id=2 then the result will be calculated with distance between point of position_id=2 and point of position_id=1 and so on.
Expected Result like:
+------------+------------- +--------------------+---------------------+
| vehicle_id | distance(km) | start_time | end_time |
+------------+------------- +--------------------+---------------------+
| 1 | 1.2 |2014-11-23 06:02:17 | 2014-11-24 07:24:59 |
+------------+------------- +--------------------+---------------------+
distance value (1.2) is just a random value from my mind. it should be calculated from previous points.
