UI Sandbox - GED4GEM - requirements gathering for viewing grid point data

[Ben Wyss]

[et=4h]
[at=6h]

requirements gathering for viewing grid point data

The GED4GEM exposure data need to be viewed in a UI. All of the information has been placed into one flat table (so that the UI will not need to process a join for each request). This flat table will have a very very large number of rows.
We need to understand how this data can be effectively viewed in a UI.

My first attempt: I exposed the gem_exposure table to GeoServer, then tried to render some features with OpenLayers, but GeoServer timed out. When I try a pure sql query on one record in the DB it takes 10' to give a response.

We need to understand why this is taking so long, and how it can be sped up

[Ben Wyss (bmwyss)]

The gem_exposure flat table has over 144 million rows, because of this, rendering each individual record directly from this table is not possible with a reasonable amount of resources (RAM & CPU), even if the rendering is limited to a very low zoom scale.

Because of this we need to consider some other solutions for the UI.

We could reduce the grid resolution from 1 km sq (30 arc secs) to 5 km sq which would reduce the number of rows to about 5.7 mil. This might help a bit, but it is still an unruly amount of data.

We could create some static GeoTiffs for each region or country. They would limit the amount of data that the end user could access as a raster is only able to reflect one attribute per raster. To allow the user to view more than one attribute, we could create multiple GeoTiffs for each region and allow the user to toggle between them.

Using GeoTiffs would do two things:
    1. It would be an effective way for us to split the task of rendering 144 million records into manageable pieces (country or region sub-region etc.)
    2. It would allow us to manage the resolution of data, in this way we can have control over data detail vrs UI performance

This approach would allow the user to get an idea of what the underlying data looks like without actually interacting with it directly. If it is a requirement for the UI to gain access to the data, we could provided the user with the ability to download the data for a region.

[Ben Wyss (bmwyss)]

After a bit more investigating it would seem that rendering the points themselves should be possible with reasonable results.

This should be possible by spatial indexing the geometry column, putting all of the records into a single postgis instance, mark the
geometry as non null, and then cluster along the spatial index, as described here:

http://revenant.ca/www/postgis/workshop/indexing.html

and here:

http://postgis.refractions.net/documentation/manual-1.5/ch06.html#id2635907

To do all this one would need an adequate development environment, which would include more disk space then we currently have on the linode server.

[Ben Wyss (bmwyss)]

The continuation of work related to this bug is here:
GED4GEM add index to Gem_exposure https://bugs.launchpad.net/openquake/+bug/941938