-1

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.
raw
  • 409
  • 1
  • 5
  • 11

1 Answers1

0

  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.

Stephen Woodbridge
  • 1,100
  • 1
  • 8
  • 16
  • I tried select ST_length(line::geography) FROM routes; but it is not giving exact length. And is there any way to find out previous and next stoppage of any GPS coordinate on linestring ? – raw Jan 14 '17 at 16:15
  • If your linstring is in srid:4326 (WGS84) then you need to project it into an appropriate projection to compute the length. Casting it to a geography does this by projecting it to something like global mercator projection which is in meters. You can try st_length_spheriod() http://postgis.net/docs/manual-1.4/ST_Length_Spheroid.html which might give you better control over which spheriod you want to project it into. – Stephen Woodbridge Jan 14 '17 at 20:51
  • regarding point 2. I described one way to do this. There is no "canned" function that does extactly what you asked for. Postgis is a set of tools that allow you to build more complex solutions. Many users write pgplsql stored procedures to accomplish more complex or specific tasks. I recommend "PostGIS in Action" http://www.postgis.us/ – Stephen Woodbridge Jan 14 '17 at 20:55