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


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?

  • 1
    how about a shell script and ogr2ogr?
    – Ian Turton
    Feb 23, 2021 at 17:20
  • Is ID unique across files, and do you care about it? That is, do you need or want a new primary key? Feb 23, 2021 at 17:44
  • Shell script running psql /COPY, then crate GEOMETRY objects (and index) in a new column.
    – geozelot
    Feb 23, 2021 at 21:34
  • @LeeHachadoorian No this is the various vehicle IDs so it is not unique across files but I do care about it! Feb 24, 2021 at 8:26

1 Answer 1


You may choose:

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;"
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.

  • 2
    Note that, while the server side SQL COPY may be a tad faster, the data needs to be accessible to the server instance, whose permissions are usually limited to the PG_DATA location on disk! psql has a client side /COPY meta command that is by far more versatile. The difference between using PGs native COPY protocol over INSERTs (or worse, proxy via QGIS) will be several hours...
    – geozelot
    Feb 24, 2021 at 7:01
  • Thanks for helping out @ThomasG77 ! I think I understand your approach, but I have never worked with bash/shell scripting. Does anyone know a good resource how to use it with GDAL/PostGIS? Feb 24, 2021 at 8:36
  • Could I use Git Bash to run such a script? Feb 24, 2021 at 8:41
  • 1
    You can, but if you are a windows user then powershell or a cmd window and batch file will work too (with a different less portable syntax)
    – Ian Turton
    Feb 24, 2021 at 8:49
  • Could someone comment the code above so I can better understand it? questions I have: since he just states 'in /*/.csv', do I need to be in the folder where the nested directories are located? How would I loop through the directories as shown above? Is the i the rows in each csv file or one of the csv files itself. In this is example, are these assumptions correct > col1=ID, col_x=lat, col_y=long, timestamp=??. Why do I need a geometry column here, I don't have polygon data in the files. Sorry for the many questions, I am new to this.. Feb 24, 2021 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.