Skip to main content
Geographic Information Systems

Return to Question

removed thanks
Source Link
Vince
Vince
  • 20.5k
  • 16
  • 49
  • 65

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?

Thank you in advance.

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?

Thank you in advance.

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?

Edited formatting
Source Link
Joseph
Joseph
  • 76.7k
  • 8
  • 173
  • 286

I have device_iddevice_id, timestamptimestamp and GPS locationGPS 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

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 distdist. Also, I think if itsit's better to calculate distance right when we receive new GPS location and compare it with previous location and save the calculated distance into distdist column.

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

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 its 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 newbie in PostgreSQL. I am not sure which approach should I follow either earthdistance point-based or ST_Distance or is there something more appropriate that can fulfill my requirement?

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?

added 1252 characters in body
Source Link
sAm
sAm
  • 131
  • 4

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: enter image description here

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 its 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 newbie in PostgreSQL. I am not sure which approach should I 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?

Thank you in advance.

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: enter image description here

Now I need to calculate progressive road distance between GPS coordinates and save into 4th column called dist. Also, I think if its 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 newbie in PostgreSQL. I am not sure which approach should I 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?

Thank you in advance.

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 its 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 newbie in PostgreSQL. I am not sure which approach should I 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?

Thank you in advance.

added 186 characters in body
Source Link
sAm
sAm
  • 131
  • 4
Source Link
sAm
sAm
  • 131
  • 4