Duplicate US/USA countries

[holman]

More of a minor thing, but thought I’d report it just in case. It looks like maybe in the last month or three I’ve started seeing all my Location data attached to the country “United States”. Previously, it was attached to the country “United States of America”. This leads to the number of countries I’ve visited as 2 (instead of the proper 1).

Again, not a dealbreaker, and I might just update all the previous ones from the database, but wanted to point it out in case this was an unintended change/data source change and it might revert back at some point, etc.

Cheers, happy new year!

[adriankumpf]

Nothing has been changed in the related source code for a while. Since the data comes directly from OpenStreetMap, it is likely that the inconsistency can be found there. Do you have an example address that you can share? The addresses stored in the TeslaMate database contain the raw data coming from OSM.

[holman]

Looks like OSM might have changed how they returned the country name?

This is the raw from a recent drive:

{
  "lat": "38.6964876",
  "lon": "-119.5485215",
  "name": "Tesla Supercharger",
  "type": "charging_station",
  "osm_id": 6641867798,
  "address": {
    "road": "US 395",
    "state": "Nevada",
    "county": "Douglas County",
    "amenity": "Tesla Supercharger",
    "country": "United States",
    "country_code": "us"
  },
  "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
  "category": "amenity",
  "osm_type": "node",
  "place_id": 71835249,
  "extratags": {
    "capacity": "6",
    "operator": "Tesla Motors",
    "brand:wikidata": "Q17089620",
    "brand:wikipedia": "en:Tesla Supercharger"
  },
  "importance": 0,
  "place_rank": 30,
  "addresstype": "amenity",
  "boundingbox": [
    "38.6964376",
    "38.6965376",
    "-119.5485715",
    "-119.5484715"
  ],
  "namedetails": {
    "name": "Tesla Supercharger",
    "brand": "Tesla Supercharger",
    "name:en": "Tesla Supercharger"
  },
  "display_name": "Tesla Supercharger, US 395, Douglas County, Nevada, United States"
}

This is the raw from an earlier drive:

{
  "lat": "37.76871065",
  "lon": "-122.39221925169028",
  "name": "UCSF Mission Bay",
  "type": "university",
  "osm_id": 224832527,
  "address": {
    "city": "San Francisco",
    "road": "Southern Embarcadero Freeway",
    "state": "California",
    "county": "San Francisco",
    "country": "United States of America",
    "postcode": "94158",
    "university": "UCSF Mission Bay",
    "country_code": "us",
    "neighbourhood": "Mission Bay"
  },
  "licence": "Data © OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
  "category": "amenity",
  "osm_type": "way",
  "place_id": 141012132,
  "extratags": {
    "website": "https://www.ucsf.edu/",
    "internet_access": "wlan"
  },
  "importance": 0,
  "place_rank": 30,
  "addresstype": "amenity",
  "boundingbox": [
    "37.7668468",
    "37.7706091",
    "-122.3956558",
    "-122.3893037"
  ],
  "namedetails": {
    "name": "UCSF Mission Bay",
    "name:en": "UCSF Mission Bay",
    "alt_name": "University of California, San Francisco - Mission Bay campus"
  },
  "display_name": "UCSF Mission Bay, Southern Embarcadero Freeway, Mission Bay, San Francisco, California, 94158, United States of America"
}

[adriankumpf]

Yeah, this looks like inconsistent OSM data. We could of course maintain a list of aliases in TeslaMate, but it would be much cleaner to fix the error directly in OSM.

[3rdStng]

I realize this topic is 3 years old now. How would one go about correcting the data? I am still seeing inconsistencies with some drives showing United States and others showing United States of America. I even have multiple drives on the same day showing both. Is there a way I could mass update the data? Or a way to define an alias saying that United States of America is actually just United States. Not a SQL guy so I'm not sure where to start with either.

[DrMichael]

Well, you can change it in the database for the past.

First backup your database, see https://docs.teslamate.org/docs/maintenance/backup_restore/

Then run this psql in the database container:
docker compose exec database psql -U teslamate -d teslamate -"UPDATE addresses SET country = 'United States' WHERE country = 'United States of America';"

And you're done.

[3rdStng]

The OSM data being reported is not consistent in any fashion. I can take the same drive on two different days and it will report US on one drive and USA on the next. Could we change the dashboard to merge US and USA into one? I managed to do this on the Locations dashboard by changing the query within the "# of Addresses" panel, and then disabling the "total_records" Transformations in the # of Cities, States, and Countries panels. I honestly don't know if this will cause issues within other panels though. But it appears to merge US and USA together properly.

SELECT COUNT(*) AS total_records, COUNT(DISTINCT city) AS city_count, COUNT(DISTINCT state) AS state_count, COUNT(DISTINCT ( CASE WHEN country IN ('United States of America', 'United States') THEN 'United States' ELSE country END )) AS country_count FROM addresses WHERE id IN ( SELECT start_address_id FROM drives WHERE car_id IN ($car_id) AND $__timeFilter(start_date) UNION SELECT end_address_id FROM drives WHERE car_id IN ($car_id) AND $__timeFilter(end_date) UNION SELECT address_id FROM charging_processes WHERE car_id IN ($car_id) AND ($__timeFilter(start_date) OR $__timeFilter(end_date)) );