A newbie-question:

I've imported the segments of several GPX-Tracks in a PostGIS-Table. Now I'm looking for a query that returns all segments that:

  1. are spatially overlapping (within a specific buffer-distance) and

  2. at about the same time.

I've tried to use ST_Intersects, ST_Buffer and standard SQL-queries for the time-aspect. Alas, no attempt was successful. Does someone have a hint how such a query could look like?

Thank you in advance.


I would recommend you use ST_HausdorffDistance() to caluclate the similarity of your GPS tracks; using ST_Buffer() or ST_Distance() will cause you issues if your tracks get near to each other at some point, but otherwise are divergent.

Hausdorff distance is a bit funny to get your head around, but it's ideal for this sort of query, and after a bit of trial and error, you can usually settle on a good value for what you consider similar.

The time part of the query can be best handled with the TIMESTAMP and INTERVAL types built in to PostgreSQL. Assuming you have one row per track, and each track has a single TIMESTAMP representing the start time say, then queries like these work well:

SELECT TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' < INTERVAL '20 days';

Which returns true;

SELECT TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' < INTERVAL '20 minutes';

Which returns false.

So your overall query should look something like this:

SELECT a.geom FROM gps a, gps b
  WHERE ST_HausdorffDistance(a.geom, b.geom) < 23.0
  AND b.ts <= a.ts
  AND a.ts - b.ts < INTERVAL '20 minutes';

So we're checking for the similarity of two linestrings, then only if one timestamp is less than another (to avoid two records being compared on both sides) do we see if they're temporally similar.

Caveat: I've not tried this on real data, but the syntax works on my database.

  • 1
    +1 Is there perhaps some simple way to apply the time part of the query first and then the spatial part? It is plausible that such a hierarchical approach could be much more efficient because it might involve much less spatial comparison. – whuber Jun 8 '12 at 15:37
  • Probably. My SQL-fu isn't strong, but it should be fairly easy to select based on interval, and then run the spatial query on that subset. Something like SELECT <spatial query> FROM (SELECT <temporal query>) Running EXPLAIN on both queries will give a good comparison of execution time. – MerseyViking Jun 8 '12 at 16:05
  • 2
    @whuber, How the query is evaluated is decided by costs defined in the functions and by statistics. In most cases the query planner makes a great job to make things in the right order, but if it seems to not be optimized it is possible to play with those "cost" values. Most spatial functions has cost 100 (including ST_Hausdorffdistance) compared to 1 that is the default in general. – Nicklas Avén Jun 9 '12 at 18:35
  • Thank you very much for your Answers and Input. I found an other method by simply calculate the distance from each track-point to all the others. When within a specific range, time is compared with a time-buffer. The calculation is a bit time-consuming, but does its task. Using ST_HausdorffDistance seems very attractive, I'll give another try exploring these possibilities. Thank you again for the support and a nice weekend to everyone :-D – user8084 Jun 15 '12 at 14:30

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.