API Data Mapping

Web service data integration with MapForce is a popular and proven strategy to capture timely information for analysis or generation of user-friendly reports. In an earlier post we demonstrated API data mapping in 5-day weather forecasts for busy cargo shipping ports by reading Web service data in JSON format and mapping to richly formatted Excel spreadsheets. The weather API we used  is hosted by OpenWeather, a provider of historical, current, and weather forecast data.

But integrating data from any API is not a set-it-and-forget-it task. When you build a solution based on external data, you have to react quickly when the data structure changes. Since our original integration project OpenWeather revised the data delivered by their API. The API now includes wind gust predictions in a JSON property separate from wind speed. Since wind gusts are suspected as a cause of the recent Suez Canal blockage the new data is very relevant to our application! Fortunately, both the MapForce data mapping and the Excel spreadsheet are easily revised to add new data.

We need to understand the changes in the new target Excel output before we can modify the API data mapping. The new main table section of the 5-day forecast is seen here:

Additional information required in API data mapping
Click image to view full-size

The spreadsheet creator added a new column between Wind Speed and Wind Direction to show predicted wind gusts for each forecast.

Several changes also appear in the header section of the spreadsheet. A line indicating gust speed is added to the wind chart, the geolocation coordinates of the port are added, and the maximum wind gust speed over the 5-day forecast is reported.

New information required in the API data mapping

A new clickable button in the worksheet labeled View Current Port Activity opens a Web page. Commercial ships all carry transponders for the automatic identification system (AIS) that continuously broadcast the ship’s position for collision avoidance and chart plotting. MyShipTracking.com is one of many Web sites and other enterprises that use AIS technology to report real time AIS data and provide a better understanding of how vessels move around the world.

Clicking the button in the spreadsheet opens a page at myshiptracking.com showing activity near the geolocation:

Example result of clicking the View Port Activity in the spreadsheet
Click image to view full-size

We need only a few revisions in the data mapping to support these enhancements to the target output: we need to insert the gust data at the right location in the table and we need to map the geolocation coordinates.

First, we manually executed the weather API in a browser window to get a new response example including wind gust data and we generate a new JSON Schema in XMLSpy for the API result. Saving the new JSON schema with the original file name automatically adds Gust to the Response in the API data mapping.

A JSON schema defines the structure of the API response for mapping

Labels attached to the original mapped data coordinates make it easy to find the right place to insert the new Gust column in the Excel spreadsheet:

Inserting a new column into the API data mapping for the target spreadsheet

Selecting Add Cell Before from the context menu opens a dialog to add the details:

Dialog to define details of the new target cell range

MapForce conveniently maintains the mapping connections for columns K and L so we only have to connect column J.

The new geocoordinates in the spreadsheet for latitude and longitude are at cells G5 and I5. The coordinates for each port are already in the source list of ports, since we use them to request weather forecast data from the API. We added those cell addresses at the bottom of the target list.

Here is a view of the revised mapping in the MapForce mapping pane with the new connection for wind gust predictions highlighted:

The complete revised API data mapping with wind gust and geocoordinates added to the output
Click image to view full-size

For one last enhancement we added more ports to the request list to include the Suez Canal and several large seaports in Europe. We added the new ports by editing the original JSON file with the request list in XMLSpy. Here is the new JSON file with the port requests for input to the API data mapping viewed in XMLSpy grid view:

New ports listed in the input file will generate new API requests and data mapping results
Click image to view full-size

Clicking the Output button at the bottom of the mapping pane executes the mapping and opens the Output view showing the first output file. If Excel 2007 or later is installed on the system, MapForce automatically opens Excel to present the result:

Preview of the first spreadsheet generated by the API data mapping
Click image to view full-size

Results have been generated for each port in the source list. We can select any Excel file for preview:

All 15 results have been generated and are available to preview
Click image to view full-size

Clicking the View Current Port Activity button in the Preview window works exactly as when the file is opened directly in Excel:

A clickable link in the preview window

The result is based on the port latitude and longitude:

View of vessels near the entrance to the Suez canal
Click image to view full-size

Each dot on the map represents a vessel with an AIS transponder reporting in real time. To further validate our results we can preview any other output file and check the link for another port. Here is the map for Hamburg, Germany:

The Port Activity button delivers different results in each output file
Click image to view full-size

The red dots represent tankers and the green dots are cargo ships. We can click any dot for details on the vessel:

Details of a cargo ship in port
Click image to view full-size

Catch up with the original post on Web service integration, watch a quick video demo of MapForce, or download a free trial including Tutorials, Help, and many more examples to get started with your own project for API data mapping, or other data integration, conversion, and transformation tasks!

Tags: , , , ,