Crunching Data In GeoServer: Mastering Rendering Transformations, WPS And SQL Views

Transcript

1. Ing. Andrea Aime
   Ing. Simone Giannecchini
   Eng. Nuno Oliveira
   GeoSolutions
   Crunching Data In GeoServer:
   Mastering Rendering Transformations,
   WPS And SQL Views
   

   View Slide

2. 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.it
   FOSS4G 2019, August 26th/30th, Bucharest
   

   View Slide

3. WPS: quick intro
   

   View Slide

4. 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 2019, August 26th/30th, Bucharest
   

   View Slide

5. An Example
   ⚫
   Buffer a L shaped
   geometry with
   distance “2”
   ⚫
   Get the result back
   as GML
   FOSS4G 2019, August 26th/30th, Bucharest
   

   View Slide

6. Synchronous vs asynchronous
   WPS
   client
   WPS
   Launch
   process
   Send back
   results
   Simple
   Suitable for fast executions
   Synchronous
   WPS
   client
   WPS
   Launch process
   Status URL
   Check progress
   50%
   Check progress
   100%
   Results inline
   Link to results
   More complex
   Suitable for longer computations
   Asynchronous
   FOSS4G 2019, August 26th/30th, Bucharest
   

   View Slide

7. Common WPS setup
   WPS
   Remote
   WCS
   Remote
   WFS
   HTTP
   server
   WPS
   client
   Request
   +
   data or links to data
   Result
   data
   Fetch
   data
   FOSS4G 2019, August 26th/30th, Bucharest
   

   View Slide

8. GeoServer WPS integration
   WPS
   Remote
   WCS
   Remote
   WFS
   HTTP
   server
   WPS
   client
   All GeoServer
   Layers
   WMS
   client
   WMS
   GeoServer
   UI
   FOSS4G 2019, August 26th/30th, Bucharest
   

   View Slide

9. Demo request builder
   ⚫
   List processes
   ⚫
   Describe
   ⚫
   Set parameters and
   execute
   ⚫
   All in one form
   FOSS4G 2019, August 26th/30th, Bucharest
   

   View Slide

10. Rendering transformations
    ⚫
    On-the-fly data transformations inside rendering
    chain
    ⚫
    Calling WPS processes from SLD docs
    ⚫
    Optimized for performance
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

11. But you don’t always need a WPS
    

    View Slide

12. Spatial DBMS!
    • Never under-estimate the processing power of
    your BDMS:
    • Designed to efficiently handle 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 2019, August 26th/30th, Bucharest
    

    View Slide

13. Parametric SQL views
    WMS/WFS client
    GeoServer
    Spatial database
    &viewparams=from:2000000;high:5000000
    Expanded Query
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

14. Parametric SQL views
    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 geom
    FROM storm_obs t1
    join(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) t2
    ON(t1.obs_datetime + '06:00:00'::interval) = t2.obs_datetime
    AND t1.storm_name::text = t2.storm_name::text
    WHERE 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:
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

15. Pure SQL views: Tuna Atlas
    

    View Slide

16. Mapping Tuna Catches
    • Multiple Filtering
    • Diferent
    aggregations
    • Joining quartely
    stats against the
    grid
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

17. 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.GEOMETRY
    FROM
    (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_OAREA
    ORDER BY T.CD_TA_OCEANAREA
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

18. Compunding WFS/WCS deficiencies:
    Clips and ship services
    

    View Slide

19. 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 2019, August 26th/30th, Bucharest
    

    View Slide

20. MapStore GUI
    Buffer process called synchronously when buffer size
    changes
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

21. MapStore GUI
    Tracking download status (asynch WPS)
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

22. Download service architecture
    WPS
    GeoServer
    Layers
    MapStore
    WMS
    GetCapabilities
    List of layers
    Buffer
    DownloadEstimator
    Download
    GetStatus
    Fetch data
    Status
    database
    Shared
    Hazelcast/DBMS
    database
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

23. Dashboards and widgets
    

    View Slide

24. Widgets
    • Community WPS module plus MapStore UI
    • Aggregate data to show synthetic information
    from map layers
    • Widgets are floating on the map
    • Automatically filtered on current map view
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

25. Widgets
    • Each widget is updated using a synchronous
    WPS (or WFS) request
    • You can create / configure your own widgets
    through a simple wizard
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

26. Widgets
    • You can create / configure your own widgets
    through a simple wizard
    • You can filter the data
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

27. Dashboards
    • In Dashboards there is not a single map
    • Multiple maps can be configured and
    connected to widgets to create a dynamic
    dashboard
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

28. Dashboards
    • Another point of view on your data
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

29. Rendering transformations:
    Precision Agriculture
    with Sentinel-2 Data
    

    View Slide

30. Identifying vegetation status
    • A sentinel2 dataset, picking 3 bands for false color display
    B4, B3, B2
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

31. Identifying vegetation status
    • NDVI, Normalized Difference Vegetation Index:
    https://en.wikipedia.org/wiki/Normalized_difference_veget
    ation_index
    • Done using Jiffle, allows to run map algebra on the bands
    of an input raster layer using the Jiffle language.
    • Called from SLD via «rendering transformation»
    • GeoServer rendering pipeline will be smart enough to only
    read the bands it needs !
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

32. Identifying vegetation status
    On the fly
    calculation and
    feeding
    results into color
    map
    On the fly WPS process
    call from SLD
    (aka rendering
    transformation)
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

33. Identifying vegetation status
    • The output in a GetMap request:
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

34. Pure WPS
    calling to other computing nodes:
    WPS Remote
    

    View Slide

35. 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 2019, August 26th/30th, Bucharest
    

    View Slide

36. WPS Remote
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

37. Mixing parametric SQL views and WPS:
    The Destination Project
    

    View Slide

38. Intro
    Computing 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 2019, August 26th/30th, Bucharest
    

    View Slide

39. 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 2019, August 26th/30th, Bucharest
    

    View Slide

40. 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 2019, August 26th/30th, Bucharest
    

    View Slide

41. 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 2019, August 26th/30th, Bucharest
    

    View Slide

42. 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 2019, August 26th/30th, Bucharest
    

    View Slide

43. Parametric queries on steroids
    • Find which queries are needed, replace params
    • Some queries have sub-queries as params
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

44. 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 DBMS to
    minimize round-trip
    overhead
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide

45. 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 2019, August 26th/30th, Bucharest
    

    View Slide

46. Efficient cross layer filtering
    SELECT v_geo_popolazione_residente_pl.*
    FROM v_geo_popolazione_residente_pl
    WHERE 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 2019, August 26th/30th, Bucharest
    

    View Slide

47. The End
    Questions?
    andrea.aime@geo-solutions.it
    simone.giannecchini@geo-solutions.it
    alessio.fabiani@geo-solutions.it
    FOSS4G 2019, August 26th/30th, Bucharest
    

    View Slide