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. Commented Sep 10, 2015 at 12:51
  • click for [blog]: roaddistance.in "Road Distance Between Two Places" 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? 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! 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 Commented Sep 11, 2015 at 16:57

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.