Re-projecting shapefile data during load in PostGIS

I recently came across the need to load data into PostGIS from Shape files. However, some data was in one projection and other data was in a different projection system. I needed to re-project the data at the time of loading the data.

The process is fairly straightforward. The syntax is

shp2pgsql -s :

Here is an example using real data..

Let's look at the data

gdalsrsinfo nybbwi.prj

As we can see, the projection is NAD_1983_StatePlane_New_York_Long_Island_FIPS_3104_Feet, whose EPSG code is 2263. Since, I would like to load the data, I can use shp2pgsql command to create the load file.

shp2pgsql -d -s 2263 -g geom -e -I nybbwi nystaging.boroughs > $scriptdir/021_boroughs.sql

The next file was in a different projection. Here I am using a different command ogrInfo to get the projection information.

ogrinfo -al -so fatality_yearly.shp

This reveals that projection is latlong, or EPSG code 4326

As explained previously, we can use the -s option to re-project the data. The following is the command

shp2pgsql -d -s 4326:2263 -g geom -e -I fatality_yearly nystaging.fatality > $scriptdir/023_fatality_yearly.sql

shp2pgsql -d -s 4326:2263 -g geom -e -I injury_yearly nystaging.injury > $scriptdir/024_injury_yearly.sql

That's it...

Here is the complete script...

# 04_load_spatial_nyc_data.sh

export datadir=../data/shpfiles
export scriptdir=../../scripts

export curdir=~/Work/Transportation/NYC/scripts
cd $curdir

export PGUSER=nyc
export PGPASSWORD=nyc
export PGDATABASE=nyc


echo $datadir
echo $scriptdir

cd $datadir

gdalsrsinfo nybbwi.prj
# EPSG code for NAD_1983_StatePlane_New_York_Long_Island_FIPS_3104_Feet is 2263
shp2pgsql -d -s 2263 -g geom -e -I nybbwi nystaging.boroughs > $scriptdir/021_boroughs.sql

gdalsrsinfo PedCountLocationsMay2015.prj
ogrinfo -al -so PedCountLocationsMay2015.shp
shp2pgsql -d -s 2263 -g geom -e -I PedCountLocationsMay2015 nystaging.pedcounts > $scriptdir/022_pedcounts.sql

ogrinfo -al -so fatality_yearly.shp
shp2pgsql -d -s 4326:2263 -g geom -e -I fatality_yearly nystaging.fatality > $scriptdir/023_fatality_yearly.sql
shp2pgsql -d -s 4326:2263 -g geom -e -I injury_yearly nystaging.injury > $scriptdir/024_injury_yearly.sql

cd $scriptdir

psql -f 021_boroughs.sql
psql -f 022_pedcounts.sql
psql -f 023_fatality_yearly.sql
psql -f 024_injury_yearly.sql