v1 - maya.public

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

serialnumber

varchar

√

null

device_rnt.serialnumber

Implied Constraint

senddate

timestamptz

35,6

√

null

receivedate

timestamptz

35,6

√

null

latitude

numeric

10,7

√

null

longitude

numeric

10,7

√

null

reason

varchar

2147483647

√

null

ignition_on

int4

√

null

speed

float4

8,8

√

null

movement_status

varchar

2147483647

√

null

odometer

numeric

10,2

√

null

relays

text

2147483647

√

null

switches

text

2147483647

√

null

relays_full

text

2147483647

√

null

switches_full

text

2147483647

√

null

switch_3

text

2147483647

√

null

switch_4

text

2147483647

√

null

secstatus

text

2147483647

√

null

rssi

text

2147483647

√

null

cell_id

text

2147483647

√

null

hdop

text

2147483647

√

null

carrier

text

2147483647

√

null

commstate

text

2147483647

√

null

network_connected

bool

√

null

network_roaming

bool

√

null

commstate_binary

bit

√

null

fix_status

text

2147483647

√

null

network_id

text

2147483647

√

null

satellites

text

2147483647

√

null

unit_status

text

2147483647

√

null

io_state

text

2147483647

√

null

voltage_in

text

2147483647

√

null

lmu_up_time

text

2147483647

√

null

ext_adc_input

text

2147483647

√

null

internal_battery

text

2147483647

√

null

securitystatusage

text

2147483647

√

null

j1708_switchedbatvoltage

text

2147483647

√

null

j1939_switchedbatvoltage

text

2147483647

√

null

j1939_dm1

text

2147483647

√

null

trip_time

text

2147483647

√

null

driver_id_1

text

2147483647

√

null

driver_id_2

text

2147483647

√

null

temp_sensor_1

text

2147483647

√

null

temp_sensor_2

text

2147483647

√

null

temp_sensor_3

text

2147483647

√

null

trip_distance

text

2147483647

√

null

total_distance

text

2147483647

√

null

ubicacion

text

2147483647

√

null

otros

hstore

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT d.serialnumber,
    e.senddate,
    e.receivedate,
    e.latitude,
    e.longitude,
    er.description AS reason,
        CASE e.ignition_on
            WHEN true THEN 1
            ELSE 0
        END AS ignition_on,
    e.speed,
    vms.description AS movement_status,
    e.odometer,
    "substring"((e.otros -> 'relays'::text), 14, 2) AS relays,
    "substring"((e.otros -> 'switches'::text), 14, 2) AS switches,
    (e.otros -> 'relays'::text) AS relays_full,
    (e.otros -> 'switches'::text) AS switches_full,
        CASE "substring"((e.otros -> 'switches'::text), 15, 1)
            WHEN '1'::text THEN 'off'::text
            ELSE 'on'::text
        END AS switch_3,
        CASE "substring"((e.otros -> 'switches'::text), 14, 1)
            WHEN '1'::text THEN 'off'::text
            ELSE 'on'::text
        END AS switch_4,
        CASE
            WHEN (("substring"((e.otros -> 'relays'::text), 14, 2) = '11'::text) AND ("substring"((e.otros -> 'switches'::text), 14, 2) = '00'::text)) THEN 'ok'::text
            WHEN (("substring"((e.otros -> 'relays'::text), 14, 2) = '10'::text) AND ("substring"((e.otros -> 'switches'::text), 14, 2) = '01'::text)) THEN 'ign_blocked'::text
            WHEN (("substring"((e.otros -> 'relays'::text), 14, 2) = '01'::text) AND ("substring"((e.otros -> 'switches'::text), 14, 2) = '10'::text)) THEN 'acc_blocked'::text
            WHEN (("substring"((e.otros -> 'relays'::text), 14, 2) = '00'::text) AND ("substring"((e.otros -> 'switches'::text), 14, 2) = '11'::text)) THEN 'acc_ign_blocked'::text
            ELSE 'unknown'::text
        END AS secstatus,
    (e.otros -> 'RSSI'::text) AS rssi,
    (e.otros -> 'CELL_ID'::text) AS cell_id,
    (e.otros -> 'HDOP'::text) AS hdop,
    (e.otros -> 'CARRIER'::text) AS carrier,
    (e.otros -> 'COMMSTATE'::text) AS commstate,
        CASE
            WHEN ((((((e.otros -> 'COMMSTATE'::text))::integer)::bit(8) & B'00001000'::"bit"))::integer = 8) THEN true
            ELSE false
        END AS network_connected,
        CASE
            WHEN ((((((e.otros -> 'COMMSTATE'::text))::integer)::bit(8) & B'00100000'::"bit"))::integer = 32) THEN true
            ELSE false
        END AS network_roaming,
    (((e.otros -> 'COMMSTATE'::text))::integer)::bit(8) AS commstate_binary,
    (e.otros -> 'FIX_STATUS'::text) AS fix_status,
    (e.otros -> 'NETWORK_ID'::text) AS network_id,
    (e.otros -> 'SATELLITES'::text) AS satellites,
    (e.otros -> 'UNIT_STATUS'::text) AS unit_status,
    (e.otros -> 'IO_STATE'::text) AS io_state,
    (e.otros -> 'VOLTAGE_IN'::text) AS voltage_in,
    (e.otros -> 'LMU_UP_TIME'::text) AS lmu_up_time,
    (e.otros -> 'EXT_ADC_INPUT'::text) AS ext_adc_input,
    (e.otros -> 'INTERNAL_BATTERY'::text) AS internal_battery,
    (e.otros -> 'securityStatusAge'::text) AS securitystatusage,
    (e.otros -> 'j1708_SwitchedBatVoltage'::text) AS j1708_switchedbatvoltage,
    (e.otros -> 'j1939_SwitchedBatVoltage'::text) AS j1939_switchedbatvoltage,
    (e.otros -> 'j1939_DM1'::text) AS j1939_dm1,
    (e.otros -> 'TRIP_TIME'::text) AS trip_time,
    (e.otros -> 'DRIVER_ID_1'::text) AS driver_id_1,
    (e.otros -> 'DRIVER_ID_2'::text) AS driver_id_2,
    (e.otros -> 'TEMP_SENSOR_1'::text) AS temp_sensor_1,
    (e.otros -> 'TEMP_SENSOR_2'::text) AS temp_sensor_2,
    (e.otros -> 'TEMP_SENSOR_3'::text) AS temp_sensor_3,
    (e.otros -> 'TRIP_DISTANCE'::text) AS trip_distance,
    (e.otros -> 'TOTAL_DISTANCE'::text) AS total_distance,
    (e.location -> 'ciudad'::text) AS ubicacion,
    e.otros
   FROM (((event_restored e
     JOIN device d ON ((d.iddevice = e.iddevice)))
     LEFT JOIN event_reason er ON ((er.id = e.reason)))
     LEFT JOIN vehicle_movementstatus vms ON ((vms.id = ((e.otros -> 'vehicle_movement_status'::text))::integer)))
  WHERE (d.idmodeldevice = 4)
  ORDER BY e.senddate DESC;

v1

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views