This is a report from the School of Data Journalism organised by Open Knowledge, European Journalism Centre, and International Journalism Festival. The session was led by Giannina Segnini who headed a team of journalists and computer engineers at La Nacion, Costa Rica’s newspaper of reference, until early February 2014. She is fully dedicated to uncovering investigative stories by gathering, analyzing and visualizing public databases.

You can download the presentation and all other data sets used in this workshop here. You can also find here the video recording of this workshop.

One of the best workshops at the School of Data Journalism in Perugia gave participants the tool, instruments and techniques to investigate crime and corruption by tracking maritime databases.

Understand how shipments work

##Codes

We need to change our mind-set! Everything in this world, from shoes to military weapons, ivory and chemical products have a Harmony System Code, known as HS Code. If you want to follow and understand international trade you have to get familiar with this.

The UN United Nations Commodity Trade Statistics Database contains detailed imports and exports statistics reported by statistical authorities from around the world.

Go to “Annual Data”, select “Fast Track” from the menu and then select “Country List” to get all data by country or “Commodity List” to look at specific goods.

Here you can observe that both countries and goods have their specific codes.As you can see in this screen grab, there are categories of codes, like 89 for ships and more descriptive codes like 8901, 8902, etc. describing specific types of boats. Codes can go up 6 digits, each digit adding more information to the code.

Let’s explore this data further. Go to back to the http://comtrade.un.org/, select “Monthly Data” which gives you a friendlier interface. We are looking for all Italian imports of “potassium permanganate” (code: 284161), a chemical substance used for making cocaine from all over the world.

We can download the data in comma separated value (CSV) format and import it in any spreadsheet programme.

To investigate this further, we can compare what Italy reports as imports (the query we just made) and what the rest of the world reports as exports towards Italy. We just need to download the same data with “World” as reporter, Italy as partner and “exports” as trade flows.

In the real world what you import should match what the other countries are reporting as export. However, with a simple analysis we can see that Italy imports a total value of potassium permanganate of about 11 million dollars since 2010 while the rest of the world only reports about 6 million. If we look more carefully, for example with a simple pivot table we notice that France and Spain are not recording everything.

##Containers

Like countries and commodities, also containers have a code. You need to learn how to decode the code. According to ISO standard 6346 (Freight Containers-coding, identification and marking), the BIC allocates an owner code to every container owner or operating company. Such codes are listed in the official Register “containers bic-code”. This task is completed with the assistance of a worldwide network National Registration Organisations.

Codes include information about the company owning it, product group code, serial number, check digit.

Let’s practice this a bit with our container SUDU5004991.

######Step 1: Search who owns the container.

On the Bic-code.org home page, go to “Bic Codes” from the menu and select “Registered Codes”.

The result will contain the company name and country where it’s registered. In our case is HAMBURG SUED.

######Step 2: Search for the itinerary

Containers are like DHL registered email, you can trace them, typically directly from their handling company. Go on the site of the company and search the container number: SUDU5004991. You can typically find a “track” search box.

You can find out where the container is, where it’s coming from and where it’s heading to. You will also get the voyage number which will help us track the container around the world using customs and other resources.

Sometimes you will find that the owner of the container is not a shipping line. It can be a company that leases containers to another company. Don’t worry, you can go to the real owner who must have a search box where you can do a unit inquiry and you get who is operating the container and from there you keep searching and searching until you find what you are looking for.

Step 3: Search on customs by container

Once we have the owner and the container, you can search for more information both about the vessel and the container. You can get the name of the exporter and most importantly, the bill of landing.

If you don’t find the info in Italy you might find it in another country, things are registered everywhere. For example, most ships coming to Italy with drugs come from Peru. If you can’t find the information on the Italian customs, look on the Peruvian side.

The best resource for finding custom organisations is the World Customs Organisations.

##Bill of Lading

A bill of lading is a document issued by a carrier to a shipper of goods. It is a standard form which serves as a receipt for the goods shipped, as evidence of the contract of carriage and as a document of ownership.

The bill of lading is the master document. From here you can find out who is the exporter, the shipper, the consignee, who has to be notified when the cargo arrives, the port of loading, the port of discharge, the vessel and so on.

Investigation techniques

There are many online services providing data about maritime and air shipping. Some need a login and some have a pricing model but in all you can get some information for free. Unfortunately, they have different database structures but the magic happens when we combine all sources!

The logic behind this kind of investigations is that of chained searches. You can find anything you want if you know how to combine sources – go from general to specific until you get what you need!

######Technique 1

• Start with the container number
• Container leads to bill of lading
• Bill of lading leads to shipper and consignee
• Shipper leads to owner or representative

######Technique 2

• Sometimes all you will have is the bill of lading
• Containers
• Vessels
• Route

######Technique 3

• Sometimes you are only investigating people or companies like shipper or exporter
• From the company you get a list of bills of lading
• Shipment
• Consignee
• Importer’s name, owner or representative

#Other useful maritime databases

• Sicex
• Import Genius 
• Piers
• Port Examiner, free but not real time data, nothing is blocked, data is a month old
• Enigma, one of the best tools.

Here you can see consolidated data from United States, United Nations and all other international organisations. The information is up to date and you can all documentation of cargo shipping and air cargo and terrestrial transportations including shipper name, container number, bill of lading number, ports, etc.

• World Wide Ships registry

More than 20 database in one where you can search by ship particular, like IMO number (the codes for ships).

• US: Ships refusal of access
• Europe: Ships refusal of access
• Panamá – Ships registry
• Marine casualties and incidents 

This is a great database for Europe containing detentions and banning. Search here all info about ships detained, including a historic database. See “details” and get the complete report plus all information of why it was detained!

• Marine traffic

Ships: real time vessel location, itineraries history, pictures. Every single ship with an EIS (like a GPS) number, and using this you can also see all the ships parked in every single port around the world, filter by type of vessel (e.g. cargo vessel), see vessel details and get all the information, including history.

• Plane Finder

You have the same for planes, all air traffic in real time.

#Practice, practice, practice

Here are some exercises you can use to practice:

• Track these containers and the ships that transported them: CXRU1455801, CXRU1454127

• Provide the owner’s name, phone number (headquarters) of the ship transporting the Syrian chemical weapons that will arrive soon to Gioia Tauro Port in Calabria, Italy. Provide the last position (coordinates) received from this vessel. Find the owner name and the last position of the US ship where the cargo will be transferred in Italy.

• In May 5, 2013, the Police and the Guardia di Finanza in Calabria seized another container carrying 190 kg of cocaine at port Gioia Tauro. Here is one of the videos about that operation. Track the LAST cargo transported by this container this year, describe the cargo, list the importer and the exporter and their addresses.

• Try to track the last drugs seizures (listed in a separate file Gioia Tauro) in port Gioia Tauro. You need to get additional information than the one on the web.