2

I have a very large pandas DataFrame. A small example:

          BaseDateTime       LAT        LON
0  2018-10-18T00:00:00  36.97696  -89.10680
1  2018-10-18T00:00:00  46.08972 -122.92928
2  2018-10-18T00:00:00  48.10739 -122.77227
3  2018-10-18T00:00:00  28.72571  -89.52151
4  2018-10-18T00:00:00  61.11447 -146.35110

How can I transform the column BaseDateTime (local time) to GMT time (according to the column LON which tells in what Universal Transverse Mercator or UTM time zone the data was measured)?

I've googled for answers. There are a lot of tutorials about time zones, but none of them use longitude + local time.

Leo
  • 772
  • 1
  • 6
  • 15
  • [UTM](https://en.wikipedia.org/wiki/Universal_Transverse_Mercator_coordinate_system) has nothing to do with time zones. – Matt Johnson-Pint May 19 '20 at 03:26
  • @MattJohnson-Pint I'm confused. How does UTM have nothing to do with time zones? My data comes from this page: https://marinecadastre.gov/ais/. If a ship travels for more than 6° longitude, it goes from one UTM zone to another and therefore reports time differently (24h/60zones=24min difference). Is this correct? I'm trying to plot ship trajectories... – Leo May 19 '20 at 14:31
  • I will provide a detailed answer, but need some details. Is all your data from ships at sea? When you say "therefore reports time differently", which field(s) and which files in that data are you evaluating? – Matt Johnson-Pint May 19 '20 at 15:17
  • Yes, my data is all from that site (maritime traffic around USA). When I group the rows of the csv file according to MMSI number (=ID of a ship) and sort them according to BaseDateTime, I get trajectories. Some look normal, and some are horrible (looks like time travel). This is partly because some ships have the same ID, and partly because (I suspect) their time is measured according to the zone they're in, so when travelling from one zone to another, you get +-24 min. – Leo May 19 '20 at 15:25

2 Answers2

3

Time zones are not perfectly split by longitudes. If you've got latitude and longitude available though, you can get the relevant time zone using APIs like the ones listed here:

How to get a time zone from a location using latitude and longitude coordinates?

There's also this Python library that simplifies the process:

https://github.com/pegler/pytzwhere

Randy
  • 14,349
  • 2
  • 36
  • 42
1

Local shipboard time for ships at sea is complicated by three factors:

  • In open international waters, time zone can be computed from longitude as a fixed hour offset from UTC. For example, UTC-9 is in use far west of the Pacific coast. In the TZ database that is referenced as Etc/GMT+9 - the sign is inverted.

  • In territorial waters, the time zone is usually that of the nearest land-based zone. For example, immediately west of the Pacific coast, the zone identifier is still America/Los_Angeles, which is UTC-8 during standard time and UTC-7 during daylight time.

  • The rules/regulations/laws/etc that govern this sort of thing are inconsistent. In all practicality, the captain of the ship can declare whatever time zone they like. This happens a lot on passenger cruise ships, where the local ship time might be switched overnight. Passengers often get confused when their cell phone picks up a signal from elsewhere that switches the time to something other than the shipboard time.

You can read more about this in Wikipedia's article about Nautical Time. There's also a note about ships at sea in the IANA tz database sources.

Ignoring the third point above, you can get the time zone id from the lat/lon coordinates. Randy's answer is generally correct with regard to that. However I'd recommend timezonefinder rather than pytzwhere, as it uses the timezone-boundary-builder data set which includes land-based time zones in territorial waters. (Pytzwhere uses older "tz_world" data, which does not have territorial water boundaries.)

That said, in the data set you are using - you do not need to convert time zones. The data is already in UTC. Here is the chart posted in their FAQ:

chart
(source: marinecadastre.gov)

Field 2, the BaseDateTime is described as the "Full UTC date and time". In other words, they should have a Z at the end. Interpret 2017-02-01T20:05:07 as 2017-02-01T20:05:07Z. (UTC and GMT are essentially the same.) Thus, all the timestamps in the files already have the same basis - UTC. That eliminates the ambiguity of local time at sea.

With regard to UTM, that's not related to time, but rather position. A UTM zone is a square on the surface of the Earth with specific calculated boundaries. In the data set you're working with, each smaller file is divided up by UTM zone (Zone1, Zone2, Zone3, etc.). All the data points within each file will have lat/lon in their respective UTM zones. There also appears (below the main data on the same page) to be links to larger files that have data from all zones combined, so you could use those instead if you were interested in the whole world, and UTM would not need to be considered.

You said:

... I get trajectories. Some look normal, and some are horrible (looks like time travel) ...

This is addressed in the same FAQ:

Q: How do I account for apparent inconsistencies of the AIS timestamp and of an observed vessel’s voyage?

A: The full timestamp is added to the record by the base station, using the time clock of the base station that is reporting in UTC. Be sure to account for your time zone shift to UTC and other offsets such as Daylight Savings Time.

They appear to be speaking with regard to how you are observing the data. Since the timestamp added to the record is done by the base station, and that station is reporting in UTC, then it's only if you need to convert to some local time to observe the data that time zone offsets and DST come into play. If you are only tracking trajectories, then you should keep things in their original UTC basis.

If the data is still wildly incorrect, then their is probably just bad data. It is indeed probable that incorrect GPS pings could result in what looks like wildly incorrect trajectories. You may need to find another way to filter such anomalies out of the data.

With regard to:

... (I suspect) their time is measured according to the zone they're in ...

I didn't see anything that would lead to that conclusion in the data description on the source web site. The data is in UTC, not shipboard time.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thank you kindly for your answer! I was not aware the local shipboard time was so complicated. I guess there are other reasons why some trajectories are so strange. One more thing: are LAT and LON in the datasets spherical latitude and longitude or UTC? If not, how do I transform them to spherical coordinates? – Leo May 19 '20 at 18:21
  • I'm not sure what you mean. Terminology: [UTC = Coordinated Universal Time](https://en.wikipedia.org/wiki/Coordinated_Universal_Time) - a timekeeping standard that we align the world's clocks to. Has nothing to do with coordinates. [UTM = Universal Transverse Mercator](https://en.wikipedia.org/wiki/Universal_Transverse_Mercator_coordinate_system) a system of coordinates that divides the earth into a grid. Has nothing to do with time. UTM coordinates are numbers assigned to each grid. Latitude and Longitude are simply that - there aren't spherical/UTM/UTC variants of lat/lon. – Matt Johnson-Pint May 19 '20 at 19:14
  • 1
    In other words, there is nothing to transform. You already have a UTC-based timestamp of the time of an event, and latitude and longitude coordinates of the position of the event. You don't need anything else to track trajectory and/or velocity. – Matt Johnson-Pint May 19 '20 at 19:16
  • Sorry, typo, I should have written UTM in my last questions. My question (unrelated to time issues) was this: are LAT and LON in the csv files the latitude and longitude (spherical coordinates) or the UTM coordinates? Can I compute haversine distance between points using LAT and LON, or do I need to transform into spherical? – Leo May 19 '20 at 20:26
  • 1
    The are lat/lon. You can use them directly. They are not UTM coordinates. – Matt Johnson-Pint May 19 '20 at 21:27