How to rename PostgreSQL/PostGIS columns while importing Shapefiles?

Question

I discovered shp2psql to successfully import geospatial data from ESRI Shapefiles into PostgreSQL/PostGIS.
Now I would like to automatically rename the target column names. Is there any mechanism where I can define the original column names and their corresponding target column names in something such as a look-up table? Or would you do the renaming within the database using triggers or rules? Or do you recommend another import tool?

It looks like the -f flag can be used for this based upon PostGIS 2.0 pgsql2shp shp2pgsql Command Line Cheatsheet — RyanKDalton, Jul 17 '14 at 14:42
@RyanDalton is -f for the filename rather than a column name in the destination file? — DPSSpatial_BoycottingGISSE, Jul 17 '14 at 15:06
I suspect you will have to use postgre sql to rename the columns after the import. See the renaming columns section on this page http://www.postgresql.org/docs/8.1/static/ddl-alter.html — Ralph Dell, Jul 17 '14 at 17:03
@mapBaker, you are correct. -f is for filename, not column name. Ummm... yeah... I really meant what LR1234567 said :) — RyanKDalton, Jul 17 '14 at 22:25

elrobis · Accepted Answer · 2014-10-13T14:48:00.870

To expand on David Bitner's answer, here's an example ogr2ogr instruction demonstrating an optional OGR SQL clause to rename fields from a source dataset (shapefile in this case) before they are brought into a target dataset (a PostGREsql table):

ogr2ogr -f "PostGreSQL" PG:"host=127.0.0.1 user=YourUser dbname=YourDB password=YourPass"
"E:\path\to\YourShapefile.shp" -nln NewTableName -nlt geometry 
-sql "SELECT col_1 AS BetterName, col_2 AS ImprovedName FROM YourShapefile" 
-lco GEOMETRY_NAME=the_geom

-nln Allows you to provide a name for the new PostGREsql table
-nlt Will allow multipart and singlepart features to exist in the same table
-sql The OGR SQL clause renaming the source fields
-lco GEOMETRY_NAME=the_geom By default ogr2ogr names the geometry field wkb_geometry, but we can use a Layer Creation Option (-lco) to name it something else, like the_geom so it will share this convention with shp2pgsql..

Gotchas: A valid ogr2ogr instruction should not have any line breaks. Also, I've had trouble copying single quotes (') and double quotes (") from web examples and pasting them into the terminal. Perhaps it's a weird unicode issue? So it's recommended to type your command in a simple text editor like notepad before pasting it into the terminal. Or just type it directly into the terminal. Either way the point is beware copy-and-pasted quotes and double quotes.

I just noticed a similar answer provided here in the context of shapefile-to-shapefile, which includes some links to additional reading on this topic. — elrobis, Oct 13 '14 at 14:51

score 4 · Answer 2 · answered Jul 17 '14 at 19:22

4

JJD yes there is. I haven't used it myself.

You use the -m option and pass a mapping file that has the old column name and new column name on each line. As Ryan alluded to here:

http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg

I suspect he was thinking of -m and mistyped -f.

Hope that helps, Regina

answered Jul 17 '14 at 19:22

Regina Obe

10,503
1
23
28

Although I have installed postgis 2.1.3+dfsg-3.pgdg13.10+2 there is no -m option listed in the man page of shp2psql 2.1.3 (r12547). I am running Ubuntu 13.10. – JJD Jul 22 '14 at 06:55
I found the feature mentioned in ticket #885 as well as in the changelog. Is the -m option listed in your version? If so, which version do you use? – JJD Jul 23 '14 at 09:17
Actually, the -m option seems to be not implemented for shp2psql as stated in ticket #899. The cheatsheet only lists the option for pgsql2shp - notice the columns P and S. – JJD Jul 23 '14 at 15:41
1

Ah yes you are right. Sorry about that. I should read my own cheatsheets. I upgraded the related ticket to implement the same feature in shp2pgsql to 2.2.0 -- http://trac.osgeo.org/postgis/ticket/899 I'll try to work on it before release. – Regina Obe Jul 23 '14 at 18:37
1

JJD if you can compile your own PostGIS I have the -m option committed for PostGIS 2.2 (trunk) http://trac.osgeo.org/postgis/ticket/899 Just trying to settle some windows/linux bot fights so I might need to rework it a bit. Anyrate give it a try if you can. – Regina Obe Jul 24 '14 at 22:12
For more info on the -m flag see: https://postgis.net/docs/using_postgis_dbmanagement.html#shp2pgsql_usage – clhenrick Mar 20 '20 at 21:09

score 4 · Answer 3 · answered Jul 23 '14 at 16:14

4

You can use ogr2ogr with the -sql option to use a sql statement

answered Jul 23 '14 at 16:14

David Bitner

531
2
4

1

+1, this is exactly what I'd do, too. Here's an example ogr2ogr call with OGR SQL: ogr2ogr -f "PostGreSQL" PG:"host=127.0.0.1 user=YourUser dbname=YourDB password=YourPass" "E:\path\to\YourShapefile.shp" -nln NewTableName -nlt geometry -sql "SELECT col_1 AS BetterName, col_2 AS ImprovedName FROM YourShapefile" – elrobis Jul 23 '14 at 16:24
@elrobis Works fine. Please convert your comment into a full answer to I can grant you the answer flag. Thanks to David for pointing into the right direction. – JJD Oct 13 '14 at 09:43
@JJD, done. I feel kinda bad stealing the points though, so I expanded on it a little more than I did in the comment. – elrobis Oct 13 '14 at 14:55
1

and what if the table has 100 columns/fields but you only want to rename a couple? Want to avoid using -sql flag with 100 fields written out. – Theo F Dec 06 '22 at 16:18

How to rename PostgreSQL/PostGIS columns while importing Shapefiles?

3 Answers3

Linked