1

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?

4
  • 1
    how about a shell script and ogr2ogr? – Ian Turton Feb 23 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? – Lee Hachadoorian Feb 23 at 17:44
  • Shell script running psql /COPY, then crate GEOMETRY objects (and index) in a new column. – geozelot Feb 23 at 21:34
  • @LeeHachadoorian No this is the various vehicle IDs so it is not unique across files but I do care about it! – DeMelkbroer Feb 24 at 8:26
4

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

6
  • 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 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? – DeMelkbroer Feb 24 at 8:36
  • Could I use Git Bash to run such a script? – DeMelkbroer Feb 24 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 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.. – DeMelkbroer Feb 24 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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