4

I am importing about 60 million records of csv data (about 25gb) to Postgresql server using ogr2ogr

After googling million times, I found out that I can make VRT file and uploading my csv file using ogr2ogr. Here's the code I used to importing data and vrt file

<OGRVRTDataSource>
    <OGRVRTLayer name="combine">
        <SrcDataSource>D:\combine.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

ogr2ogr -overwrite -progress -gt 999999999 -skipfailures -f "PostgreSQL" PG:"host=000.000.000.000 port=0000 dbname=myDB user=me password=youtellme" -lco GEOMETRY_NAME=shape -t_srs EPSG:3857 D:\input.vrt -nln output

I thought I could adjust -gt option to speed up the importing time, yet it doesn't help much.

It has been 3 days since I started to importing data to Postgresql server and only 20 million data are uploaded so far. (means that It will take almost a week to upload)

Seems like my ogr2ogr code (or VRT) can only import 100 - 200 records per second depending on the server status (see the screen capture below)

enter image description here

Improve this question
16
  • If your code is working, then don't forget that there is also a Code Review Stack Exchange.
    – PolyGeo ♦
    Commented Feb 27, 2018 at 3:50
  • @PolyGeo sure thing. Thank you so much. I didn't know about the community. Should i ask there or here though?
    – Pil Kwon
    Commented Feb 27, 2018 at 4:19
  • 1
    What is your GDAL version? In gis.stackexchange.com/questions/109564/… using --config PG_USE_COPY YES helped a lot but since GDAL 2.0 that should be the default. Are you sure that you don't have troubles with your database connection or hardware?
    – user30184
    Commented Feb 27, 2018 at 5:54
  • 2
    Why don't you just use the COPY command. It is by far the fastest way of loading CSV into Postgres and avoid all that hideous XML parsing, which is going to all loads of overhead. XML before breakfast, there should have had a health warning, I think I'd better go and look at some GeoJSON with my coffee :-)
    – John Powell
    Commented Feb 27, 2018 at 6:55
  • 4
    No, but after you have imported it, which will take hours istead of days you do: ALTER TABLE sometable ADD column geom geometry(POINT, 4326); UPDATE sometable SET geom = ST_SetSRID(ST_Makepoint(lon, lat), 4326) or something similar and then drop the text columns, if you see fit. I have used a similar approach hundreds of times and it will be vastly faster that the VRT approach, even if it involves more steps.
    – John Powell
    Commented Feb 27, 2018 at 7:04

1 Answer 1

Reset to default
8

You don't have to use a .vrt any more. ogr2ogr supports reading csv files with geometry directly since version 2.1.

The ogr2ogr command:

ogr2ogr -f "PostgreSQL" PG:"host=000.000.000.000 port=0000 dbname=myDB user=me password=youtellme" -s_srs EPSG:4326 -t_srs EPSG:3857 -progress -nln output -lco OVERWRITE=YES  -lco GEOMETRY_NAME=shape -oo X_POSSIBLE_NAMES=longitude -oo Y_POSSIBLE_NAMES=latitude combine.csv

It might be quicker to do the geometry transformation in the database.

Improve this answer
8
  • 1
    Also Starting with GDAL 2.0, COPY is used by default when inserting from a table that has just been created. Obviously "from" means really "into".
    – user30184
    Commented Feb 27, 2018 at 14:08
  • @user30184 so it is! Amended the answer.
    – HeikkiVesanto ♦
    Commented Feb 27, 2018 at 14:11
  • HALLELUJAH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! i didn't do anything with my Server setting and it records 40K records in a sec. Thank you so much and also thanks to user30184 and john!
    – Pil Kwon
    Commented Feb 27, 2018 at 14:56
  • Excellent, that is quite a good speed up. Still worth learning psql, though :-)
    – John Powell
    Commented Feb 27, 2018 at 15:02
  • 1
    Ah, I should have noticed that. Skipfailures is forcing the size of the transactions into 1. If writing into database fails it leads to rollback which affects the whole transaction. Therefore inserting rows one by one is the only possibility to skip only problematic rows. It should be mentioned in the ogr2ogr manual page.
    – user30184
    Commented Feb 27, 2018 at 15:24

Your Answer

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

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