Exporting all features from file geodatabase to PostgreSQL database to specified schema

Question

How can I export features from Esri file geodatabase (.gdb) to PostgreSQL database, to specified schema, with specified geometry column name?

Now I am doing this that way:

os module to get all gdb from specified dir,
ogr2ogr to get all the features from gdb and export them to PostgreSQL,
psycopg2 to update current schema ('public') to disired one and change geometry column name to 'geom' like all the other tables in PostgreSQL database.

Can I do the same using only ogr2ogr or it's another way?

• thibautg: You can use -lco GEOMETRY_NAME=geom to set the name of the geometry column to "geom" and prefix your destination table name with your schema name like that: -nln your_schema.your_table
• Pawel: Thanks, I used something like that ogr2ogr -f "PostgreSQL" PG:"host={} port={} dbname={} user={} password={}" "{}" -lco schema={} -lco 'geometry_name={}', I've tried earlier schema command but didnt know that -lco is required.
• thibautg: I've added a more detailed answer

Answer 1

To change the name of the destination geometry column, use -lco GEOMETRY_NAME=geom. By default, the geometry column is named wkb_geometry. See docs.

To set the destination schema, there are a few ways to do it according to the PostGIS driver documentation.

1) Specify the destination table prefixed with the schema with -nln your_schema.yourfeatureclass:

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=user dbname=db password=pw" "FileGDB.gdb" "yourfeatureclass" -lco GEOMETRY_NAME=geom -nln your_schema.yourfeatureclass

2) Add schemas=schema1,schema2 or active_schema=schema1 in the connection string (active_schema gives a warning but works with my version - 2.2.1 on Windows, so using schemas is preferred):

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=user dbname=db password=pw active_schema=your_schema" "FileGDB.gdb" "yourfeatureclass" -lco GEOMETRY_NAME=geom

or

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=user dbname=db password=pw schemas=your_schema" "FileGDB.gdb" "yourfeatureclass" -lco GEOMETRY_NAME=geom

3) Add the schema in an option -lco SCHEMA=your_schema

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=user dbname=db password=pw" "FileGDB.gdb" "yourfeatureclass" -lco GEOMETRY_NAME=geom -lco SCHEMA=your_schema

Warning: #3 does not work if there is a table with the same name in the public schema.