<jupyter_start><jupyter_text>Count number of total shots Takeaway:  >It takes about 2-3 seconds to read a simple feather dataframe into geopandas.  This means in total it will take about 3-4h to read in all the data.> From a rough look at the first 100 samples, there will be about 500 Mio - 1 B shots over the Amazon.  > Exact number: 452'202'228 (450 Mio.) Note: if we just want to get the lenght, we can also read via pandas:<jupyter_code>import pandas as pd

n_shots = 0
for feather in tqdm(feather_files):
    n_shots += len(pd.read_feather(feather, columns=["quality_flag"]))
    
print(n_shots)
print(n_shots)<jupyter_output>452202228<jupyter_text>Look at a sample of the dataset<jupyter_code>feather_files[0].stat().st_size / 1024 / 1024 
sample = gpd.read_feather(feather_files[0])
sample.head()<jupyter_output><empty_output><jupyter_text>Upload to PostGIS database<jupyter_code>from sqlalchemy import create_engine
import sqlalchemy as db
from src.constants import DB_CONFIG

engine = create_engine(DB_CONFIG, echo=False) 
gedi_l2a = db.Table("level_2a", db.MetaData(), autoload=True, autoload_with=engine)
for i, feather_file in enumerate(tqdm(feather_files[479:])):
    try:
        print(i+479)
        sample = gpd.read_feather(feather_file)
        sample[sample.quality_flag == 1]
        sample.to_postgis(name="level_2a", 
                          if_exists="append", 
                          con=engine, 
                          index=False, 
                          index_label="shot_number")
    except Exception as e:
        
        print(e)
        continue<jupyter_output><empty_output><jupyter_text>Load from PostGIS  Runtime comparision after uploading only `feather_files[0]`<jupyter_code>%%time
df = pd.read_sql(gedi_l2a.select(), con=engine)  # reads only data, not geometry
%%time
df = gpd.read_postgis(gedi_l2a.select(), con=engine, geom_col="geometry")  # reads geometry as well
%%time
sample = pd.read_feather(feather_files[0], columns=["granule_name"])  # read from feather format (no geometry)
%%time
sample = gpd.read_feather(feather_files[0], columns=["geometry"])  # read only geometry column from feather format
%%time
sample = gpd.read_feather(feather_files[0])<jupyter_output>CPU times: user 732 ms, sys: 104 ms, total: 836 ms
Wall time: 773 ms<jupyter_text>Test out sql query<jupyter_code>sql = "SELECT * FROM gedi_l2a"
df = gpd.read_postgis(sql, con=engine)<jupyter_output>2021-06-10 13:25:13,029 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2021-06-10 13:25:13,030 INFO sqlalchemy.engine.Engine [cached since 169.5s ago] {'name': 'SELECT * FROM gedi_l2a'}
2021-06-10 13:25:13,036 INFO sqlalchemy.engine.Engine SELECT * FROM gedi_l2a
2021-06-10 13:25:13,037 INFO sqlalchemy.engine.Engine [raw sql] {}
{'fraction_non_alphanumeric': 0.10462287104622871, 'fraction_numerical': 0.05074730622175878, 'mean_word_length': 4.461100569259962, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '11489', 'license': 'MIT', 'n_tokens_mistral': 1107, 'n_tokens_neox': 992, 'n_words': 258, 'path': 'notebooks/20-GEDI-look-at-processed-data.ipynb', 'repo_name': 'Croydon-Brixton/gedi-biomass-mapping', 'size': '2877'}