Navigation

Documentation Data Hack Zurich

Requesting access to the Migros Challenge

You can get your credentials by filling out this form.

Table „gis opex_international_bestellu“

This table contains order data. Below, all relevant data fields are described:

  • bestellnummer: Link to a different data table
  • lieferant_plz / lieferant_ort: ZIP code and locality of supplier
  • lieferant_land: Country of supplier (ISO 2 country code)
  • empfänger / empfänger_plz / empfänger_ort / empfänger_land: Address of goods recipient
  • incoterm: Abbreviation of the delivery conditions according to international trade law. In our data, you will always find incoterm FOB „Free on Board“ (Link for further information)
  • incoterm_ort: Place at which the delivery condition will be fulfilled (for FOB, this is usually the port of loading)
  • termin_lieferant: Estimated date when the supplier will deliver the goods at the port of loading
  • termin_empfänger: Estimated date when the goods will arrive at the distribution center in the country of destination
  • bb_name: Range of goods

The field „bestellnummer“ is the link to the table „gis opex_international_raw“

Table „gis opex_international_raw“

This table enriches the order data with transport-relevant information. Data fields:

  • schiff: Name of the container ship / vessel on which our container is located
  • datum_abgang: Dispatch date of the vessel at the Port of Loading
  • datum_ankunft: Arrival date of the vessel at the Port of Destination
  • bestellnummer: Link to data table „gis opex_international_bestellu“
  • container: Container ID, link to data table «gis opex_international_raw_trac»
  • incoterm: see above
  • incoterm_ort: see above
  • pod_name: Port of Destination
  • pod_land: Country of the Port of Destination
  • pod_id: ID of the Port of Destination
  • pol_name: Port of Loading
  • pol_land: Country of the Port of Loading
  • pol_id: ID of the Port of Loading
  • imo_nr: ID of a vessel/container ship, link to data table «gis opex_international_shiptrac». Attention: this ID does not change after a shipment has been delivered. It is possible that several orders have the same IMO number

The ISO 2 country code of the port (pod_land / pol_land) combined with the port ID (e.g., NLRTM for Port Rotterdam) can be used to get the geo-coordinates of these ports in the table «Ports».

Table „gis opex_international_shiptrac“

This table container data about the position, the direction as well as the speed of container ships (Link: “imo_nr”) per time (usually available every few hours).

  • date: Date and time when a ship sent the position, direction/course, and speed information
  • longitude: Degree of longitude on which a ship was positioned at a given point in time (column «date»)
  • latitude: Degree of latitude on which a ship was positioned at a given point in time (column «date»)
  • imo_number: ID of a vessel/container ship, link to data table «gis opex_international_raw»
  • course: Direction/course of a ship at a given point in time (column «date»)
  • speed: Speed (in knots) of a ship at a given point in time (column «date»)
  • name: Name of the vessel
  • destination: Upcoming (intermediate) destination of the container ship. This does not necessarily have to be the Port of Destination; it can also be a stopover.
  • eta: «Expected time of arrival» at the upcoming destination. This does not necessarily have to be the Port of Destination; it can also be a stopover.

Table „gis opex_international_raw_trac“

This table summarizes the most relevant milestones along the supply chain.

  • container: Container ID, link to data table «gis opex_international_raw»
  • schiff: Name of the vessel / container ship
  • sea_etd_pol: Estimated Time of Departure at Port of Loading
  • sea_eta_pod: Estimated Time of Arrival at Port of Destination
  • sea_dat_ank_hafen: Actual Time of Arrival at Port of Destination
  • sea_dat_cont_entl: Date of unloading of the container ship at the Port of Destination
  • sea_dat_abf_hafen: Date at which the container leaves the port (on-carriage by truck or train)
  • sea_dat_ank_vz: Data at which the container arrives at the distribution center in Switzerland.
  • pod_id: ID of the Port of Destination
  • pod_name: Name of the Port of Destination
  • pod_land: Country of the Port of Destination

Table «Ports»

This table contains information about the exact locations of different ports. The data in this table was downloaded from:

https://geonode.wfp.org/layers/esri_gn:geonode:wld_trs_ports_wfp

  • portname: Name of port
  • code: ID of port (ISO-2 country code + port ID, e.g., “NLRTM” for Port Rotterdam)
  • prttype: Type of port
  • locprecision: Precision of geo-coordinates
  • latitude: Latitude
  • longitude: Longitude
  • iso3 / iso3_op: ISO-3 country code
  • country: country