Data Discussion

[lkiesow]

Unanswered Questions

What data do we have and or want?

• Do we want to import the whole two years now?
• How do we do continuous imports?
• Do we import multiple products?
• Do we import/store additional data (qa-value or precision)?
• Do we pre-filter values?

Database considerations

• Do we keep one table per product? *¹
  • Should be more flexible to add later on.
  • Queries might be a bit faster.
• Do we have all the products in one table? *²
  • We likely need less space for the data.
  • We might need less memory.

*¹) Multiple Tables

Geo-locations might be duplicated

id	lon	lat	CO
1	2.12	5.43	0.0258
2	2.22	6.73	0.0159

id	lon	lat	SO²
1	2.12	5.43	0.0124
2	2.22	6.73	0.0921

*²) One Table

Table might be sparse

id	lon	lat	CO	SO²
1	2.12	5.43	0.0258	0.0124
2	2.12	5.43		0.0098
3	2.22	6.73	0.0159	0.0921

Available Data Products

Of the ESA Data Products the following data might be of interest to us:

• Level-2
  • geolocated total columns of ozone, sulfur dioxide, nitrogen dioxide, carbon monoxide, formaldehyde and methane
  • geolocated tropospheric columns of ozone
  • geolocated vertical profiles of ozone
  • geolocated cloud and aerosol information (e.g. absorbing aerosol index and aerosol layer height)

Suggested products for us using identifiers from the Products and Algorithms description:

• L2__O3____ – Ozone (O3) total column
• L2__NO2___ – Nitrogen Dioxide (NO2), total and tropospheric columns
• L2__SO2___ – Sulfur Dioxide (SO2) total column
• L2__CO____ – Carbon Monoxide (CO) total column
• L2__CH4___ – Methane (CH4) total column
• L2__HCHO__ – Formaldehyde (HCHO) total column

Data available in the nc files

importing dimension scanline(2) (was 4173)                                                                                                                                                                                                    
importing dimension ground_pixel(215)                                                                                                                                                                                                         
importing dimension corner(4)                                                                                                                                                                                                                 
importing dimension time(1)                                                                                                                                                                                                                   
importing dimension layer(50)                                                                                                                                                                                                                 
skipping scanline                                                                                                                                                                                                                             
skipping ground_pixel                                                                                                                                                                                                                         
skipping time                                                                                                                                                                                                                                 
skipping corner                                                                                                                                                                                                                               
skipping layer                                                                                                                                                                                                                                
importing delta_time((dtype('int32'), ('time', 'scanline')))                                                                                                                                                                                  
skipping time_utc                                                                                                                                                                                                                             
importing qa_value((dtype('uint8'), ('time', 'scanline', 'ground_pixel')))                                                                                                                                                                    
importing latitude((dtype('float32'), ('time', 'scanline', 'ground_pixel')))                                                                                                                                                                  
importing longitude((dtype('float32'), ('time', 'scanline', 'ground_pixel')))
importing carbonmonoxide_total_column((dtype('float32'), ('time', 'scanline', 'ground_pixel')))
skipping carbonmonoxide_total_column_precision

Precision may be of interest:

% gdalinfo ...nc | grep carbonmonoxide_total_column_precision
  PRODUCT_carbonmonoxide_total_column_ancillary_variables=carbonmonoxide_total_column_precision
  PRODUCT_carbonmonoxide_total_column_precision_coordinates=longitude latitude
  PRODUCT_carbonmonoxide_total_column_precision_long_name=Standard error of the vertically integrated CO column
  PRODUCT_carbonmonoxide_total_column_precision_multiplication_factor_to_convert_to_molecules_percm2=6.0221409e+19 
  PRODUCT_carbonmonoxide_total_column_precision_standard_name=atmosphere_mole_content_of_carbon_monoxide standard_error 
  PRODUCT_carbonmonoxide_total_column_precision_units=mol m-2
  PRODUCT_carbonmonoxide_total_column_precision__FillValue=9.96921e+36 
  PRODUCT_carbonmonoxide_total_column_precision__Netcdf4Dimid=3

[lkiesow]

Additionally, we should talk about if we want to filter data or want to align the data to a grid.

[shaardie]

* Do we want to import the whole two years now?

I think it is important to have the whole time span to have a benefit for external users.

* Do we import multiple products?

I would rather limit the products than the time span.

* Do we import/store additional data (qa-value or precision)?

I would rather limit the additional data than the time span. Depends highly on the amount of data.

[EmbeddedHenni]

* Do we want to import the whole two years now?

I think it is important to have the whole time span to have a benefit for external users.

* Do we import multiple products?

I would rather limit the products than the time span.

* Do we import/store additional data (qa-value or precision)?

I would rather limit the additional data than the time span. Depends highly on the amount of data.

since i thought over the goals we could set up for the end of prototypefund i agree to @shaardie

[tibroc]

The only thing I have to add to @shaardie and @EmbeddedHenni is to the question if we prefilter data:

I would suggest prefiltering with quality threshold '0.7' (debatable if 1.0 is a better choice). Everything below that is by ESA either labeled invalid or experimental data and I don't think that this is of interest for our audience.

[TylerBird]

• Do we want to import the whole two years now?
  Me: Here I would agree with @shaardie. If we can do this easily, we should do that.
• How do we do continuous imports?
  Me: What does that mean?
• Do we import multiple products?
  Me: I think this don't have to be a must-have for the prototype until feburary.
• Do we import/store additional data (qa-value or precision)?
  Me: Here I am not sure how difficult that is!?
• Do we pre-filter values?
  Me: Here I'm not sure, aren't we are filtering yet?

[lkiesow]

I would suggest prefiltering with quality threshold '0.7' […]

Just a reminder that the ESA recommended minimum quality threshold is actually 0.5:

PRODUCT_qa_value_comment=A continuous quality descriptor, varying between 0 (no data) and 1 (full quality data). Recommend to ignore data with qa_value < 0.5

I think the 0.7 originates from the usage article we discussed at the beginning of the project.

---

How do we do continuous imports?

Me: What does that mean?

Sentinel-5P and later the Sentinel-5 satellites continue to generate data. We probably want this new data to be imported automatically, as soon as it is publically available and we probably do not want to do that manually.

[tibroc]

@lkiesow you are right, 0.5 is recommended and I agree that that should be used (which for carbon monoxide effectively results in only values of 0.7 or higher).

[lkiesow]

Relevant for climate change (more relevant ones at the top):

• Methane
• NO2
• Ozone

Less important (catalyst?):

• Formaldehyde
• CO

Health:

• SO2

[shaardie]

I did some testing with additional columns to our database.

I used \timing in the psql command line to measure execution time of the queries and

SELECT AVG(value) from carbonmonoxide LIMIT 10000;

as the test query.

I ran this query 10 times against our regular database and got

Time: 62779.256 ms (01:02.779)
Time: 65553.870 ms (01:05.554)
Time: 63480.244 ms (01:03.480)
Time: 66427.530 ms (01:06.428)
Time: 66858.713 ms (01:06.859)
Time: 69165.144 ms (01:09.165)
Time: 66412.289 ms (01:06.412)
Time: 68247.839 ms (01:08.248)
Time: 67704.718 ms (01:07.705)
Time: 66728.666 ms (01:06.729)
average = 66335.8269

with an average time of 66335.827 ms (01:06.335).

Now I altered the table and add a new column

ALTER TABLE carbonmonoxide ADD value2 double precision;

and ran the queries again

Time: 63001.563 ms (01:03.002)
Time: 69076.575 ms (01:09.077)
Time: 64206.951 ms (01:04.207)
Time: 61049.544 ms (01:01.050)
Time: 67589.539 ms (01:07.590)
Time: 65992.319 ms (01:05.992)
Time: 69097.175 ms (01:09.097)
Time: 67216.223 ms (01:07.216)
Time: 65218.635 ms (01:05.219)
Time: 66989.275 ms (01:06.989)

with an average time of 65943.780 ms (01:05.943).
So the new column does not have an effect on the query execution time, but this is a new empty column. What happens if I add values to it?

So after that I updated all rows

UPDATE carbonmonoxide SET value2 = 0.1235132416;

after that I ran the query again and sadly I got

Time: 98463.692 ms (01:38.464)
Time: 104990.958 ms (01:44.991)
Time: 122920.417 ms (02:02.920)
Time: 125488.922 ms (02:05.489)
Time: 134387.733 ms (02:14.388)
Time: 133847.260 ms (02:13.847)
Time: 128899.655 ms (02:08.900)
Time: 132986.167 ms (02:12.986)
Time: 137798.222 ms (02:17.798)
Time: 142054.079 ms (02:22.054)

with an average time of 126183.711 ms (02:06.183), so the query execution time nearly doubles.

[shaardie]

I did some tests with different queries for selecting bounding boxes. I ran every query 10 times and calculate the average. Here are the results:

SELECT AVG(value) FROM carbonmonoxide WHERE ST_Within(geom, 'POLYGON((-171.791110603 18.91619,-171.791110603 71.3577635769,-66.96466 71.3577635769,-66.96466 18.91619,-171.791110603 18.91619))') AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time: 2053.678 ms (00:02.054)

SELECT AVG(value) FROM carbonmonoxide WHERE ST_Contains('POLYGON((-171.791110603 18.91619,-171.791110603 71.3577635769,-66.96466 71.3577635769,-66.96466 18.91619,-171.791110603 18.91619))', geom) AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time: 1978.227 ms (00:01.978)

SELECT AVG(value) FROM carbonmonoxide WHERE ST_Intersects('POLYGON((-171.791110603 18.91619,-171.791110603 71.3577635769,-66.96466 71.3577635769,-66.96466 18.91619,-171.791110603 18.91619))', geom) AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time: 1997.349 ms (00:01.997)
 
SELECT AVG(value) FROM carbonmonoxide WHERE geom && 'POLYGON((-171.791110603 18.91619,-171.791110603 71.3577635769,-66.96466 71.3577635769,-66.96466 18.91619,-171.791110603 18.91619))' AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time:  1592.877 ms (00:01.593)
 
SELECT AVG(value) FROM carbonmonoxide WHERE geom && ST_MakeEnvelope(-171.791110603, 18.91619, -66.96466, 71.3577635769) AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time: 1555.928 ms (00:01.556)

SELECT AVG(value) FROM carbonmonoxide WHERE carbonmonoxide.geom @ ST_MakeEnvelope(-171.791110603, 18.91619, -66.96466, 71.3577635769) AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time: 2265.131 ms (00:02.265)

SELECT AVG(value) FROM carbonmonoxide WHERE ST_X(geom) >= -171.791110603 AND ST_X(geom) <= -66.96466 AND ST_Y(geom) >= 18.91619 AND ST_Y(geom) <= 71.3577635769 AND timestamp >= '2019-02-01' AND timestamp < '2019-02-11';
Time: 8441.977 ms (00:08.442)