Skip to content

Instantly share code, notes, and snippets.

@fawcett

fawcett/gnis_processing.txt

Created October 21, 2015 17:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save fawcett/58813c2f50e9ffd00acb to your computer and use it in GitHub Desktop.
Save fawcett/58813c2f50e9ffd00acb to your computer and use it in GitHub Desktop.
Download ZIP
gnis processing and pg copy
Raw
gnis_processing.txt
$ file POP_PLACES_20151001.txt
POP_PLACES_20151001.txt: UTF-8 Unicode (with BOM) text, with CRLF line terminators
######################
dos2unix POP_PLACES_20151001.txt
cat <<EOT> POP_PLACES_20151001.sql
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET datestyle = 'MDY';
SET search_path = test, pg_catalog;
COPY test.gnis_pop_places (feature_id, feature_name, feature_class, state_alpha, state_numeric, county_name, county_numeric, primary_latitude_dms, primary_longitude_dms, primary_latitude_dec, primary_longitude_dec, source_latitude_dms, source_longitude_dms, source_latitude_dec, source_longitude_dec, elevation_meters, elevation_feet, map_name, date_created, date_edited) FROM stdin WITH NULL '' DELIMITER '|';
EOT
tail -n +2 POP_PLACES_20151001.txt >> POP_PLACES_20151001.sql
echo '\.' >> POP_PLACES_20151001.sql
echo '' >> POP_PLACES_20151001.sql
psql -f POP_PLACES_20151001.sql spatialprod
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment