We use PostgreSQL/PostGIS to manage a lot of our tabular and geographic data from the US Census. In terms of workflow we will either download a shapefile manually from ftp://ftp.census.gov/geo/tiger/ or, if we’re dealing with more than one file (block groups or blocks for example), we will do this from within R (using the
unzip() functions in a loop). In order to import the data to Windows-based PostgreSQL we can generally rely on GDAL’s org2ogr program and this works out well (ogr2ogr can be used to convert between a ton of different formats). For example we might use the code:
ogr2ogr -overwrite -lco GEOMETRY_NAME=geom -a_srs "EPSG:4269" -f "PostgreSQL" PG:"host=localhost user=postgres dbname=census password=xxx" -nlt MULTIPOLYGON existing_shapefile.shp -nln target_table
to write from a shapefile to a PostgreSQL/PostGIS database and we might use R’s
system() function to execute this on the command line.
But we have found that the default character encoding for PostgreSQL (UTF-8) does not handle the occasional names with accents or other non-English words that you find in county or Core Based Statistical Area files. Running the ogr2ogr program on county shapefiles, for example, brings up the error:
ERROR 1: Terminating translation prematurely after failed translation
In order to address this we used R’s
cat() function to create a *.bat file that first sets the encoding to LATIN1 and then run the same ogr2ogr command. So the bat file looks like:
SET PGCLIENTENCODING=LATIN1 ogr2ogr -overwrite -lco GEOMETRY_NAME=geom -a_srs "EPSG:4269" -f "PostgreSQL" PG:"host=localhost user=postgres dbname=census password=spatial" -nlt MULTIPOLYGON existing_shapefile.shp -nln target_table
You can then run the bat file with the
system() function in R and the shapefile will import without errors.