2

I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database.

Now I have a table which holds all the track points of these tracks.

I would like to build a network between all these points so that at the end I can calculate the shortest path from a point to any point. In all I have 28 track points where 26 of these points are distinct.

I would like some suggestions how can I determine the source and the target of each point in order to create a network.

Any suggestions please?

Improve this question
1
  • hint. GPS data has correct order allready there. So you can use ' SELECT track_id , ST_COLLECT(the_geom)as multi_geom from gps_track order by id , group by track_id ' then you can give that subquery result to SELECT ST_MakeLine' Read about hose functions and you will find correct answer for your use case
    – simpleuser001
    Commented Dec 12, 2012 at 12:00

1 Answer 1

Reset to default
1

I presume your 'track' points table in fact has for each record, a start point and end point that relates to a line that represents each track segment?

The steps are:

  1. If you don't already have these, add two columns to your 'track' table that represent the start point and end point of each track segment.

  2. Create a new table that will hold distinct points (called 'points' table here). It will need a unique id, and point field.

  3. Select all the distinct start points in your 'track' table, and insert into the points table above.

  4. Select all the distinct end points in your 'track' table, that don't already exist in your 'points' table.

  5. What you should now have, is a record in your 'points' table, for each unique point from your track table.

  6. Add two columns to your 'track' table, called something like startid and endid.

  7. Update the startid column of your 'track' table, by joining the 'track' table to the 'point' table using the start point and point fields from 'track' and 'point' tables respectively.

  8. Do the same as 7. but for endid and endpoint.

  9. You now have routable network information in your 'track' table in that end endid will match a corresponding startid of the next segment in the network. You will then be able to pass these id fields to pgrouting functions to to LCPA.

Improve this answer

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.