003.12.2 Estimation - New Recalculation Flow

[howardwkim]

Recalculation Logic

• Remove the rule that blocks recalculation for model and satellite datasets.
• Recalculation flow:
  1. If row counts can be estimated, then compute them using our estimation function
  2. If not computable and the dataset is cluster-only (tblDatasetsServer), skip it.
  3. Otherwise, run the standard row-count calculation.

Estimation Logic and Calculation

But all the tables that we need for this calculation should be added to controllers/catalog/fullCatalogDb.js
mapSpatialResolutionToNumber/mapTemporalResolutionToNumber should be created on the backend. Rather than hardcoding it, we should have a a function that calculates from the resolution to the number.

cmap-react/src/Components/Visualization/ControlPanel/estimateDataSize.js - existing code. We have a rule where we try not to touch legacy code. So the new estimation function that we create, we'll take inspiration from this old one, but we'll create a new one and put it in the shared directory.

• Spatial resolution: if not irregular, use existing uniform-grid logic. tblSpatial_Resolutions has columns ID and Spatial_Resolution

const mapSpatialResolutionToNumber = (resolution) => {
  let map = {
    [spatialResolutions.halfDegree]: 0.5,
    [spatialResolutions.quarterDegree]: 0.25,
    [spatialResolutions.twentyFifthDegree]: 0.04,
    [spatialResolutions.fourKm]: 0.041672,
    [spatialResolutions.twelfthDegree]: 0.083333,
    [spatialResolutions.oneDegree]: 1,
    [spatialResolutions.seventyKm]: 0.25,
    [spatialResolutions.nineKm]: 0.083333,
    [spatialResolutions.twentyFiveKm]: 0.23148,
    [spatialResolutions.fortyEighthDegree]: 0.020833333,
  };

  return map[resolution];
};

• Temporal resolution: use existing interval logic for values like 3-day or 8-day; investigate. tblTemporal_Resolutions has columns ID and Temporal_Resolution

const mapTemporalResolutionToNumber = (resolution) => {
  let map = {
    [temporalResolutions.threeMinutes]: 1,
    [temporalResolutions.sixHourly]: 1,
    [temporalResolutions.daily]: 1,
    [temporalResolutions.weekly]: 7,
    [temporalResolutions.monthly]: 30,
    [temporalResolutions.annual]: 365,
    [temporalResolutions.irregular]: null,
    [temporalResolutions.monthlyClimatology]: 30,
    [temporalResolutions.threeDay]: 3,
    [temporalResolutions.eightDayRunning]: 8,
    [temporalResolutions.eightDays]: 8,
  };

  return map[resolution];
};

• Depth resolution: use depth-bin metadata from tblDarwin_Depth and tblPisces_Depth for Darwin and Pisces. Both are single column tables, column name depth_level. These are the only two datasets with depth-bin data. Otherwise, depth will require standard row-count calculation.

Cluster Logic

• Cluster-only datasets are determined using tblDataset_Servers i.e. only server type is cluster. This should be added to the backend only.
  The following returns the dataset IDs whose entries in tblDataset_Servers use only the server alias ‘cluster’ and no others.

SELECT Dataset_ID
FROM tblDataset_Servers
GROUP BY Dataset_ID
HAVING 
    COUNT(*) = SUM(CASE WHEN Server_Alias = 'cluster' THEN 1 ELSE 0 END);

Other

• Review current query-time counting logic for simplification or optimization.

[howardwkim]

• Review row count estimation logic and determine whether to refactor, reuse, or create new
• Add cluster-only skip logic in calculateRowCounts endpoint (as instructed, not adding to frontend db)
• Add all FE tables and test, use hardcoded mappings for now
• Add DB versioning for fullcatalogDB - add cache logic on frontend to check always check for latest DB version AND checksum atop 24hr reset
• Implement dynamic mapping (temporal -> find a unit that enables whole numbers, monthly climatology requires special calculation)
• Add data access queries for new FE tables
• Include units for mapping tables for clarity
• Create isEligibleForEstimation.js
• Create new row count estimate function estimateRowCount.js
• Integrate into rowCountCalculationStore.js then test npm test -- src/shared/estimation/__tests__/estimateRowCount.test.js
• Review timeout logic on row_counts
• Review current query for row counting logic for simplification or optimization -> queries should be inclusive with constraints
• Refactor: Do estimates on search and remove from recalculation flow

[howardwkim]

• Continue to use non-irregular temporal/spatial resolutions for determining whether to estimate
• Add error handling without additional tooling

[howardwkim]

• datasets with km resolutions -> find adjacent points, subtract values to get the degree value -> may map onto existing values, else calculate - hardcode, but document in the function

[howardwkim]

/**
 * Returns hardcoded spatial resolution mappings
 * Resolution string -> numeric degree value (or null for irregular) with units
 *
 * NOTE: Some datasets have kilometer labels but their actual data resolution is
 * degree-based. 
 * Confirmed values: 70km X 70km and 9km X 9km were verified by examining actual 
 * data points and measuring differences in spatial values.
 * Estimated values: 4km X 4km and 25km X 25km are estimates as we do not have
 * datasets to confirm these resolutions.
 *
 * @returns {Array<{resolution: string, value: number|null, units: string|null}>}
 */
const getSpatialResolutionMappings = () => {
  return [
    { resolution: 'Irregular', value: null, units: null },
    { resolution: '15 arc-second interval grid', value: 0.004166667, units: 'degrees' }, // 15 ÷ 3600 = 0.004166...
    { resolution: '1/48° X 1/48°', value: 0.020833333, units: 'degrees' }, // 1 ÷ 48 = 0.020833
    { resolution: '1/25° X 1/25°', value: 0.04, units: 'degrees' },        // 1 ÷ 25 = 0.04
    { resolution: '1/12° X 1/12°', value: 0.083333, units: 'degrees' },    // 1 ÷ 12 = 0.083333
    { resolution: '1/8° X 1/8°', value: 0.125, units: 'degrees' },         // 1 ÷ 8 = 0.125
    { resolution: '1/4° X 1/4°', value: 0.25, units: 'degrees' },          // 1 ÷ 4 = 0.25
    { resolution: '1/2° X 1/2°', value: 0.5, units: 'degrees' },           // 1 ÷ 2 = 0.5
    { resolution: '1° X 1°', value: 1, units: 'degrees' },                 // 1 degree
    { resolution: '4km X 4km', value: 0.041672, units: 'degrees' },        // Estimated 1/24°
    { resolution: '9km X 9km', value: 0.083333, units: 'degrees' },        // 1/12°
    { resolution: '25km X 25km', value: 0.125, units: 'degrees' },         // Estimated 1/8°
    { resolution: '70km X 70km', value: 0.25, units: 'degrees' },          // 1/4°
  ];
};

/**
 * Returns hardcoded temporal resolution mappings
 * Resolution string -> numeric seconds value (or null for irregular/monthly climatology) with units
 * @returns {Array<{resolution: string, value: number|null, units: string|null}>}
 */
const getTemporalResolutionMappings = () => {
  return [
    { resolution: 'Irregular', value: null, units: null },
    { resolution: 'Monthly Climatology', value: null, units: null },
    { resolution: '1/6 s', value: 0.166666666666667, units: 'seconds' },
    { resolution: 'One Second', value: 1, units: 'seconds' },
    { resolution: 'Three Seconds', value: 3, units: 'seconds' },
    { resolution: '10 Seconds', value: 10, units: 'seconds' },
    { resolution: '30 seconds', value: 30, units: 'seconds' },
    { resolution: 'One Minute', value: 60, units: 'seconds' },
    { resolution: 'Three Minutes', value: 180, units: 'seconds' },
    { resolution: 'Hourly', value: 3600, units: 'seconds' },
    { resolution: 'Six Hourly', value: 21600, units: 'seconds' },
    { resolution: 'Daily', value: 86400, units: 'seconds' },
    { resolution: 'Three Days', value: 259200, units: 'seconds' },
    { resolution: 'Weekly', value: 604800, units: 'seconds' },
    { resolution: 'Eight Day Running', value: 691200, units: 'seconds' },
    { resolution: 'Eight Days', value: 691200, units: 'seconds' },
    { resolution: 'Monthly', value: 2592000, units: 'seconds' },
    { resolution: 'Annual', value: 31536000, units: 'seconds' },
  ];
};

[howardwkim]

• Estimates -> just do them without needed to recalculate
• Test estimate function against backend queries
• Row count queries should be inclusive

[howardwkim]

If regualr spatial and temporal, then depth is also regular. so, we need to check for hasDepth.

• hasDepth field - variables - bool
• Redo Pisces/Darwin logic to check for hasDepth
• There are tables outside of pisces/darwin that qualify
• new depth table logic - waiting on mohammad