php - PostgreSQL select query (GPS) -
the problem!
i have postgresql database on saving gps data of cars. i'm preparing history report , have show 2 different stutus each car according speed, firs stoped if car speed remains 0 within 5 minutes, if more 5 minutes have show parking. data gps on each 10 seconds. there way that? appreciated.
here 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) )
i have tackled problem myself creating postgresql function!
-- function: stop_func(bigint) -- drop function stop_func(bigint); create or replace function stop_func(in device_id_param bigint) returns table(coordinate geometry, difference interval) $body$ declare r record; stop_records stop_record[]; speed_zero boolean; prev_date_time timestamp; coordinate geometry; begin speed_zero:=false; r in select g.coordinate, g.date_time, g.speed gps_data_abl_soft g g.device_id=device_id_param order g.date_time loop if r.speed=0 if not speed_zero prev_date_time:=r.date_time; coordinate=r.coordinate; end if; speed_zero:=true; else if speed_zero stop_records=array_append(stop_records,(coordinate, r.date_time-prev_date_time)::stop_record); end if; speed_zero:=false; end if; end loop; return query select * unnest(stop_records); end $body$ language plpgsql volatile cost 100 rows 1000; alter function stop_func(bigint) owner dr;
usage of function
select st_asgeojson(st_transform(st_multi(st_union(coordinate)), 4326)) coordinate, difference stop_func(device_id, start_time, end_time) group difference;
sample result here:
Comments
Post a Comment