localhorst/LocationHub
SHA256
1
0
Fork 0
Code Issues 2 Pull Requests 1 Actions Packages Projects Releases Wiki Activity

Merge pull request 'Add timestamps of locations providers' (#18) from feat/timestamps into main

Browse Source 
Reviewed-on: #18
Reviewed-by: Philipp Schweizer <Philipp.schw@directbox.com>
This commit is contained in:
Hendrik Schutter 2025-02-14 21:22:57 +01:00
commit a9c8525e6e
6 changed files with 48 additions and 14 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

4
server/sql/tables.sql
View File

@ -5,7 +5,7 @@ CREATE TABLE IF NOT EXISTS lp_ttn_end_device_uplinks (
dev_eui VARCHAR(255),
join_eui VARCHAR(255),
dev_addr VARCHAR(255),
received_at_utc DATE,
received_at_utc DATE NOT NULL,
battery NUMERIC,
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
@ -16,6 +16,7 @@ CREATE TABLE IF NOT EXISTS wifi_scan (
lp_ttn_end_device_uplinks_id UUID,
mac VARCHAR(255),
rssi NUMERIC,
scanned_at_utc TIMESTAMP NOT NULL,
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
@ -63,6 +64,7 @@ CREATE TABLE IF NOT EXISTS location (
gnss_longitude DOUBLE,
ttn_gw_latitude DOUBLE,
ttn_gw_longitude DOUBLE,
gnss_location_at_utc TIMESTAMP,
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)

42
server/src/controller/ttnController.ts
View File

@ -42,23 +42,38 @@ router.post(
)?.measurementValue,
});
const messageData = message.uplink_message.decoded_payload?.messages[0];
const latitudeData = messageData?.find((e) => e.type === "Latitude");
const longitudeData = messageData?.find((e) => e.type === "Longitude");
const gnnsLocation = {
latitude: message.uplink_message.decoded_payload?.messages[0].find(
(e) => e.type === "Latitude"
)?.measurementValue,
longitude: message.uplink_message.decoded_payload?.messages[0].find(
(e) => e.type === "Longitude"
)?.measurementValue,
latitude: latitudeData?.measurementValue,
longitude: longitudeData?.measurementValue,
};
const gnssTimestamp = {
timestamp: latitudeData?.timestamp
? new Date(latitudeData.timestamp)
: longitudeData?.timestamp
? new Date(longitudeData.timestamp)
: undefined,
};
const wifiMessage =
message.uplink_message.decoded_payload?.messages[0].find(
(e) => e.type === "Wi-Fi Scan"
);
const wifiScans =
message.uplink_message.decoded_payload?.messages[0]
.find((e) => e.type === "Wi-Fi Scan")
?.measurementValue?.map((w) => ({
lp_ttn_end_device_uplinks_id,
mac: w.mac,
rssi: w.rssi,
})) ?? [];
wifiMessage?.measurementValue?.map((w) => ({
lp_ttn_end_device_uplinks_id,
mac: w.mac,
rssi: w.rssi,
scanned_at_utc: wifiMessage?.timestamp
? new Date(wifiMessage.timestamp)
: undefined,
})) ?? [];
const ttnGatewayReceptions = message.uplink_message.rx_metadata.map(
(g) => ({
@ -98,6 +113,7 @@ router.post(
longitude: gnnsLocation.longitude,
}
: undefined,
gnss_timestamp: gnssTimestamp.timestamp,
});
};
createDatabaseEntries().then();

4
server/src/models/location.ts
View File

@ -10,6 +10,7 @@ export class Location extends Model {
public gnss_longitude!: number;
public ttn_gw_latitude!: number;
public ttn_gw_longitude!: number;
public gnss_location_at_utc!: Date;
public created_at_utc!: Date;
public updated_at_utc!: Date;
}
@ -42,6 +43,9 @@ Location.init(
ttn_gw_longitude: {
type: DataTypes.NUMBER,
},
gnss_location_at_utc: {
type: DataTypes.DATE,
},
created_at_utc: {
type: DataTypes.DATE,
defaultValue: DataTypes.NOW,

6
server/src/models/wifiScan.ts
View File

@ -6,6 +6,7 @@ export class WifiScan extends Model {
public wifi_scan_id!: string;
public mac!: string;
public rssi!: number;
public scanned_at_utc!: Date;
public created_at_utc!: Date;
public updated_at_utc!: Date;
}
@ -30,6 +31,11 @@ WifiScan.init(
type: DataTypes.NUMBER,
allowNull: false,
},
scanned_at_utc: {
type: DataTypes.DATE,
defaultValue: DataTypes.NOW,
allowNull: false,
},
created_at_utc: {
type: DataTypes.DATE,
defaultValue: DataTypes.NOW,

4
server/src/services/locationService.ts
View File

@ -9,6 +9,7 @@ interface CreateLocationParams {
wifi?: Coordinates;
gnss?: Coordinates;
ttn_gw?: Coordinates;
gnss_timestamp?: Date;
}
interface CreateLocationTriangulationParams {
@ -19,6 +20,7 @@ interface CreateLocationTriangulationParams {
}[];
ttn_gw: LocationSignal[];
gnss?: Coordinates;
gnss_timestamp?: Date;
}
interface LocationSignal extends Coordinates {
@ -75,6 +77,7 @@ export class LocationService {
ttn_gw_longitude: data.ttn_gw?.longitude,
gnss_latitude: data.gnss?.latitude,
gnss_longitude: data.gnss?.longitude,
gnss_location_at_utc: data.gnss_timestamp,
});
}
@ -91,6 +94,7 @@ export class LocationService {
wifi: wifi_location,
ttn_gw: gateway_location,
gnss: data.gnss,
gnss_timestamp: data.gnss_timestamp,
});
}

2
server/src/services/wifiScanService.ts
View File

@ -5,12 +5,14 @@ interface CreateWifiScanParams {
lp_ttn_end_device_uplinks_id: string;
mac: string;
rssi: number;
scanned_at_utc?: Date;
}
interface UpdateWifiScanParams {
wifi_scan_id: string;
mac?: string;
rssi?: number;
scanned_at_utc?: Date;
}
@injectable()