store location created from all location sources

README.md

@@ -10,7 +10,7 @@ TODO
 ### Database
 **Change name of database and credentials as you like!**
 
-- Create new database: `CREATE DATABASE locationhub;`
+- Create new database: `CREATE DATABASE dev_locationhub;`
 - Create new user for database: `GRANT ALL PRIVILEGES ON dev_locationhub.* TO 'dbuser'@'localhost' IDENTIFIED BY '1234';`
 - Import tables: `/usr/bin/mariadb -u dbuser -p1234 dev_locationhub < server/sql/tables.sql`
 

server/.env.template

@@ -7,6 +7,6 @@ DB_PORT=""
 WIGLE_TOKEN=""
 WIGLE_BASE_URL="https://api.wigle.net"
 WIGLE_NETWORK_SEARCH="/api/v2/network/search"
-GET_LOCATION_WIFI_MAX_AGE=1209600000 # 14 Tage in Millisekunden (14 * 24 * 60 * 60 * 1000)
+GET_LOCATION_WIFI_MAX_AGE=1209600000 # 14 days in milliseconds (14 * 24 * 60 * 60 * 1000)
 GET_LOCATION_WIFI_MAX=10000          
 GET_LOCATION_WIFI_PRIMITIVE=true  

server/package.json

@@ -13,11 +13,11 @@
   "license": "ISC",
   "devDependencies": {
     "@types/express": "^5.0.0",
+    "@types/memoizee": "^0.4.11",
     "@types/node": "^22.10.2",
     "nodemon": "^3.1.9",
     "ts-node": "^10.9.2",
-    "typescript": "^5.7.2",
-    "@types/memoizee": "^0.4.11"
+    "typescript": "^5.7.2"
   },
   "dependencies": {
     "cors": "^2.8.5",

server/sql/tables.sql

@@ -46,6 +46,8 @@ CREATE TABLE IF NOT EXISTS location (
     wifi_longitude DOUBLE,
     gnss_latitude DOUBLE,
     gnss_longitude DOUBLE,
+    ttn_gw_latitude DOUBLE,
+    ttn_gw_longitude DOUBLE,
     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)

server/src/controller/ttnController.ts

@@ -28,7 +28,8 @@ router.post(
   async (req: Request, res: Response) => {
     try {
       const message = req.body as TtnMessage;
-      const { lp_ttn_end_device_uplinks_id } =
+      // Create uplink record
+      const { lp_ttn_end_device_uplinks_id, latitude, longitude } =
         await lpTtnEndDeviceUplinksService.createUplink({
           device_id: message.end_device_ids.device_id,
           application_ids:
@@ -78,6 +79,7 @@ router.post(
           latitude: g.latitude,
           longitude: g.longitude,
         })),
+        gnssLocation: { latitude, longitude }
       };
 
       domainEventEmitter.emit(TtnMessageReceivedEventName, event);

server/src/event/ttnMessageReceivedEvent.ts

@@ -11,4 +11,8 @@ export type TtnMessageReceivedEvent = {
     longitude: number;
     altitude: number;
   }[];
+  gnssLocation: {
+    latitude: number | undefined;
+    longitude: number | undefined;
+  }
 };

server/src/eventHandler/ttnMessageReceivedEventHandler.ts

@@ -3,11 +3,115 @@ import {
   TtnMessageReceivedEvent,
   TtnMessageReceivedEventName,
 } from "../event/ttnMessageReceivedEvent";
+import { container } from "tsyringe";
+import { LocationService } from "../services/locationService";
+import { WifiScanService } from "../services/wifiScanService";
+import { getLocationForWifiMemoized } from "../proxy/wigle";
+
+const locationService = container.resolve(LocationService);
+const wifiScanService = container.resolve(WifiScanService);
 
 domainEventEmitter.on(
   TtnMessageReceivedEventName,
   async (event: TtnMessageReceivedEvent) => {
     console.log(event);
-    // TODO Hendrik 🚀
+
+    var wifi_based_latitude: number | undefined = undefined;
+    var wifi_based_longitude: number | undefined = undefined;
+    var gnss_based_latitude: number | undefined = undefined;
+    var gnss_based_longitude: number | undefined = undefined;
+    var ttn_gw_based_latitude: number | undefined = undefined;
+    var ttn_gw_based_longitude: number | undefined = undefined;
+
+    // Get location based on TTN Gateways
+    if (!event.ttnGateways || event.ttnGateways.length === 0) {
+      console.log("No TTN Gateway location received!")
+    } else {
+      let totalWeight = 0;
+      let weightedLatitude = 0;
+      let weightedLongitude = 0;
+
+      event.ttnGateways.forEach(gw => {
+        const weight = 1 / Math.abs(gw.rssi); // Higher RSSI (closer to 0) gives more weight
+        totalWeight += weight;
+        weightedLatitude += gw.latitude * weight;
+        weightedLongitude += gw.longitude * weight;
+      });
+
+      // Calculate the weighted average to get the virtual location
+      const virtualLocation = {
+        latitude: weightedLatitude / totalWeight,
+        longitude: weightedLongitude / totalWeight
+      };
+
+      console.log("Tracker location based on TTN Gateway location:", virtualLocation);
+      ttn_gw_based_latitude = virtualLocation.latitude;
+      ttn_gw_based_longitude = virtualLocation.longitude;
+    }
+
+    // Get location based on WiFi Scans
+    if (!event.wifis || event.wifis.length === 0) {
+      console.log("No WiFi scans received!")
+    } else {
+      let totalWeight = 0;
+      let weightedLatitude = 0;
+      let weightedLongitude = 0;
+
+      // Process Wi-Fi data to compute weighted location
+      await Promise.all(
+        event.wifis.map(async (wifi) => {
+          const apiResponse = await getLocationForWifiMemoized(wifi.mac);
+          if ((apiResponse != undefined) && (apiResponse?.totalResults > 0)) {
+            // Create new WiFi Scan entry if wigle.net reported location
+            const newWifiScan = wifiScanService.createWifiScan({
+              lp_ttn_end_device_uplinks_id: event.lp_ttn_end_device_uplinks_id,
+              mac: wifi.mac,
+              rssi: wifi.rssi,
+              latitude: apiResponse?.results[0]?.trilat,
+              longitude: apiResponse?.results[0]?.trilong,
+            })
+
+            // Calculate weight based on RSSI (higher signal strength gives more weight)
+            const weight = 1 / Math.abs((await newWifiScan).rssi);
+            totalWeight += weight;
+
+            // Accumulate weighted latitude and longitude
+            weightedLatitude += (await newWifiScan).latitude * weight;
+            weightedLongitude += (await newWifiScan).longitude * weight;
+          }
+        })
+      );
+
+      // Calculate the weighted average to get the virtual location
+      const virtualLocation = {
+        latitude: weightedLatitude / totalWeight,
+        longitude: weightedLongitude / totalWeight
+      };
+
+      console.log("Tracker location based on WiFi Scan location:", virtualLocation);
+      wifi_based_latitude = virtualLocation.latitude;
+      wifi_based_longitude = virtualLocation.longitude;
+    }
+
+    // Get location based on GNSS
+    if ((event.gnssLocation.latitude) && (event.gnssLocation.longitude)) {
+      gnss_based_latitude = event.gnssLocation.latitude;
+      gnss_based_longitude = event.gnssLocation.longitude;
+    } else {
+      console.log("No GNSS location received!")
+    }
+
+    const newLocation = await locationService.createLocation({
+      lp_ttn_end_device_uplinks_id: event.lp_ttn_end_device_uplinks_id,
+      ttn_gw_latitude: ttn_gw_based_latitude,
+      ttn_gw_longitude: ttn_gw_based_longitude,
+      gnss_latitude: gnss_based_latitude,
+      gnss_longitude: gnss_based_longitude,
+      wifi_latitude: wifi_based_latitude,
+      wifi_longitude: wifi_based_longitude,
+    });
+
+    console.log(newLocation)
   }
 );
+

server/src/index.ts

@@ -25,5 +25,5 @@ app.use("/api/locations", locationRoutes);
 app.use("/api/ttn", ttnRoutes);
 
 app.listen(PORT, () => {
-  console.log(`🚀 Server läuft auf http://localhost:${PORT}`);
+  console.log(`🚀 Server runs here: http://localhost:${PORT}`);
 });

server/src/models/location.ts

@@ -8,6 +8,8 @@ export class Location extends Model {
   public wifi_longitude!: number;
   public gnss_latitude!: number;
   public gnss_longitude!: number;
+  public ttn_gw_latitude!: number;
+  public ttn_gw_longitude!: number;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }
@@ -40,6 +42,14 @@ Location.init(
       type: DataTypes.NUMBER,
       allowNull: true,
     },
+    ttn_gw_latitude: {
+      type: DataTypes.NUMBER,
+      allowNull: true,
+    },
+    ttn_gw__longitude: {
+      type: DataTypes.NUMBER,
+      allowNull: true,
+    },
     created_at_utc: {
       type: DataTypes.DATE,
       defaultValue: DataTypes.NOW,