Search code examples
postgresqlpostgis

PostGIS detect crossing with timestamp

I need to store a series of GPS points with timestamps in the database (traces of various vehicles).

Initially I wanted to write something on my own but it would involve a bit more computational power, then just having it come as a result of a single query.

I explored a bit and came across PostGIS, but I'm not sure if it's suitable or possible to solve this problem.

The idea is to check if a two vehicles passed each other at the same time.

I have a table with the coordinates, each coordinate is in a separate row, and each row has a timestamp associated with it.

The table has following schema (vehicle_id, latitude, longitude, timestamp).

So given multiple coordinates of a vehicles I need to check if it has crossed with other vehicles at the same time. I found that I could use ST_MakeLine to create a line string from a sequence of GPS points, and saw that there are different intersection functions, but that requires coordinates to match perfectly and here the offset may be let's say 30 meters and timestamp has to be taken in account.

Any answer would help.

Thanks

Solution

  • If I understood your use case correctly, I believe you don't need to create LineStrings to check if your trajectory intersects or gets close in a certain point in time.

    Data Sample:

    CREATE TABLE t (vehicle_id INT, longitude NUMERIC, latitude NUMERIC, ts TIMESTAMP);

INSERT INTO t VALUES (1,1,1.1111,'2019-05-01 15:30:00'),
                     (1,1,2.1112,'2019-05-01 15:40:00'),
                     (1,1,3.1111,'2019-05-01 15:50:00'),

                     (2,2,2.1111,'2019-05-01 15:30:00'),
                     (2,1,2.1111,'2019-05-01 15:40:00'),
                     (2,1,4.1111,'2019-05-01 15:05:00');

    As you can see in the sample data above, vehicle_id 1 and 2 are close (less than 30m) to each other at 2019-05-01 15:40:00, which can be found using a query like this:

    SELECT 
  t1.vehicle_id,t2.vehicle_id,t1.ts, 
  ST_AsText(ST_MakePoint(t1.longitude,t1.latitude)::GEOGRAPHY) AS p1,
  ST_AsText(ST_MakePoint(t2.longitude,t2.latitude)::GEOGRAPHY) AS p2,
  ST_Distance(
    ST_MakePoint(t1.longitude,t1.latitude)::GEOGRAPHY,
    ST_MakePoint(t2.longitude,t2.latitude)::GEOGRAPHY) AS distance
FROM t t1, t t2
WHERE 
  t1.vehicle_id <> t2.vehicle_id AND
  t1.ts = t2.ts AND
  ST_Distance(
    ST_MakePoint(t1.longitude,t1.latitude)::GEOGRAPHY,
    ST_MakePoint(t2.longitude,t2.latitude)::GEOGRAPHY) <= 30


 vehicle_id | vehicle_id |         ts          |       p1        |       p2        |  distance   
------------+------------+---------------------+-----------------+-----------------+-------------
          1 |          2 | 2019-05-01 15:40:00 | POINT(1 2.1112) | POINT(1 2.1111) | 11.05757826
          2 |          1 | 2019-05-01 15:40:00 | POINT(1 2.1111) | POINT(1 2.1112) | 11.05757826
(2 rows)

    As you can see, the result is sort of duplicated since 1 is close to 2 and 2 is close to 1 at the same time. You can correct this using DISTINCT ON(), but since I'm not familiar with your data I guess you better adjust this yourself.

    Note that the data type is GEOGRAPHY and not GEOMETRY. It's because distances with ST_Distance over geometries are calculated in degrees, and using geography it 's in meters.

    EDIT: To address a question mentioned in comments.

    To avoid the overhead of having to create geography records in execution time, you might want to already store the coordinates as geography. In that case the table would look like this ..

    CREATE TABLE t (vehicle_id INT, geom GEOGRAPHY, ts TIMESTAMP);

    And you could populate it like this.

    INSERT INTO t (vehicle_id, geom, ts) 
VALUES (1,ST_MakePoint(1,1.1111),'2019-05-01 15:30:00');

    In case you want to avoid having to populate the table again, you might want to just move the data to another column and get rid (if you wish) of latitude and longitude:

    ALTER TABLE t ADD COLUMN geom GEOGRAPHY;
UPDATE t SET geom = ST_MakePoint(longitude,latitude);
ALTER TABLE t DROP COLUMN longitude, DROP COLUMN latitude;
CREATE INDEX idx_point ON t USING GIST(geom);
SELECT vehicle_id,ts,ST_AsText(geom) FROM t;

 vehicle_id |         ts          |    st_astext    
------------+---------------------+-----------------
          1 | 2019-05-01 15:30:00 | POINT(1 1.1111)
          1 | 2019-05-01 15:40:00 | POINT(1 2.1112)
          1 | 2019-05-01 15:50:00 | POINT(1 3.1111)
          2 | 2019-05-01 15:30:00 | POINT(2 2.1111)
          2 | 2019-05-01 15:40:00 | POINT(1 2.1111)
          2 | 2019-05-01 15:05:00 | POINT(1 4.1111)
(6 rows)