3

The situation

So I have a shapefile with several columns:

id|name_en|name_es|name_de|name_fr|...

I also have an existing PostGIS table on my remote server:

county_id|county_name|county_name_de|has_rain|...

What I want to achieve

Now I would like to INSERT data from the shapefile into my PostGIS table. However, I only need specific columns from it (namely the name_en, name_de and geometry). The remaining columns in my PostGIS table have a DEFAULT or are nullable.


What I have tried

I had a look into shp2pgsql and ogr2ogr but it seems that I cannot do my above query without creating a new table. It seems the only option I have is to drop all columns from the shapefile that I don't need, rename the columns to match the column names of the PostGIS table, and use the -a flag of shp2pgsql to insert the data into my table. (or using tools like FME)

Is there perhaps a more elegant way to do this?

1
  • ogr2ogr with -append -update and -dialect sqlite -sql 'SELECT id AS country_id, name_en AS country_name, ..., geometry FROM <shapefile_name> should do what you want. – geozelot Aug 13 '20 at 6:30
0

You may use -m switch of shp2pgsql in the command line. Before it you have to create a simple text file with new and old column names (space separated, no leading or trailing spaces). In your case for example in columns.map file:

country_id id
country_name name_en
country_name_de name_de
...

Then use the following command:

shp2pgsql -m columns.map your_shape.shp > script.sql

This way all columns will be copied, not only those mentioned in columns mapping file. New columns won't be added. So after executing SQL script you have to drop columns and add columns using ALTER TABLE commands.

With ogr2ogr you can rename columns and skip unnecessary columns using an SQL query:

ogr2ogr -f PostgreSQL -sql "SELECT id as country_id, name_en as country_name, name_de as country_name_de from your_shape_name" PG:connection_details your_shape_name.shp

Columns which are not mentioned in the SQL are not copied to the database. Extra columns should be added manually.

0

As stated in the comments and in the answer by @Zoltan, you can do that by using the -sql parameter.

In this case you can use either OGRSQL or SQLite dialect. The syntax is a bit different because with OGRSQL geometry is selected automatically. With SQLite dialect you must select also geometry. Sometimes it may be necessary to rename the geometry to match the name of the geometry_field in PostGIS but my test did succeed also without having as wkb_geometry. Naturally you must take care that the other ogr2ogr parameters are correct. In my test data I had to use -nlt promote_to_multi because the geometry type of "states" table was multipolygon, but the one geometry that was selected from the shapefile was polygon.

Example with OGRSQL dialect that is also the default dialect:

ogr2ogr -f postgresql -update -append pg:"dbname=my_database user=user password=password" -dialect OGRSQL -sql "select male as female from states limit 1" states.shp -nlt promote_to_multi -nln states 

Example with SQLite dialect:

ogr2ogr -f postgresql -update -append pg:"dbname=my_database user=user password=password" -dialect sqlite -sql "select geometry as wkb_geometry, male as female from states limit 1" states.shp -nlt promote_to_multi -nln states

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.