I just started to work with spatial databases and I want to write a SQL(PostGIS) query for automatic generalizing of raw GPS-tracks (with fixed tracking frequency). The first thing I am wokring on is a query which identifies points of standstill in form of query like "x points within a distance of y meters" to replace massive point clouds by representative points. I already realized to snap points within a certain distance and count the snapped ones. In the picture below one can see a raw example track (small black points) and the centers of snapped points as colored circles (size = number of snapped points).

enter image description here

CREATE table simplified AS 
 SELECT count(raw.geom)::integer AS count, st_centroid(st_collect(raw.geom)) AS center
   FROM raw
  GROUP BY st_snaptogrid(raw.geom, 500, 0.5)
  ORDER BY count(raw.geom) DESC;

I would be as quite satisfied with this solution, but there is the time-problem: Imaging the track as a full-day-track in a city the person can return to places already visited before. In my example, the dark-blue circle represents the person's home which he visited twice but my query of course ignores that.

In this case, the sophisticated query should only collect points with contiguous timestamps (or id's), so that it would produce two representive points here. My first idea was a modification of my query to a 3d-version (time as third dimension), but it does not seem to work.

Does anybody have any advice for me? I hope that my question is clear.

Thank you for the line-idea. I realized to make and simplify a linestring as you can see in the screenshot below (dots are original points). enter image description here What I still need is to determine the places of rest (> x points in < x meters radius), ideally as one point with an arrival time and a leaving time... any other ideas?

  • 2
    Do you actually need the points for other purposes? Otherwise, it sounds like maybe just creating lines from the points, and then simplifying/generalizing those lines would serve your purpose. Oct 23, 2012 at 13:29
  • 2
    It's a fascinating problem. You might be able to glean some ideas from essentially the same question that was asked on the Mathematica site at mathematica.stackexchange.com/questions/2711. Not all the answers exploit the temporal dimension of the data (but mine does :-).
    – whuber
    Oct 23, 2012 at 13:53
  • @Anthony-GISCOE- that's an interesting approach. In case point features are needed, new ones could be created from the vertices of the generalized lines, or along the lines like here gis.stackexchange.com/questions/27102/…. I know, those still are not the original points!
    – andytilia
    Oct 23, 2012 at 13:57
  • @ Anthony: I absolutely need the "representation"-points of any stillstand and at least start- and endtime...
    – Berlin_J
    Oct 23, 2012 at 13:59
  • 1
    and it would be really useful to have a postgis-solution :)
    – Berlin_J
    Oct 23, 2012 at 14:05

2 Answers 2


If you really need all the points for visualisation, then you can create a line and st_simplify (which is Douglas Peucker implementation) would do the job quite nicely.

In some cases you do not even need to store all the points, so you could do filtering before saving point data, e.g. when subject does not move, do not store it. You can apply DouglasPeucker or some other basic filter before adding points to DB. Also some GPS providers (like Android Location API) can do initial filtering based on time and minimum distance automatically. In some cases you make keep duplicate data: prefiltered for fast visualisations and full log for archive. Plain storage is quite cheap nowadays.


Meanwhile, I found a solution for my problem:

First, I determined a "distance-type" for every point. If the point is closer as x meters to the next point, it is determined as "stop", otherwise as "move". Then, I started a window function like this:

     SELECT t1.id, t1.dist_type, t1."time", t1.the_geom, t1.group_flag, sum(t1.group_flag) OVER (ORDER BY t1.id) AS group_nr
FROM ( SELECT distances.id, distances.the_geom, distances."time", distances.dist_type, 
                    WHEN lag(distances.dist_type) OVER (ORDER BY distances.id) = distances.dist_type THEN NULL::integer
                    ELSE 1
                END AS group_flag
           FROM distances) t1;

The resulting table looks as follows:

enter image description here

The simple next step groups the "stop" points, identify the centroid of these point groups, and take minimum and maximum timestamps as arrival and leaving time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.