2

I am working on tracking system that track location of vehicles on the map and tell their total distance from the beginning till today.

I have device_id, timestamp and GPS location in the table like this:

30 | 2015-09-08 14:16:34 | 0101000020E610000052CD51EC0D733A4080F7E9D3852F5040
30 | 2015-09-08 14:17:01 | 0101000020E6100000C323963902513A40988FFF5D7A315040
30 | 2015-09-08 14:17:15 | 0101000020E6100000046B4194D4383A40633CEF6B49325040
30 | 2015-09-08 14:19:14 | 0101000020E61000006CE7A0F07E213A40279E584C91325040
30 | 2015-09-08 14:19:26 | 0101000020E6100000C7C1001D410B3A40BF4B040539335040
30 | 2015-09-08 14:20:23 | 0101000020E6100000728C648F50EF3940613B736AC2345040
30 | 2015-09-08 14:20:44 | 0101000020E61000002BE1647671E439405EDFE23CF7365040
30 | 2015-09-08 14:20:58 | 0101000020E61000005399BDC7F4D33940033FAA61BF385040
30 | 2015-09-08 14:21:10 | 0101000020E6100000E38FFD874FC33940830FCAA07F3A5040
30 | 2015-09-08 14:21:23 | 0101000020E6100000904C874ECF9D3940800640811C3E5040
30 | 2015-09-08 14:22:01 | 0101000020E61000003AAA3F67778B394044BD851A2A3F5040
30 | 2015-09-08 14:22:14 | 0101000020E610000084C1903067833940B349D97D223F5040
30 | 2015-09-08 14:22:26 | 0101000020E6100000E2B5A6D48078394013697638153F5040
30 | 2015-09-08 14:24:30 | 0101000020E6100000BE1D9727C6E94140404BFCF61A3F5040
30 | 2015-09-08 14:25:07 | 0101000020E6100000C3F92F6BBD78394081C75CBE1A3F5040
30 | 2015-09-08 14:26:39 | 0101000020E6100000F3B116FABB783940522CB7B41A3F5040

Now I need to calculate progressive road distance between GPS coordinates and save into 4th column called dist. Also, I think if it's better to calculate distance right when we receive new GPS location and compare it with previous location and save the calculated distance into dist column.

I am quite a newbie in PostgreSQL. I am not sure which approach I should follow, either earthdistance point-based or ST_Distance or is there something more appropriate that can fulfill my requirement?

Update:

After some research I found neither earth distance nor ST_Distance gives distance of road network. Is there any way to calculate road distance between GPS coordinates?

Improve this question
2
  • 1
    You should show the columns as text, not an image, otherwise nobody can actually use the data as a sample for tests.
    – Craig Ringer
    Commented Sep 10, 2015 at 12:51
  • click for [blog]: roaddistance.in "Road Distance Between Two Places"
    – user64460
    Commented Dec 29, 2015 at 8:51

1 Answer 1

Reset to default
4

So your GPS coordinates are far apart? If they are close - eg. short segments along a road, then "straight line" is good enough.

There is an open source extension for PostGIS that can calculate road distances. It is called pgRouting. You will also need to create a road database - typically OpenStreetMaps is used.

Improve this answer
3
  • Thank you. "Straight line" you mean point-based of earthdistance? Also, is it so pgRouting with openStreetMap best supported in US not in Europe relatively?
    – sAm
    Commented Sep 11, 2015 at 7:49
  • Straight Line, If we're talking about a sphere, then I meant "Great Circle Distance". Use PostGIS's functions, or use the Haversine Function to calculate the great circle distance (=shortest distance between two points on the surface of a sphere). If anything I would expect OpenStreetMaps to have better European coverage because that is where it started, but its coverage is wide - it even includes Antarctica!
    – winwaed
    Commented Sep 11, 2015 at 14:03
  • I decided to go with ST_distance function of PostGIS. I tested it with the new entries, its working quite fine. With my existing table design, now I need to make update query using ST_Distance that calculate progressive distance of existing GPS coordinates and store into distance column. Any idea how that can be achieved? Thanks
    – sAm
    Commented Sep 11, 2015 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.