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: