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).
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). 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?