1INSTALL httpfs; LOAD httpfs;
2SET s3_endpoint='s3-west.nrp-nautilus.io';
3SET s3_url_style='path';
4SET s3_use_ssl=false;
5
6-- Priority disadvantaged tracts (high climate burden + high socioeconomic burden)
7SELECT GEOID10, SF AS state, CF AS county, TC, CC, POV_ET, LLE_ET
8FROM read_parquet('s3://public-rivers/american-rivers/roo-cjest.parquet')
9WHERE EPL_ET >= 90 AND POV_ET >= 65
10ORDER BY TC DESC LIMIT 20;
11
12-- Average scores by state
13SELECT SF AS state, COUNT(*) AS tracts,
14       AVG(TC) AS avg_tc, AVG(POV_ET) AS avg_pov, AVG(LLE_ET) AS avg_lle
15FROM read_parquet('s3://public-rivers/american-rivers/roo-cjest.parquet')
16GROUP BY SF ORDER BY avg_tc DESC LIMIT 15;

1INSTALL httpfs; LOAD httpfs;
2SET s3_endpoint='s3-west.nrp-nautilus.io';
3SET s3_url_style='path';
4SET s3_use_ssl=false;
5
6-- Priority disadvantaged tracts (high climate burden + high socioeconomic burden)
7SELECT GEOID10, SF AS state, CF AS county, TC, CC, POV_ET, LLE_ET
8FROM read_parquet('s3://public-rivers/american-rivers/roo-cjest.parquet')
9WHERE EPL_ET >= 90 AND POV_ET >= 65
10ORDER BY TC DESC LIMIT 20;
11
12-- Average scores by state
13SELECT SF AS state, COUNT(*) AS tracts,
14       AVG(TC) AS avg_tc, AVG(POV_ET) AS avg_pov, AVG(LLE_ET) AS avg_lle
15FROM read_parquet('s3://public-rivers/american-rivers/roo-cjest.parquet')
16GROUP BY SF ORDER BY avg_tc DESC LIMIT 15;

1map.addSource('roo-cjest', {
2  type: 'vector',
3  url: 'pmtiles://https://s3-west.nrp-nautilus.io/public-rivers/american-rivers/roo-cjest.pmtiles'
4});
5
6map.addLayer({
7  id: 'roo-fill',
8  type: 'fill',
9  source: 'roo-cjest',
10  'source-layer': 'roo-cjest',
11  paint: {
12    'fill-color': [
13      'interpolate', ['linear'], ['get', 'TC'],
14      0, '#f7fbff',
15      50, '#6baed6',
16      100, '#08306b'
17    ],
18    'fill-opacity': 0.75
19  }
20});

1map.addSource('roo-cjest', {
2  type: 'vector',
3  url: 'pmtiles://https://s3-west.nrp-nautilus.io/public-rivers/american-rivers/roo-cjest.pmtiles'
4});
5
6map.addLayer({
7  id: 'roo-fill',
8  type: 'fill',
9  source: 'roo-cjest',
10  'source-layer': 'roo-cjest',
11  paint: {
12    'fill-color': [
13      'interpolate', ['linear'], ['get', 'TC'],
14      0, '#f7fbff',
15      50, '#6baed6',
16      100, '#08306b'
17    ],
18    'fill-opacity': 0.75
19  }
20});