Ing. Andrea AimeIng. Simone Giannecchini
GeoSolutions
Processing Data In GeoServer With WPS And SQL Views
GeoSolutions Founded in Italy in late 2006 Expertise
• Image Processing, GeoSpatial Data Fusion• Java, Java Enterprise, C++, Python• JPEG2000, JPIP, Advanced 2D visualization
Supporting/Developing FOSS4G projects GeoServer, MapStore GeoNetwork, GeoNode, Ckan
Clients Public Agencies Private Companies
http://www.geo-solutions.itFOSS4G 2016, Bonn
22nd - 26th August 2016
Some historical perspective Year 2008:
Publish maps on the net Nice styling, maybe time/elevation based selector,
some little extra filtering Maybe some editing, some PDF printing
Since 2013: All of the above, but… We can hardly make a new application without
some data processing in it
FOSS4G 2016, Bonn22nd - 26th August 2016
Web Processing Service Wikipedia introduces OGC WPS as:
[A service] designed to standardize the way that GIS calculations are made available to the Internet.
WPS can describe any calculation including all of its inputs and outputs, and trigger its execution
The specific processes served up by a WPS implementation are defined by the owner of that implementation.
Although WPS was designed to work with spatially referenced data, it can be used with any kind of data.
FOSS4G 2016, Bonn22nd - 26th August 2016
An Example
Buffer a L shaped geometry with distance “2”
Get the result back as GML
FOSS4G 2016, Bonn22nd - 26th August 2016
Synchronous vs asynchronous
WPS client WPS
Launch process
Send back results
SimpleSuitable for fast executions
Synchronous
WPS client WPS
Launch process
Status URL
Check progress
50%
Check progress
100%Results inlineLink to results
More complexSuitable for longer computations
Asynchronous
FOSS4G 2016, Bonn22nd - 26th August 2016
Common WPS setup
WPS
RemoteWCS
RemoteWFS
HTTP server
WPSclient
Request +
data or links to data
Resultdata
Fetchdata
FOSS4G 2016, Bonn22nd - 26th August 2016
GeoServer WPS integration
WPS
RemoteWCS
RemoteWFS
HTTP server
WPSclient
All GeoServer Layers
WMSclientWMS
GeoServer UI
FOSS4G 2016, Bonn22nd - 26th August 2016
Demo request builder
List processes Describe Set parameters and
execute All in one form
FOSS4G 2016, Bonn22nd - 26th August 2016
Rendering transformations On-the-fly data transformations inside rendering
chain Calling WPS processes from SLD docs Optimized for performance
FOSS4G 2016, Bonn22nd - 26th August 2016
Spatial DBMS!• Never under-estimate the processing power of
your BDMS:• Designed to efficiently juggle large quantities of
data• Efficient spatial primitives (at least, in PostGIS)• Doesn’t get more local to your data than this!
• Passing params down? • Parametric SQL views!
FOSS4G 2016, Bonn22nd - 26th August 2016
Parametric SQL views
WMS/WFS client
GeoServer
Spatial database
&viewparams=from:2000000;high:5000000
Expanded Query
FOSS4G 2016, Bonn22nd - 26th August 2016
Parametric SQL views
FOSS4G 2016, Bonn22nd - 26th August 2016
SELECT Date_part('year'::text, t1.obs_datetime) AS obs_year, t1.storm_num, t1.storm_name, t1.wind, t2.wind AS wind_end, t1.press, t2.press AS press_end, t1.obs_datetime, t2.obs_datetime AS obs_datetime_end, St_makeline(t1.geom, t2.geom) AS geomFROM storm_obs t1join(SELECT storm_obs.id, storm_obs.storm_num, storm_obs.storm_name, storm_obs.wind, storm_obs.press, storm_obs.obs_datetime, storm_obs.geom FROM storm_obs) t2ON(t1.obs_datetime + '06:00:00'::interval) = t2.obs_datetimeAND t1.storm_name::text = t2.storm_name::textWHERE Date_part('year'::text, t1.obs_datetime) BETWEEN %min_obs_year% AND %max_obs_year% ORDER BY Date_part('year'::text, t1.obs_datetime), t1.storm_num, t1.obs_datetime
• Building lines of the fly from point data:
Soil Monitor• Computing Soil Monitor Indeces at country level
• Soil Sealing• Change Matrix
• GeoServer as the W*S Server• MapStore as the mapping Front-End
• Webmapping and WPS Orchestration• Charting
• GPU processing wrapper as WPS Processes• JCuda• Custom code to bridge between
GeoServer/GeoTools and Cuda• Direct Ingestion of the resulting raster data
• Raster Algebra as rendering transformation using Jiffle
http://www.soilmonitor.it/ FOSS4G 2016, Bonn
22nd - 26th August 2016
https://github.com/mbedward/jiffle
Mapping Tuna Catches
• Multiple Filtering
• Aggregation
• Joining quartely stats against the grid
FOSS4G 2016, Bonn22nd - 26th August 2016
Filtering, joining and aggregation
Some example control regexps:• Y_INTERV:
• Default: 1• Regex: ^(\d)+$
• OP• Default: sum• Regex: ^[avg|sum]$
SELECT (T.TS_VALUE / %Y_INTERV%) AS TS_VALUE, T.CD_TA_OCEANAREA, G.GEOMETRYFROM (SELECT CD_TA_OCEANAREA, OP%(TS_VALUE) AS TS_VALUE FROM FIGIS.TS_FI_TA WHERE FIC_ITEM IN (%FIC_ITEM%) AND CD_GEAR IN (%CD_GEAR%) AND YR_TA IN (%YR_TA%) AND QTR_TA IN (%QTR_TA%) GROUP BY CD_TA_OCEANAREA ) t LEFT OUTER JOIN FIGIS_GIS.GRID_G5 g ON T.CD_TA_OCEANAREA = g.CD_OAREAORDER BY T.CD_TA_OCEANAREA
FOSS4G 2016, Bonn22nd - 26th August 2016
Animation• Animator tool• GetMap + variying parameters + frame control
=> animated GIF!• http://docs.geoserver.org/stable/en/user/
tutorials/animreflector.html
FOSS4G 2016, Bonn22nd - 26th August 2016
Animator• …/geoserver/wms/animate?request=GetMap&…
&format=image/gif;subtype=animated&aparam=viewparams:YR_TA&avalues=2000,2001,2002,2003,…
FOSS4G 2016, Bonn22nd - 26th August 2016
Advanced Clip and Ship• Community WPS module plus MapStore UI• Requirements
• Download large amounts of data• Generic data filtering• Clip on polygon/bbox/circle, both vector and
raster• Reproject to target CRS• Band selection in raster• Work in a cluster
• Solution: new WPS processes, asynch WPS call
FOSS4G 2016, Bonn22nd - 26th August 2016
MapStore GUI
Buffer process called synchronously when buffer size changes
FOSS4G 2016, Bonn22nd - 26th August 2016
Download service architecture
WPS
GeoServer Layers
Map
Stor
eWMS
GetCapabilitiesList of layers
Buffer
DownloadEstimator
Download
GetStatus
Fetch data
Status database
Shared Hazelcast/DBMS
database
FOSS4G 2016, Bonn22nd - 26th August 2016
WPS Remote• Use GeoServer as WPS Broker Run Remote
Processes Asynchronously• Support Python or command line tools• Relies on XMPP for discovery and
messaging/logging• Supports Dismiss and basic load balancing for
different executors• Automagic results ingestion in GeoServer
FOSS4G 2016, Bonn22nd - 26th August 2016
IntroComputing the risk of road accidents involving dangerous goods (chemicals, petrol, gases and so on)
Road segments and stats about car accidents
Human and environmental «targets»
Involved area, depending on type of good and amount of damage
FOSS4G 2016, Bonn22nd - 26th August 2016
Large Data Volume• Road network of good part of northern Italy• Road divided into segments
• 100m portions (500k of them)• 500m aggregation (120k of them)• 1 km square cells (few hundreds)
• 51 buffer distances (depending on good, scenario, level of damage)
• Several types of targets: schools, malls, hospitals, populated areas, superficial and underground acquifers, crops, woods, ….
FOSS4G 2016, Bonn22nd - 26th August 2016
The road arc risk formula (s)
• Adding togheter the risk caused by the different• Arc own propension to accidentds• Types of goods• Human and enviromental targets
• The system allows to compute partial views of the formula, either by selection of targets/goods or by computing portions of it
• Has a number of coefficients that can be hand-tuned by the caller
FOSS4G 2016, Bonn22nd - 26th August 2016
The results, visually
• Rendering transformation
• Read the arcs/polys from the DB, compute their risk based on the chosen formula, scenario, targets, and coefficients
FOSS4G 2016, Bonn22nd - 26th August 2016
How to compute it efficiently?• Using SQL Views? No, the possible
aggregations variants are too many• Using a pure Java process? No, too much data
to transfer from the DBMS• Fully on the fly? No, too much data involved• Pre-compute all buffers and locate all
involved targets before hand (pre-cooked per buffer risk)
• Use a process that builds a final aggregation query on the fly (dynamic sql views)
FOSS4G 2016, Bonn22nd - 26th August 2016
Parametric queries on steroids• Find which queries are needed, replace params• Some queries have sub-queries as params
FOSS4G 2016, Bonn22nd - 26th August 2016
Efficient rendering tx
Risk process
Queries database
Arcs/Buffer areas db
Map renderer
Build overall query, replace params
Compute risk for a batch of arcs
Raw arcs
Arcs + risk
• Compute risk on the fly in the viewing area
• Batch requests to the BDMS to minimize round-trip overhead
FOSS4G 2016, Bonn22nd - 26th August 2016
Efficient cross layer filtering
• Show only targets involved in the scenario under study, e.g., the ones crossing the buffer areas where there is significant risk
• Limit query to the current bbox
FOSS4G 2016, Bonn22nd - 26th August 2016
Efficient cross layer filtering
SELECT v_geo_popolazione_residente_pl.*FROM v_geo_popolazione_residente_plWHERE v_geo_popolazione_residente_pl.fk_bersaglio_umano_pl in ( SELECT distinct bersaglio.fk_bersaglio_umano_pl FROM v_geo_popolazione_residente_pl bersaglio join siig_geo_ln_arco_1 on st_dwithin(bersaglio.geometria, siig_geo_ln_arco_1.geometria, %distanzaumano%) WHERE siig_geo_ln_arco_1.geometria && st_makeenvelope(%bounds%, 32632) )
This is a job for a parametric sql view
FOSS4G 2016, Bonn22nd - 26th August 2016
Efficient computation of multi-buffers
• Computing 51 buffers around each of 500k arcs…
• Slow!• Buffering over the
previous buffer proved to be much faster than computing each buffer from the original arc
• Custom multibuffer process
FOSS4G 2016, Bonn22nd - 26th August 2016
Intro• The Integrated Decision Aid (IDA) has been developed at the Centre for
Maritime Research and Experimentation (CMRE) in order to provide naval exercise planners with an effective tool to assess risk to marine mammals from acoustic transmissions.
• Assessment of potential hazard is performed through sound propagation modeling available in the interface.
Sound Propagation Model
FOSS4G 2016, Bonn22nd - 26th August 2016
Sound Propagation Model• External Octave Matlab Process• Logs the asynchrouns process status to the
DB as a Feature
Octave Matlab Script for SPM
WFSFollows the process status through the WFSWFSLog
Process allows to insert and update the status of SPM into the DB
Dynamically builds an external Octave command line using the input parameters through a FreeMarker Template (FTL)
FOSS4G 2016, Bonn22nd - 26th August 2016
Sound Propagation Model• The list of model runs is available on the bottom Data
Grid
• It is possible to get information on the input parameters and execution from the WFS
FOSS4G 2016, Bonn22nd - 26th August 2016
Raster Algebra Processes• Simple Raster Algebra
• Gets an OGC Filter containing logical operations between raster layers and produces a binary (0/1) layer
FOSS4G 2016, Bonn22nd - 26th August 2016
Raster Algebra Processes• Advanced Raster Algebra using Jiffle
• Wraps Jiffle (http://code.google.com/p/jiffle/) in order to execute complex Raster Algebra scripts against the input layers
Jiffle is a scripting language for creating and analysing raster images. Rather than having to write and test lots of JAI or Java AWT boiler-plate code to access and manipulate images, Jiffle lets you concentrate on the interesting bit: your algorithm.
Jiffle is being developed as part of the JAITools project.Update 16 January 2013: Jiffle sources have moved to GitHub
FOSS4G 2016, Bonn22nd - 26th August 2016
Raster Algebra Processes• Advanced Raster Algebra using Jiffle
• Gets an OGC Filter containing logical operations between raster layers and produces a binary light layer
FOSS4G 2016, Bonn22nd - 26th August 2016
Raster Algebra Processes• Raster Algebra detailed info
• Input parameters and outcomes are logged into WFS• WPS Raster Stats Process provides statistics on the
coverages
Raster Statistics and Area in sq Km
Raster Algebra Process user inputs
FOSS4G 2016, Bonn22nd - 26th August 2016
Utility Processes• WPS Raster Stats process
• WPS Temp Cleaner async process
The WPS Raster Stats process can be invoked at run-time.
It computes the Raster Stats on the current viewport area.
A cleaner process has been created to cleanup all the IDA temporary files.Is invoked asynchronously and makes use of the IDA config parameters.
Also a WFS Delete Transaction listener is available cleaning up all the input and temp files when removing a row from the Data Grid.
FOSS4G 2016, Bonn22nd - 26th August 2016