CODEWITHSUNDEEP
sqlpostgresqlgpspostgislatitude-longitude
Shanmugham Sundaram
Shanmugham Sundaram

Reputation: 25

Query to find idle time i.e GPS lat long showing same coordinates within 15 minutes

My GPS table in postgres has the schema with four fields as userid (int), latitude (float8), longitude (float8), recordedtime (time stamp). For every user, we get 2 records for every minute.

Need a query to find the users who spent more then 15 minutres in same GPS lat long value?

the sample records could be 

Userid, Latitiude, Longitude, recordedtime 
============================================
'ID1',12.089475408,76.089890,2019-11-24 19:10:01
'ID1',12.089475408,76.089890,2019-11-24 19:10:31
'ID1',12.089475408,76.089890,2019-11-24 19:11:01
'ID1',12.089475408,76.089890,2019-11-24 19:10:31
'ID1',12.089475408,76.089890,2019-11-24 19:12:01
'ID1',12.089475408,76.089890,2019-11-24 19:10:31
....

..

Could anyone please provide the required query?

Upvotes: 1

Views: 367

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

This CTE might be what you're looking for.

WITH j AS (
  SELECT userid,longitude,latitude,
    max(recordedtime)-min(recordedtime) AS timespent
  FROM t
  GROUP BY userid,longitude,latitude
) SELECT * FROM j
  WHERE timespent > interval '15 minute';

 userid | longitude | latitude  | timespent 
--------+-----------+-----------+-----------
 ID1    | 80.205480 | 13.082240 | 00:20:00
(1 Zeile)

Have you considered using PostGIS to deal with your points? Your use case seems pretty simple, but very quickly you might find yourself struggling to perform geospatial operations in your data model that are already solved in PostGIS.

In case you're interested, this is what you should do to create a geometry column based on your GPS coordinates:

ALTER TABLE t ADD COLUMN geom GEOMETRY;
UPDATE t SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude),4326);

The value 4326 in the ST_SetSRID function stands for the Reference System WGS84. Check which one suits your coordinates.

And this is how the query would look like:

WITH j AS (
  SELECT userid,geom,
    max(recordedtime)-min(recordedtime) AS timespent
  FROM t
  GROUP BY userid,geom
) SELECT userid,ST_AsText(geom),timespent FROM j
  WHERE timespent > interval '15 minute';

 userid |        st_astext         | timespent 
--------+--------------------------+-----------
 ID1    | POINT(80.20548 13.08224) | 00:20:00
(1 Zeile)

Note: As mentioned by @JGH, this use case assumes that the coordinates are identical in order to compute the time spent in certain location. Your coordinates have six decimal places, which gives you a precision of 0.111 m. Long story short, if the coordinates deviate by a few meters it won't work. In PostGIS using ST_Buffer you can easily create a buffer of a given size and with ST_Within you can check if one of your points is inside of the buffer. The query below should give an idea on how to do this:

(Buffer of 50 meters)

WITH j AS (
  SELECT userid, recordedtime, geom,
    ST_Buffer(geom::GEOGRAPHY,50)::GEOMETRY AS buffer
  FROM t
) SELECT t.userid, max(t.recordedTime)-min(t.recordedTime) AS timespent 
  FROM t,j
  WHERE t.userid=j.userid AND ST_Within(t.geom,j.buffer) 
  GROUP BY t.userid
  HAVING max(t.recordedTime)-min(t.recordedTime) > INTERVAL '15 minute';

 userid | timespent 
--------+-----------
 ID1    | 00:22:00
(1 Zeile)

Upvotes: 2

Related Questions