317 lines
15 KiB
Markdown
317 lines
15 KiB
Markdown
# Aggregated Fields Calculation Reference
|
||
|
||
This document explains how all aggregated metric fields on the `Application` (`applications`) and `AppFile` (`appfiles`) collections are calculated, and which code paths produce them.
|
||
|
||
---
|
||
|
||
## Collections and Fields
|
||
|
||
### Application (`applications`)
|
||
|
||
| Field | Unit | Description |
|
||
|---|---|---|
|
||
| `totalSprayed` | Hectares | Total area covered with spray ON |
|
||
| `totalSprLength` | Meters | Total path distance during spray-ON periods |
|
||
| `totalFlightLength` | Meters | Total flight path distance (spray ON + turns) |
|
||
| `totalSprayTime` | Seconds | Total time with spray ON |
|
||
| `totalTurnTime` | Seconds | Total turning time between spray lines |
|
||
| `totalFlightTime` | Seconds | Total flight time (all GPS intervals) |
|
||
| `totalSprayMat` | L or Kg | Total material dispensed |
|
||
| `totalSprayMatUnit` | Code | Rate unit: `3` = L/ha, `4` = Kg/ha |
|
||
| `avgSpraySpeed` | m/s | Average ground speed during spray-ON periods |
|
||
| `avgXtError` | m | Average absolute cross-track error across spray-on records (stat 1 & 3), valid segments only; `null` when the device firmware does not record xTrack (all binary values are 0) |
|
||
| `avgHdop` | — | Average HDOP across spray-ON records; lower = better (< 1 excellent, 1–2 good, > 5 poor) |
|
||
| `flowAccuracyPct` | % | Flow control accuracy: `(totalSprayMat / totalSprayed / appRate) × 100`. `null` when any source field is zero/absent |
|
||
| `appRate` | L/ha or Kg/ha | Average application rate recorded from data |
|
||
| `startDateTime` | String `YYYYMMDDTHHmmss` | Timestamp of the first GPS record |
|
||
| `endDateTime` | String `YYYYMMDDTHHmmss` | Timestamp of the last GPS record |
|
||
|
||
### AppFile (`appfiles`)
|
||
|
||
Mirrors the same set of aggregated fields as `Application`, computed per individual data file within the upload archive. Fields: `totalSprayed`, `totalSprLength`, `totalFlightLength`, `totalSprayTime`, `totalTurnTime`, `totalFlightTime`, `totalSprayMat`, `totalSprayMatUnit`.
|
||
|
||
### ApplicationDetail (`application_details`)
|
||
|
||
Stores the raw GPS + sensor records parsed from each data file. These are the source rows from which all aggregated fields above are derived. Key fields used in aggregation: `gpsTime`, `utmX`, `utmY`, `swath`, `sprayStat`, `llnum`, `grSpeed`, `lhaReq`, `lhaApp`, `lminApp`, `xTrack`, `sprayHeight`, `radarAlt`.
|
||
|
||
### Job (`jobs`)
|
||
|
||
| Field | Unit | Description |
|
||
|---|---|---|
|
||
| `ttSprArea` | Hectares | Planned spray area minus exclusion zones (from GeoJSON polygons, not from flight data) |
|
||
|
||
---
|
||
|
||
## Code Paths
|
||
|
||
There are two independent processing pipelines that calculate these fields:
|
||
|
||
```
|
||
Upload (AgNav binary / Shape)
|
||
└─► job_worker.js ──► importData()
|
||
└─► importDataFiles() (per file)
|
||
├─► readNTFile() (AgNav binary .nt files)
|
||
└─► readShapeDataFile() (ESRI Shape .dbf files)
|
||
|
||
Partner sync (SatLoc partner logs)
|
||
└─► partner_sync_worker.js
|
||
└─► satloc_application_processor.js ──► processJobGroup()
|
||
```
|
||
|
||
---
|
||
|
||
## Pipeline 1 — AgNav Binary / Shape Files (`job_worker.js`)
|
||
|
||
### Entry point: `importData()` (`workers/job_worker.js` line 943)
|
||
|
||
Orchestrates the full import for one uploaded archive. Steps:
|
||
|
||
1. Scans the unzipped folder for data files matching known patterns.
|
||
2. Classifies files: `FILE.DATA_AGNAV` (`.nt` binary), `FILE.DATA_SHAPE` (`.dbf` shape), or legacy `FILE.DATA_SALOG` (`.asc` ASCII — no longer processed).
|
||
3. Sorts files by AGN timestamp prefix to process them in chronological order.
|
||
4. Calls `importDataFiles()` for each file/pair and accumulates the per-file sub-totals.
|
||
5. After all files are processed, computes the final `appData` object:
|
||
```javascript
|
||
appRate = mean(avgRates[]) // average of per-file average rates
|
||
totalSprayed = sum(data.totalSprayed) // m² – converted to ha at the Application level
|
||
totalSprLength = sum(data.totalSprLength)
|
||
totalFlightLength = sum(data.totalFlightLength)
|
||
totalSprayTime = sum(data.sprayTime)
|
||
totalTurnTime = sum(data.turnTime)
|
||
totalFlightTime = sum(data.totalTime)
|
||
totalSprayMat = sum(data.totalSprayMat)
|
||
avgSpraySpeed = weightedMean(avgSpraySpeed * spraySpeedCount)
|
||
avgHdop = weightedMean(hdopSum across files) / totalHdopCount
|
||
```
|
||
6. Writes the computed totals to the `Application` document (with `m² → ha` unit conversion for `totalSprayed`).
|
||
7. After all Application fields are set, `work()` computes `flowAccuracyPct = (totalSprayMat / totalSprayed / appRate) × 100` when all three are positive, and writes it to Application.
|
||
|
||
### Per-file processing: `importDataFiles()` (line 1108)
|
||
|
||
For each data file (or spray-on/spray-off pair):
|
||
|
||
1. Reads the companion `q*` metadata file to obtain configured application rate, rate unit, and flow controller type.
|
||
2. Creates an `AppFile` document.
|
||
3. Calls `readNTFile()` or `readShapeDataFile()` to get per-record data and per-file totals.
|
||
4. Inserts all `ApplicationDetail` records into MongoDB in 1,000-record batches.
|
||
5. Iterates the sorted record array to compute **time-based and point-metric fields** (`totalFlightTime`, `totalSprayTime`, `totalTurnTime`, `avgSpraySpeed`, `avgHdop`, `avgXtError`) — this loop runs over the stored `ApplicationDetail` records after all files in the pair are merged and sorted.
|
||
6. Saves the per-file totals to the `AppFile` document.
|
||
|
||
#### Time calculations (loop in `importDataFiles`, line ~1310)
|
||
|
||
```
|
||
totalFlightTime += timeDif where 0 < timeDif ≤ 120 s (between every consecutive GPS record)
|
||
|
||
totalSprayTime += timeDif where 0 < timeDif ≤ 120 s (only when sprayStat > 0)
|
||
|
||
turnTime: counted from spray-OFF on one line number to spray-ON on the next line number
|
||
turnTime += timeDif where 5 ≤ timeDif ≤ 120 s
|
||
|
||
avgSpraySpeed = sum(grSpeed) / count (all spray-ON records except sprayStat === 3)
|
||
|
||
avgHdop = sum(stdHdop) / count (all spray-ON records where stdHdop > 0)
|
||
|
||
avgXtError = sum(|xTrack|) / count (spray-ON records where sprayStat ∈ {1, 3} and xTrack ≠ 0)
|
||
null when the device firmware does not record xTrack (all binary values are 0)
|
||
|
||
AppDetail.xTrack unit: metres for all file types.
|
||
- AgNav binary (.nt) — decoded from raw cm integer at parse time (÷ 100 in _readAgnBinary / _readAmsRpm DRY)
|
||
- SatLoc ASCII (.asc/.log) — X-Track field is natively in metres; no conversion applied
|
||
- AgNav Shape (.shp) — XTRACK field is natively in metres; no conversion applied
|
||
Application.avgXtError is therefore in metres for all application types.
|
||
No further conversion is applied in the dashboard API or migration.
|
||
```
|
||
|
||
The 120-second cap on time differences rejects GPS dropouts or large gaps between segments.
|
||
Turn time is line-number-aware: only the gap between the end of one spray line and the start of the **next** line number qualifies.
|
||
|
||
### AgNav binary reader: `readNTFile()` (line 1423)
|
||
|
||
Reads the raw AgNav binary packet stream (fixed-size `FILE.AGN_PACK_SIZE` packets).
|
||
|
||
**Spray area and material per binary record:**
|
||
|
||
```
|
||
sprayStat 3 → "start of line" marker — updates prevUTM_X/Y, prevSwath, prevLine but does NOT accumulate area
|
||
|
||
sprayStat 1 or 2 (spray ON):
|
||
if prevStat > 0 AND prevLine === record.llnum (same spray line):
|
||
sprayedSeg = hypot(utmX - prevUTM_X, utmY - prevUTM_Y) × prevSwath [m²]
|
||
totalSprays += sprayedSeg
|
||
totalSprayMat += (sprayedSeg × SM2HA) × appliedRate
|
||
|
||
appliedRate priority:
|
||
1. Q-file configured rate (converted to metric L/ha or Kg/ha)
|
||
2. Fallback to record.lminApp → converted via appRateFromFlowRate()
|
||
3. Fallback to record.lhaReq
|
||
|
||
appRate = mean(lhaReq across all spray-ON records)
|
||
```
|
||
|
||
Area accumulates only within a single spray line (`prevLine === record.llnum`), preventing cross-line area double-counting.
|
||
|
||
### Shape file reader: `readShapeDataFile()` (line 1598)
|
||
|
||
Reads spray-on DBF attributes. Area and material logic is identical to `readNTFile()` except:
|
||
- Spray-off file contributes only timing records (no area).
|
||
- There is no "start-of-line" `sprayStat 3` marker — the same-line guard uses `prevLine === record.llnum`.
|
||
|
||
### Distance computation — inline streaming
|
||
|
||
`totalSprLength` and `totalFlightLength` are computed **incrementally during the file-read loop** in `readNTFile()`, `readShapeDataFile()`, and `readSatLogAsc()`. No separate rescan of the record array is needed.
|
||
|
||
For multi-file shape uploads (spray-on + spray-off pair merged into one `importInfo`), a cross-file boundary segment is also computed once when the two record sets are joined.
|
||
|
||
Validity gates applied to **every** consecutive pair:
|
||
|
||
| Gate | Threshold | Reason |
|
||
|---|---|---|
|
||
| Time gap | `0 < dt ≤ 120 s` | Skip GPS dropouts, instrument pauses, file boundaries |
|
||
| Distance | `dist ≤ 1000 m` | Reject GPS position outliers |
|
||
| Spray status (sprLength only) | `prev.sprayStat > 0 \|\| curr.sprayStat > 0` | Skip pure turn/off segments |
|
||
|
||
Midnight rollover (`gpsTime` is seconds-of-day) is handled in every loop: if `dt < 0` and `|dt| ≥ 80000`, then `dt = 86400 − prevTime + currTime`.
|
||
|
||
```javascript
|
||
// Pseudocode (applied inside readNTFile / readShapeDataFile)
|
||
for each record after timeOffset adjustment:
|
||
dt = record.gpsTime - prevRecTime // handle midnight rollover
|
||
if (dt > 0 && dt <= 120):
|
||
segDist = hypot(record.utmX - prev.utmX, record.utmY - prev.utmY)
|
||
if (segDist <= 1000):
|
||
totalFlightLen += segDist
|
||
if (prevSprStat > 0 || record.sprayStat > 0):
|
||
totalSprLen += segDist
|
||
```
|
||
|
||
`_computeFlightLength()` and `_computeSprLength()` remain as **fallback helpers** (used only when inline totals are not available, e.g. from the migration script). They apply the same two-gate logic:
|
||
|
||
```javascript
|
||
// _computeFlightLength() fallback — all GPS pairs, dt ≤ 120 s AND dist ≤ 1000 m
|
||
// _computeSprLength() fallback — spray-ON pairs, dt ≤ 120 s AND dist ≤ 1000 m
|
||
```
|
||
|
||
---
|
||
|
||
## Pipeline 2 — SatLoc Partner Logs (`satloc_application_processor.js`)
|
||
|
||
### Entry point: `processJobGroup()` (`helpers/satloc_application_processor.js` line ~157)
|
||
|
||
Called by `partner_sync_worker.js` after `satloc_log_parser.js` has parsed the binary SatLoc log into `ApplicationDetail`-compatible records.
|
||
|
||
**UTM conversion** (per record, using `@mickeyjohn/geodesy/utm.js`):
|
||
```javascript
|
||
{ easting: utmX, northing: utmY } = LatLon(lat, lon).toUtm(zone, hemisphere)
|
||
```
|
||
|
||
**Flight time** (same 120 s cap as job_worker):
|
||
```javascript
|
||
if (0 < timeDif ≤ MAX_TIME_DIFF) // MAX_TIME_DIFF = 120 s
|
||
totalFlightTime += timeDif
|
||
```
|
||
|
||
**Spray time:**
|
||
```javascript
|
||
if (prevSprayStat > 0 AND curSprayStat > 0 AND 0 < timeDif ≤ 120)
|
||
totalSprayTime += timeDif
|
||
```
|
||
|
||
**Spray area and material** (triggered while `curSprayStat > 0` and `prevSprayStat > 0`):
|
||
```
|
||
distance = hypot(utmX - prevUTM_X, utmY - prevUTM_Y) [m]
|
||
swathArea = distance × record.swath [m²]
|
||
|
||
totalSprayed += swathArea
|
||
totalSprayLength += distance
|
||
|
||
appRate = record.lhaApp || record.lhaReq
|
||
totalSprayMat += (swathArea × appRate) / 10000 [L or Kg]
|
||
```
|
||
|
||
Note: `prevUTM_X/Y` is updated **only** while spray is ON, so the distance for area never bridges a spray-OFF gap.
|
||
|
||
**Unit conversion after the loop:**
|
||
```javascript
|
||
totalSprayed = totalSprayed × 1E-4 // m² → hectares
|
||
```
|
||
|
||
**Spray segments** are tracked in addition for map rendering: each continuous spray-ON run is stored as a `{ startTime, endTime, startLat/Lon, endLat/Lon, distance, area, points[] }` segment object.
|
||
|
||
**Material unit** is determined from SatLoc flow controller type:
|
||
```javascript
|
||
sprayMatUnit = (fcType === FCTypes.LIQUID) ? RateUnits.LIT_PER_HA : RateUnits.KG_PER_HA
|
||
```
|
||
|
||
**Database writes:**
|
||
```javascript
|
||
ApplicationFile.updateOne({ _id: appFile._id }, { $set: {
|
||
totalSprLength, totalSprayTime, totalFlightTime,
|
||
totalSprayed, totalSprayMat, totalSprayMatUnit
|
||
}});
|
||
|
||
Application.updateOne({ _id: application._id }, { $set: {
|
||
status: AppStatus.DONE,
|
||
totalSprayTime, totalFlightTime, totalSprayed,
|
||
totalSprayMat, totalSprayMatUnit, totalSprLength,
|
||
appRate: 0, // Not yet calculated for SatLoc path
|
||
startDateTime, endDateTime
|
||
}});
|
||
```
|
||
|
||
---
|
||
|
||
## Job Planned Spray Area (`job_worker.js` + `job_util.js`)
|
||
|
||
`Job.ttSprArea` is **not** derived from flight data — it is calculated from the GeoJSON spray-area polygons drawn by the operator when the job is created or updated.
|
||
|
||
**`jobUtil.calcTTSprayAreas(sprayAreas, excludedAreas)`** (`helpers/job_util.js` line 117):
|
||
|
||
```
|
||
for each sprayArea polygon:
|
||
realArea = turf.area(sprayArea) // m²
|
||
|
||
for each exclusionZone that intersects this polygon (R-tree spatial index):
|
||
realArea -= turf.area( turf.intersect(sprayArea, exclusionZone) )
|
||
|
||
ttSprArea += realArea
|
||
|
||
job.ttSprArea = calcTTSprayAreas(...) × SM2HA // m² → ha
|
||
```
|
||
|
||
This is recalculated every time a job's spray areas or exclusion zones change.
|
||
|
||
---
|
||
|
||
## Unit Conversion Constants
|
||
|
||
| Constant | Value | Purpose |
|
||
|---|---|---|
|
||
| `1E-4` | `0.0001` | m² → hectares |
|
||
| `CVCST.SM2HA` | `1E-4` | Same as above (used for material calculation) |
|
||
| `CVCST.SM2ACR` | `0.000247105` | m² → acres (subscription limit checks) |
|
||
| Max time gap | `120 s` | Outlier rejection for all time accumulators |
|
||
| Max GPS segment | `1000 m` | Outlier rejection in all distance loops (inline + `_computeFlightLength` / `_computeSprLength` fallbacks) |
|
||
| Max time gap (distance) | `120 s` | Skip GPS dropouts in all distance loops — same cap used for flight/spray time accumulators |
|
||
|
||
---
|
||
|
||
## Summary: Which Code Sets Which Field
|
||
|
||
| Field | AgNav binary | Shape | SatLoc partner | Notes |
|
||
|---|---|---|---|---|
|
||
| `totalSprayed` | `readNTFile` → `importData` | `readShapeDataFile` → `importData` | `processJobGroup` | m² accumulated, converted × 1E-4 to ha before DB write |
|
||
| `totalSprLength` | inline in `readNTFile` (fallback: `_computeSprLength`) | inline in `readShapeDataFile` (fallback: `_computeSprLength`) | loop in `processJobGroup` | spray-ON segments, dt ≤ 120 s, dist ≤ 1000 m |
|
||
| `totalFlightLength` | inline in `readNTFile` (fallback: `_computeFlightLength`) | inline in `readShapeDataFile` (fallback: `_computeFlightLength`) | not computed | all GPS segments, dt ≤ 120 s, dist ≤ 1000 m |
|
||
| `totalSprayTime` | loop in `importDataFiles` | loop in `importDataFiles` | loop in `processJobGroup` | 120 s cap |
|
||
| `totalTurnTime` | loop in `importDataFiles` | loop in `importDataFiles` | not computed | line-number-aware |
|
||
| `totalFlightTime` | loop in `importDataFiles` | loop in `importDataFiles` | loop in `processJobGroup` | 120 s cap |
|
||
| `totalSprayMat` | `readNTFile` | `readShapeDataFile` | `processJobGroup` | L or Kg depending on unit |
|
||
| `totalSprayMatUnit` | from Q-file or record | from Q-file or record | from SatLoc `fcType` | 3=L/ha, 4=Kg/ha |
|
||
| `avgSpraySpeed` | loop in `importDataFiles` | loop in `importDataFiles` | `processJobGroup` | spray-ON records |
|
||
| `avgXtError` | loop in `importDataFiles` (fallback: `migrate_applications.js`) | loop in `importDataFiles` (fallback: `migrate_applications.js`) | `migrate_applications.js` | spray-ON (stat 1 & 3), xTrack ≠ 0 |
|
||
| `avgHdop` | loop in `importDataFiles` | loop in `importDataFiles` | not computed | spray-ON records, stdHdop > 0; backfilled by migration |
|
||
| `flowAccuracyPct` | `work()` post-field-set | `work()` post-field-set | not computed | (totalSprayMat/totalSprayed/appRate)×100; backfilled by migration |
|
||
| `avgSpraySpeed` | loop in `importDataFiles` | loop in `importDataFiles` | not computed | mean `grSpeed` while spray ON |
|
||
| `appRate` | mean `lhaReq` in `readNTFile` | mean `lhaReq` in `readShapeDataFile` | not computed (set to 0) | |
|
||
| `ttSprArea` (Job) | `calcTTSprayAreas` | `calcTTSprayAreas` | `calcTTSprayAreas` | from planned GeoJSON polygons, not flight data |
|