Search code examples
javapostgresqlpgrouting

Find distance using gps coordinate to next vertex

I am creating a Bus arrival prediction system using Java and Postgres.There is fixed route for each bus, so we are storing whole driving route as LineString in postgres.

enter image description here

There are 3 bus stoppage, and each bus stop lat lon is also stored as LineString in routes table. There is one more table which have stoppage name and lat lon details.

stoppage_details Table

Stoppage 1 -  lat1,lon1
Stoppage 2 -  lat2,lon2
Stoppage 3 -  lat3,lon3

I am looking Postgres function to solve below problem.

  1. How can I get total distance(660m) of route using LineString(Start point lat lon and End point lat lon).
  2. There is GPS System installed in each bus which send data at every 30 sec.I am using ST_ClosestPoint function to find nearest vertex from LineString. How can I find the distance between vertex and next and previous stoppage using LineString.
  3. Or Can someone suggest any other approach to solve above problem.
Solution

    1. if p1 through p8 are a linestring geometry in srid:4326 (long/lat) then you can get it length with st_length(line::geography) in meters.

    2. is a little more complicated but there are various approaches to it. For example you can use the linear referencing function to project your GPS point onto the line string and get the percentage along that where 0 is the start and 1.0 is the end. If you take each stoppage point and get its percentage the same way (maybe store it in your table also) then find the stoppage point less than the GPS percentage and greater than the GPS percentage will give you the adjacent stoppage points. Linear referencing function give you the ability to extract a substring based on a start and stop percentage of you linestring and you can get the length of each of them for your distances before and after the GPS point.