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 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 postgresql.org/docs/8.1/static/ddl-alter.html – Ralph Dell Jul 17 '14 at 17:03
-
1@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
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 fieldwkb_geometry
, but we can use a Layer Creation Option (-lco
) to name it something else, likethe_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.
-
1I 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
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
-
Although I have installed
postgis 2.1.3+dfsg-3.pgdg13.10+2
there is no-m
option listed in the man page ofshp2psql 2.1.3 (r12547)
. I am running Ubuntu 13.10. – JJD Jul 22 '14 at 6: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 9:17 -
Actually, the
-m
option seems to be not implemented forshp2psql
as stated in ticket #899. The cheatsheet only lists the option forpgsql2shp
- notice the columnsP
andS
. – JJD Jul 23 '14 at 15:41 -
1Ah 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 -- trac.osgeo.org/postgis/ticket/899 I'll try to work on it before release. – LR1234567 Jul 23 '14 at 18:37
-
1JJD if you can compile your own PostGIS I have the -m option committed for PostGIS 2.2 (trunk) 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. – LR1234567 Jul 24 '14 at 22:12
You can use ogr2ogr with the -sql option to use a sql statement
-
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 9: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