search

jbrzusto / find_tags

search raw data streams for patterns from registered tags
GNU General Public License v2.0
2 stars 0 forks source link

why are there (0, 0, 0) records in GPS table for receivers that were not recording GPS fixes #39

Closed jbrzusto closed 6 years ago

jbrzusto commented 7 years ago

e.g. Lotek-162 (= device 477)

This is another problem with the behaviour reported in jbrzusto/motusServer#241

jbrzusto commented 6 years ago

Diagnosis

Lotek receiver GPS records use sqlite3_column_double() to grab lat/lon from receiver DB's DTAtags table, but this returns0.0 when the field is NULL. So check for NULL and return the 999.0 sentinel value used

Only 3 SG receivers have GPS records with lat = 0.0:

Stats

(currently, lat==0.0 indicates a bogus GPS record; this urgently needs to change, as motus expands!)

-- number of distinct receivers with at least one lat=0.0 record
MariaDB [motus]> select count(distinct motusDeviceID) from batches as t1 join gps as t2 on t1.batchID=t2.batchID and t2.lat=0.0;
+-------------------------------+
| count(distinct motusDeviceID) |
+-------------------------------+
|                           103 |
+-------------------------------+
-- which deviceIDs, as a string usable in queries:
MariaDB [motus]> select group_concat(distinct motusDeviceID) as ids from batches as t1 join gps as t2 on t1.batchID=t2.batchID and t2.lat=0.0;

913,741,513,486,515,479,477,478,516,514,519,520,517,518,686,521,522,872,523,871,873,689,690,754,524,529,528,481,698,526,884,874,791,847,756,742,753,757,759,758,743,744,760,745,755,746,750,751,761,752,487,748,489,490,492,765,766,494,491,495,493,767,488,497,496,498,484,499,503,500,504,501,502,505,507,506,483,512,510,511,770,534,533,771,535,536,537,540,538,541,509,542,539,915,916,917,918,772,899,902,903,907,908

From the meta DB, distribution of receiver types among these devices:

select receiverType, count(distinct serno) from recvDeps where deviceID in (913,741,513,486,515,479,477,478,516,514,519,520,517,518,686,521,522,872,523,871,873,689,690,754,524,529,528,481,698,526,884,874,791,847,756,742,753,757,759,758,743,744,760,745,755,746,750,751,761,752,487,748,489,490,492,765,766,494,491,495,493,767,488,497,496,498,484,499,503,500,504,501,502,505,507,506,483,512,510,511,770,534,533,771,535,536,537,540,538,541,509,542,539,915,916,917,918,772,899,902,903,907,908) group by receiverType;
receiverType|count(distinct serno)
LOTEK|89
SENSORGNOME|3
sqlite> select receiverType, deviceID, serno from recvDeps where deviceID in (913,741,513,486,515,479,477,478,516,514,519,520,517,518,686,521,522,872,523,871,873,689,690,754,524,529,528,481,698,526,884,874,791,847,756,742,753,757,759,758,743,744,760,745,755,746,750,751,761,752,487,748,489,490,492,765,766,494,491,495,493,767,488,497,496,498,484,499,503,500,504,501,502,505,507,506,483,512,510,511,770,534,533,771,535,536,537,540,538,541,509,542,539,915,916,917,918,772,899,902,903,907,908) and receiverType="SENSORGNOME" group by receiverType, serno;
receiverType|deviceID|serno
SENSORGNOME|916|SG-E290RPI34AAE  -- device ID 916 == device ID for Lotek-D000440
SENSORGNOME|917|SG-1914BBBK0929  -- device ID 917 == device ID for Lotek-D000441
SENSORGNOME|918|SG-F37DRPI38476  -- device ID 918 == device ID for Lotek-D000442

This is a separate bug: we had incorrect deviceIDs for some Lotek receivers when we ran their data. So no SG gps records have 0.0 lat (i.e. are bogus).