Bit of a newbie here, trying to work out whether any PostGIS functions can help with my task. Apologies in advance if my use of GIS terminology is not up to scratch yet.

I have a large database of GPS points, ie lat/long coordinate pairs (large being "millions" of points). Currently they have no relational information, so they are not linked together.

However, they were collected over the UK rail network, typically every few metres. So all points map to rail tracks.

I need to process these points and link them together, so they map onto real railway lines. I want to identify which GPS points are buffers (ie ends of track) and points (in the rail sense - ie where track splits) as well as those that are "just sections of track". This needs to be a process that runs automatically with no user intervention.

There are many areas where multiple tracks run in parallel, or areas such as large stations with lots of points & sidings, where there are large numbers of GPS points in a small area.

Being rail, there is a limit to how much the line will curve, so that must be a key feature to aid analysis. As the GPS points are close together, it should be possible to see which form lines that could be track, and dismiss those that turn too sharply (which must be points from parallel tracks).

Initially I had been planning to write some java code that would pick GPS points from the DB that fall within a limited geographical area of each other, and look for GPS points within that region that continued in a straight-ish line. The add/update "previous node id" and "next node id" fields for each point. Then repeat, using the last linked node as the centre of the next geographical region to analyse. When the end of a track was reached, then I'd pick the next "untraversed" point in the DB and start again. Then repeat this cycle until every point in the DB had been traversed.

Initially the data is in a mySQL database table. I'd added an extra field, in which I'd converted the lat/long pairs to a geo "Point" field. Then used some rudimentary "MBRContains" queries to return points within a certain area (eg 100m2). I was then going to write some java code that explored/traversed the points, with repeated calls to get points within small areas.

So my question(s) are:

Does PostGIS offers any functions that could assist with the task of identifying related points ?

Or are there any better approaches out there to achieve this ?

Maybe there are tools/routines to do just this kind of thing, but I don't know the terminology enough to know about it yet :)

Thanks for any advice.

  • To clarify, the data has already been recorded, and there are no timestamps or similar present. Just lat/long pairs. It's also possible that the data has been recorded in different passes over the same tracks at different days/times.
    – fatbadger
    Jan 28, 2014 at 11:33

4 Answers 4


PostGIS answer. First change that long/lat to geometry type. If you have imported your gps data from NMEA log or similiar source then they are already in correct order ( one gps, one nmea log , import to db using serial for each line). I assume that you have railroad network data as linestrings in db.

To make simple match which gps point on what linestring you can use something like SELECT gid, ST_distance(rr.geom, gps.geom) FROM railroad as rr, gps WHERE gps.id = 1 ORDER BY ST_Distance(rr.geom, gps.geom) would return you closest railroad geometry. There are option to limit search using spatial relations etc.

If you search here using Postgis keyword you will find pretty much all answers for your questions (Nearest neighbor , closest line , routing , solving topology problems etc.) I wont add more details to this post because question is so open.

Q: Does PostGIS/Postgresql offers any functions that could assist with the task of identifying related points ? A: it offers all tools you need

See also QGIS , GRASS....

  • Thanks. I don't have any linestrings - just a load of GPS points... so I need to pick one, then start working out which surrounding points could lie in a straight(ish) line from it, and start traversing.
    – fatbadger
    Jan 30, 2014 at 11:53

GPS software solves this problem by looking at the time stamp on the waypoints, and 'joins the dots' on this basis to make tracks. If your waypoints have time data you can do this.

  • Thanks IanS - unfortunately the data has already been recorded, and no timestamps. It's also possible that the data has been recorded in different passes over the same tracks at different days/times.
    – fatbadger
    Jan 28, 2014 at 11:33

You need somehow to identify your points when it's comming to database. Each source must have an ID. So, my GPS will send my points with my ID and your GPS (you are in other track) will send points with your ID.

Will be easy to connect points with same ID number.

Maybe an user login? Cellphone number? Software serial number? E-Mail?

  • Thanks, but the data has already been recorded, and I don't have this information...
    – fatbadger
    Jan 28, 2014 at 11:31
  • Can you post an image? Give an order to random points may be a bit difficult. We need to know how close the track is from other and points disposition. A simple ST_Distance may do the job.
    – Magno C
    Jan 28, 2014 at 12:18

If you don't have the timestamps it is more difficult but not impossible if you know at least the sequence in which the points were recorded. I don't think PostGIS has a function to solve this out of the box. If your points are dense enough, look for map-matching algorithms and path inference algorithms. They should work decently well given that your network (rail) is relatively sparse.

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.