maya.public

Parameters

Name	Type	Mode
_creationdate	timestamp with time zone	IN
serialnumber	character varying	OUT
creationdate	timestamp with time zone	OUT
senddate	timestamp with time zone	OUT
latitude	double precision	OUT
longitude	double precision	OUT
location	character varying	OUT
ignition_on	boolean	OUT
speed	real	OUT
v	ARRAY	OUT
vp	ARRAY	OUT
tm	ARRAY	OUT
a	ARRAY	OUT

Definition

/* Extracts the information contained in the field cell_id (fuel sensor level info) of the device frame in an easily manageable format */
 WITH q AS (SELECT d.serialnumber,e.creationdate,e.senddate,e.latitude,e.longitude,e.ignition_on,e.speed,((e.location->'ciudad')::text||', '|| (e.location->'estado')::text) as location,regexp_split_to_array(e.otros->'cell_id',';') AS fls
 FROM event e JOIN device d ON d.iddevice = e.iddevice
        /* filter by creationdate and a senddate by a threshold of 1 day before and 2 days after */
        WHERE  e.senddate > _creationdate ORDER BY creationdate ),           
 q1 AS (SELECT serialnumber,creationdate,senddate,latitude,longitude,location,ignition_on,speed,regexp_split_to_array(fls[1]::varchar,E'\\|') v,regexp_split_to_array(fls[2],E'\\|') vp,regexp_split_to_array(fls[3],E'\\|') tm,regexp_split_to_array(fls[4],E'\\|') a from q)
SELECT serialnumber,creationdate,senddate,latitude::float,longitude::float,location,ignition_on,speed::real,v[2:4]::int[],vp[2:4]::int[],tm[2:4]::int[],a[2:3]::int[]
FROM q1 where array_length(v,1) > 0 and array_length(vp,1) > 0 and array_length(tm,1) > 0 ;

ot_ro_get_fls(timestamp with time zone)

Parameters

Definition