6

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?

|improve this question
14

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.

|improve this answer
  • 1
    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
4

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

|improve this answer
  • 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
3

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

|improve this answer
  • 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 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 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 -- trac.osgeo.org/postgis/ticket/899 I'll try to work on it before release. – LR1234567 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) 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

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.