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