Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

Simone Giannecchini
PRO

August 29, 2019
Tweet

More Decks by Simone Giannecchini

Other Decks in Technology

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?
    [email protected]
    [email protected]
    [email protected]
    FOSS4G 2019, August 26th/30th, Bucharest

    View Slide