CREATE TABLE IF NOT EXISTS
`data_traccar_position` (
`Guid` VARCHAR(64) NOT NULL PRIMARY KEY,
`Package` VARCHAR(255) DEFAULT NULL,
`DeviceId` INT DEFAULT NULL,
`DeviceName` VARCHAR(255) DEFAULT NULL,
`DeviceModel` VARCHAR(128) DEFAULT NULL,
`DeviceCategory` VARCHAR(64) DEFAULT NULL,
`DeviceStatus` VARCHAR(32) DEFAULT NULL,
`Lat` DECIMAL(10,7) DEFAULT 0,
`Lng` DECIMAL(10,7) DEFAULT 0,
`Altitude` DECIMAL(8,1) DEFAULT 0,
`Speed` DECIMAL(8,2) DEFAULT 0,
`Course` DECIMAL(6,2) DEFAULT 0,
`Accuracy` DECIMAL(8,1) DEFAULT 0,
`BatteryLevel` DECIMAL(5,2) DEFAULT NULL,
`Address` TEXT DEFAULT NULL,
`PositionTime` DATETIME DEFAULT NULL,
`CreateTime` DATETIME DEFAULT CURRENT_TIMESTAMP,
`Module` VARCHAR(255) DEFAULT 'ObjServiceTraccar',
KEY `idx_device` (`DeviceId`),
KEY `idx_time` (`CreateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
INSERT INTO data_traccar_position
(Guid, Package, DeviceId, DeviceName,
DeviceModel, DeviceCategory, DeviceStatus,
Lat, Lng, Altitude, Speed, Course,
Accuracy, BatteryLevel, Address,
PositionTime)
VALUES
('{guid}', '{package}', {device_id},
'{device_name}', '{device_model}',
'{device_category}', '{device_status}',
{lat}, {lng}, {altitude}, {speed},
{course}, {accuracy}, {battery_level},
'{address}', '{position_time}')
SELECT t1.DeviceName, t1.DeviceCategory,
t1.DeviceStatus, t1.Lat, t1.Lng,
t1.Speed, t1.BatteryLevel,
t1.Address, t1.PositionTime
FROM data_traccar_position t1
INNER JOIN (
SELECT DeviceId, MAX(CreateTime) AS MaxTime
FROM data_traccar_position
WHERE Package = '{package}'
GROUP BY DeviceId
) t2 ON t1.DeviceId = t2.DeviceId
AND t1.CreateTime = t2.MaxTime
ORDER BY t1.DeviceName
SELECT Lat, Lng, Speed, Altitude,
BatteryLevel, Address, PositionTime
FROM data_traccar_position
WHERE DeviceId = {device_id}
AND Package = '{package}'
ORDER BY PositionTime DESC
LIMIT {limit}