PostGIS – Import GeoDataFrame Using osm2pgsql

geopandaspostgis

I use python and GeoPandas to preprocess data in a GeoDataFrame. Now I want to import these data into PostGIS. I know that I could do it with python directly but this is too slow. My application needs to be as fast as possible.
Therefore I want to make use of https://osm2pgsql.org/ because is is written in C and is supposed to be very fast.

Problem
osm2pgsql only supports XML, PBF or O5M format. How can I export the GeoDataFrame to pbf (preferred)? I could also export it as GeoJson, but then I need to convert GeoJson to PBF, but how? I also had a look at osmium https://osmcode.org/osmium-tool/ but I think only pbf -> geojson is possible.

Best Answer

To improve your load time into PostgreSQL database do an export of your geodataframe to csv, then import resulting file to the database using COPY command and psycopg that performs better than directly using to_sql or to_postgis over dataframe, as:

#write Geodataframe to csv
gdf.to_csv('geodataframe.csv', sep='|', index=False)
#perform COPY, requires an existing table
pg_conn = psycopg.connect('postgres://user:password@host/database')
pg_cur = pg_conn.cursor()
sql = ''' COPY your_schema.your_table 
            FROM 'geodataframe.csv' 
            DELIMITER '|' CSV HEADER;
        '''
pg_cur.execute(sql)
pg_conn.commit()
pg_cur.close()
Related Question