Uploading 50Gb GIS data (csv files) contained in nested directories into PostGIS database

Question

I have two years worth of GIS data contained in CSV files stored in nested directories (per year, per month) so it looks like:

2013
   01
       2013-01-01.csv
       2013-01-02.csv
       etc.
   02
   etc.
2014
   01
       2014-01-01.csv
       2014-01-02.csv
       etc.
   02
   etc.

The files contain the fields: ID, timestamp, lat, long. Combined, it is around 50GB worth of data. I am looking for the most efficient way to load data stored in a folder hierarchy into a single table in PostGIS. (This is my 'training data', and I'll be working with 500GB later on.)

At the moment I am thinking about writing a PyQGIS script based on os.walk (still have to work out how, tips are very welcome) for loading all the points into QGIS, saving them into the same layer, and then loading that layer into PostGIS. But I can imagine this would not be most efficient.

Any suggestions for alternative routes?

Answer 1

You may choose:

• to use GDAL using ogrmerge.py like here but with setting a destination target not a file but your database compare to this example https://twitter.com/ThomasG77/status/1362118505819176966 See ogrmerge doc
• looping with bash doing something like below (not exact as it depends from your own table structure)

psql -c "CREATE TABLE your_table_name (col1 text, col_x double precision, col_y double precision, geom geometry(Point, 4326));"

for i in */*/*.csv;
  do psql -c "COPY your_table_name(col1, col_x, col_y) FROM '"$i"' DELIMITER ',' CSV;"
done;
psql -c "UPDATE your_table_name SET geom = ST_SetSrid(ST_MakePoint(col_x, col_y)) where geom is NULL;"

For docs, you can go to CREATE TABLE and COPY

You may also see how to change parameters when calling psql command line as contrary to my example, you will need to set the database name and connection related informations.

I've mentioned COPY your_table_name(col1, col_x, col_y) with explicit columns names as you may want to create a table with empty geometry column, populate the table and then update geometry column from columns content filled with the CSV COPY. You may prefer psql meta command \COPY

For performances purpose, IMHO, it's not necessary to go through QGIS to load your CSVs.