map contour in db for speed and use in API and app

[bbest]

PROBLEM: map_contours() in R too slow

The calcofi4r::map_contours() (example, documentation) is finally the "right" level of detail to map interpolated values so as to show an overall trend. This moreso than earlier iterations of the app's map visualization using individual points of oceano app or interpolated rasters of oceano-demo as discovered through "co-design" with Jenn & Erin. However, the bivariate GAM smooth runs in R and this is slow to render: 5-20 seconds.

SOLUTION: ST_Contour() in Database

We could implement a button with an estimated progress bar, spinny wheel and/or ETA.

Or we could try run this in the database, which should be much faster:

• ST_Contour()
• ST_InterpolateRaster()
  • GDAL Grid Tutorial — GDAL documentation
  • gdal_grid — GDAL documentation
• pgfaceting: Faceted query acceleration for PostgreSQL using roaring bitmaps
• Waiting for PostGIS 3.2: ST_Contour and ST_SetZ - Jul 23, 2021
• PostGIS 3.2 New and Improved - Jan 13, 2022
  • ST_InterpolateRaster()
  • ST_Contour()
  • ST_Value() function (and ST_SetZ() and ST_SetM()) has been upgraded to support bilinear interpolation of values from rasters; eg for doing transects interpolating from raster surfaces
  • ∆ ST_IsValid(), + ST_MakeValid()

[bbest]

Now noticing polygonize argument in ST_Contour(), but seeing potential issues / workarounds with its use:

• postgis - ST_Contour doesn't produce polygon - GIS Stack Exchange
• postgis - ST_Polygonize creating single polygon - GIS Stack Exchange

See also:

• GDALContourGenerateEx() - gdal_alg.h: GDAL Algorithms C API — GDAL documentation
• ST_InterpolateRaster()
  • Interpolation algorithms - gdal_grid — GDAL documentation
  • GDAL Grid Tutorial — GDAL documentation