importing an ESRI shapefile to PostGIS

I don’t work with shapefiles and PostGIS enough to remember how to import a shapefile into a table. I want to import the NHDPlus dataset for rivers. There’s lots of ways to do this, here’s what’s working now in a plodding, step by step way.

  1. createdb -T template_postgis2 nhd
  2. shp2pgsql -W LATIN1 NHDFlowline.shp >| /tmp/o
  3. psql nhd < /tmp/o
  4. echo ‘CREATE INDEX “nhdflowline_geom_gist” ON “nhdflowline” USING GIST (“geom”);’ | psql nhd

This can probably be done on one line with shp2pgsql -I foo.shp | psql nhd

Getting fancier on the import (and setting the SRID to 4269):

  1. shp2pgsql -s 4269 -I -D -W LATIN1 NHDFlowline.shp | psql nhd

Related: shp2pgsql won’t look at the .prj file to figure out the projection. Figuring out a projection is still way too hard and Prj2EPSG.org failed me. This StackOverflow question and this one have a neat trick; use osgeo.osr.SpatialReference().AutoIdentifyEPSG() to guess the projection.

Update: Seth F has a nice shell script for importing a lot of useful NHD data and creating important indices.