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?
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
-nlnAllows you to provide a name for the new PostGREsql table
-nltWill allow multipart and singlepart features to exist in the same table
-sqlThe OGR SQL clause renaming the source fields
-lco GEOMETRY_NAME=the_geomBy default ogr2ogr names the geometry field
wkb_geometry, but we can use a Layer Creation Option (
-lco) to name it something else, like
the_geomso 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.
You can use ogr2ogr with the -sql option to use a sql statement
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:
I suspect he was thinking of -m and mistyped -f.
Hope that helps, Regina