Saving the world one application at a time: Atlas of Tuna and Billfish Catches

This blog post covers some work we have done to support the FAO Fishery and Acqualture Department, specifically the FIGIS group with information dissemination about catches of Tuna and Billfish over the last 60 years globally. 

As stated on their website:

"... the Atlas presents the global distribution of catches by gear from 1950 to 2010, at 5° latitude by 5° longitude resolution, of those tuna and tuna-like species for which this distribution is generally well known on the global scale. These species consist of the so-called principal market tunas and some billfishes."

Using Geoserver, OpenLayers and Ext-JS, we built for them a simplfied viewer that allow users to access dynamically their statistics and to map them by multiple dimensions:

• by gears (one or more)
• by species (one or more)
• for a certain years' range
• for a certain quarters' range

Moreover users can decide to view the following indicators:

• Cumulative catches across years
• Average catches across years 
• Average catches across quarters

The selections made by users are reflected directly on the visualization combining data from various sources through the GeoServer; animations can be created across years as well as years' quarters to show the evolution of the catches. The result can be seen here and in the pictures below.

Let's take a look at the implementation.

Background maps are served via GeoWebCache, nothing too sophisticated there, while the maps are generated on the fly by GeoServer connected to the FAO corporate Oracle instance via SQL Views that are performing join and aggregations on the fly between tables containing live information about catches and tables containing ancillary information like species and gear types. In addition, users can print the current map (together with the eventual result of a request for info on the map), as shown here below,

or they can request to generate an animated GIF, leveraging on the WMS Animator work (see this page for additional info) using a specific panel shown below where multiple dimensions can be again controlled:

Here below the resulting animations is shown.

Let us now summarise a few points for this project:

• background maps are served with GeoWebCache which is shared with all the others mapping applications in places withing the department
• most of the data is stored inside the corporate instance of Oracle Spatial, demonstrating perfect integration of Open Source with COTS
• extensive usage of CQL filters and SQL Parametric Views has been made to generate dynamic maps and animations

Last but not least, we believe it is worth to mention that this work was performed via our GeoSolutions Enterprise Services. Therefore, if you'd like to know more about what we could achieve together, do not hesitate and get in touch with us!

The GeoSolutions team,

GeoSolutions Company Profile

At the link below you can find a one-page company profile for GeoSolutions which can also be downloaded as a PDF. Soon I will come up with a longer descriptions of the projects we have been involved since our inception.

GeoSolutions Company Profile

Enjoy,

Simone.

Supporting FAO Fishery department with a custom GeoServer DataStore for Oracle

GeoSolutions is collaborating with FAO Fishery department in order to improve the statistical GIS application "Atlas of Tuna and Billfish Catches".

The interactive version of the Atlas of Tuna and Billfish Catches presents the global distribution of 1950 to 2003 catches, at 5° latitude by 5° longitude resolution, of those tuna and tuna-like species for which this distribution is generally well known on the global scale. These species consist of the so-called principal market tunas and some billfishes.

Its refactoring mainly consists into switching the actual architecture which produces static snapshots of the statistic requested by the users through a parameter selection form, into a fully dynamic GIS map client based upon the OpenLayers served by a GeoServer/GeoTools/GeoWebCache stack working on top of an Oracle database.

Here is a common use case to solve.
Consider a table which contains the yearly catches of several fish species around the world by using different kind of gears; the user may want to issue several kind of statistical requests like for instance the total sum of the yearly catches grouped by gears and species over a specific time-range. Moreover the user wants to be able to compute these statistics on a defined by a lon,lat grid so that in the end he can either map the squares for the grid with different colors depending on the catches' statistics. In the normal approach we should create a new view which joins the catches table with the lon,lat grid table and performs the requested aggregate functions on the result.

The main issue when coping with such a complex use case was to allow GeoServer to work with an underlying complex databases structure, joining tables on the fly depending on some parameters selected by the users and being able to apply statistics function by aggregating resulting records for criteria like across years or quarters of the catches. One of our goals was to not generate views on the fly inside the database as well as not to create new layers inside GeoServer on the fly, because such an approach would have, on a side,consumed a lot of resources and on the other side, it would have required a sort of clean-up mechanism to remove old, unneeded views and GeoServer layers; in a word a nightmare.

At the end of the analysis we have come out with an architecture proposal based upon a brand new GeoTools driver capable of producing Aggregate DataStore

s on the fly, by analyzing and rebuilding the SQL at run-time and also a CQL filter adapter for GeoServer able to understand Aggregate Filters to be forwarded to the Aggregate Driver.

The proposed architecture, as depicted in the drawing aside, consists of several components:

• A Proxy which intercepts the client request and traduces the parameters selection into an appropriate Aggregate CQL filter
• The Aggregate DataStore which dynamically changes the FeatureType structure on GeoServer
• The Business logic which performs dynamic queries to the DB by merging statistical and GIS datasets.

This approach can be generalized to similar problems. The proposed architecture, in fact, aims to be the base for future GIS statistical mapping application of the Fishery department.

Actually we based the driver on the Oracle NG plugin, but with some more effort it could be extended to the JDBC one allowing it working with other Databases like for instance PostGis.

The results of the plugin are shown in the screenshots below: