shawn
064fc43fe3
All GPS from MSFS account addresses and Cantaloupe seed data removed. Only 5 authentic app check-in GPS entries remain. Reverts the bulk restore from earlier commit (gps restore was address-based).
76 lines
2.6 KiB
Python
76 lines
2.6 KiB
Python
#!/usr/bin/env python3
|
|
"""Remove ALL GPS data that was geocoded from addresses.
|
|
|
|
Keeps ONLY: the 5 original app check-in entries from gps-backup.json that
|
|
still exist in the current DB. Everything else was derived from an
|
|
address geocoding source (MSFS accounts, Cantaloupe seed data)."""
|
|
|
|
import json
|
|
import sqlite3
|
|
|
|
DB_PATH = "/home/oplabs/projects/canteen-asset-tracker/assets.db"
|
|
GPS_BAK_PATH = "/home/oplabs/projects/canteen-asset-tracker/gps-backup.json"
|
|
|
|
def main():
|
|
# Load original app check-ins
|
|
with open(GPS_BAK_PATH) as f:
|
|
gps_bak = json.load(f)
|
|
checkin_coords = {}
|
|
for e in gps_bak:
|
|
checkin_coords[e['machine_id']] = (e['lat'], e['lng'])
|
|
print(f"Original app check-in entries: {len(checkin_coords)}")
|
|
|
|
cur = sqlite3.connect(DB_PATH)
|
|
|
|
total = cur.execute("SELECT COUNT(*) FROM assets").fetchone()[0]
|
|
before = cur.execute(
|
|
"SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL AND latitude != 0"
|
|
).fetchone()[0]
|
|
print(f"Before: {before} / {total} with GPS")
|
|
|
|
removed = 0
|
|
kept_checkin = 0
|
|
kept_other = 0
|
|
|
|
for r in cur.execute(
|
|
"SELECT rowid, machine_id, latitude, longitude FROM assets "
|
|
"WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude != 0 AND longitude != 0"
|
|
).fetchall():
|
|
rowid, mid, lat, lon = r
|
|
|
|
# KEEP: only app check-in entries where coordinates match exactly
|
|
if mid in checkin_coords:
|
|
expected = checkin_coords[mid]
|
|
if abs(float(lat) - expected[0]) < 0.0001 and abs(float(lon) - expected[1]) < 0.0001:
|
|
kept_checkin += 1
|
|
continue
|
|
|
|
# Everything else is address-geocoded — remove
|
|
cur.execute("UPDATE assets SET latitude = NULL, longitude = NULL WHERE rowid = ?", (rowid,))
|
|
removed += 1
|
|
|
|
cur.commit()
|
|
|
|
after = cur.execute(
|
|
"SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL AND latitude != 0"
|
|
).fetchone()[0]
|
|
|
|
print(f"\nResults:")
|
|
print(f" Kept (app check-ins): {kept_checkin}")
|
|
print(f" Removed (address-geocoded): {removed}")
|
|
print(f" After: {after} / {total} with GPS")
|
|
print(f" Without GPS: {total - after}")
|
|
|
|
# Show what's left
|
|
print(f"\n=== Remaining GPS entries ===")
|
|
for r in cur.execute(
|
|
"SELECT machine_id, make, model, category, latitude, longitude FROM assets "
|
|
"WHERE latitude IS NOT NULL AND latitude != 0"
|
|
).fetchall():
|
|
print(f" {r[0]}: ({r[4]}, {r[5]}) — {r[1]} {r[2]} ({r[3]})")
|
|
|
|
cur.close()
|
|
|
|
if __name__ == "__main__":
|
|
main()
|