PostgreSQL select query (GPS)

Question

The problem!

I have a PostgreSQL database on which I am saving GPS data of cars. Now I'm preparing history report and I have to show two different stutus to each car according to their speed, firs stoped if car speed remains 0 within 5 minutes, if more than 5 minutes then I have to show parking. I get data from GPS on each 10 seconds. Is there any way to do that? any help is appreciated.

Here is the table structure:

CREATE TABLE gps_data_abl_soft
(
  id bigint NOT NULL DEFAULT nextval('gps_data_id_seq'::regclass),
  device_id bigint NOT NULL DEFAULT (-1),
  keyword character varying(30),
  coordinate geometry,
  date_time timestamp without time zone NOT NULL DEFAULT now(),
  message character varying(200) NOT NULL DEFAULT ''::character varying,
  speed real NOT NULL DEFAULT 0.0,
  angle real NOT NULL DEFAULT 0.0,
  CONSTRAINT gps_data_pkey PRIMARY KEY (id)
)

Here is the sample data from my table:

PostgreSQL select query (GPS)

1 Answers