Skip to content

Instantly share code, notes, and snippets.

@fawcett

fawcett/gnis_populated_places.sql

Created October 21, 2015 16:15
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/18c33cf4d12a6a620144 to your computer and use it in GitHub Desktop.
Save fawcett/18c33cf4d12a6a620144 to your computer and use it in GitHub Desktop.
Download ZIP
DDL to create a table in Postgres that can be used to accept the data from a csv file downloaded from GNIS.
Raw
gnis_populated_places.sql
-- DROP TABLE test.gnis_pop_places;
CREATE TABLE test.gnis_pop_places
(
feature_id character varying(10),
feature_name character varying(120),
feature_class character varying(50),
state_alpha character varying(2),
state_numeric numeric(2,0),
county_name character varying(100),
county_numeric numeric(3,0),
primary_latitude_dms character varying(7),
primary_longitude_dms character varying(8),
primary_latitude_dec numeric(11,7),
primary_longitude_dec numeric(12,7),
source_latitude_dms character varying(7),
source_longitude_dms character varying(8),
source_latitude_dec numeric(11,7),
source_longitude_dec numeric(12,7),
elevation_meters numeric(5,0),
elevation_feet numeric(6,0),
map_name character varying(100),
date_created date,
date_edited date
)
WITH (
OIDS=FALSE
);
ALTER TABLE test.gnis_pop_places
OWNER TO dfawcett;
COMMENT ON TABLE test.gnis_pop_places IS 'A working table to process some GNIS data.';
COMMENT ON COLUMN test.gnis_pop_places.feature_id IS 'Permanent, unique feature record identifier and official feature name as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone. ';
COMMENT ON COLUMN test.gnis_pop_places.feature_class IS 'GNIS Classification of the feature e.g. (airport, basin, canal, etc.';
COMMENT ON COLUMN test.gnis_pop_places.state_alpha IS 'The unique two letter alphabetic code for a US State as specified in INCITS 38:200x, (Formerly FIPS 5-2) Codes for the Identification of the States, the District of Columbia, Puerto Rico, and the Insular Areas of the United States. ';
COMMENT ON COLUMN test.gnis_pop_places.state_numeric IS 'The unique two number code for a US State as specified in INCITS 38:200x, (Formerly FIPS 5-2) Codes for the Identification of the States, the District of Columbia, Puerto Rico, and the Insular Areas of the United States. ';
COMMENT ON COLUMN test.gnis_pop_places.county_name IS 'The name for a county or county equivalent as specified in INCITS 31:200x, (Formerly FIPS 6-4) Codes for the Identification of Counties and Equivalent Entities of the United States, its Possessions, and Insular Areas';
COMMENT ON COLUMN test.gnis_pop_places.county_numeric IS 'The unique three number code for a county or county equivalent as specified in INCITS 31:200x, (Formerly FIPS 6-4) Codes for the Identification of Counties and Equivalent Entities of the United States, its Possessions, and Insular Areas';
COMMENT ON COLUMN test.gnis_pop_places.primary_latitude_dms IS 'The official feature location as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone. NAD 83,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.primary_longitude_dms IS 'The official feature location as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone. NAD 83,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.primary_latitude_dec IS 'The official feature location as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone. NAD 83,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.primary_longitude_dec IS 'The official feature location as defined in INCITS 446-2008, Identifying Attributes for Named Physical and Cultural Geographic Features (Except Roads and Highways) of the United States, Its Territories, Outlying Areas, and Freely Associated Areas, and the Waters of the Same to the Limit of the Twelve-Mile Statutory Zone. NAD 83,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.source_latitude_dms IS 'Source coordinates of linear feature only (Class = Stream, Valley, Arroyo), NAD 83 ,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.source_longitude_dms IS 'Source coordinates of linear feature only (Class = Stream, Valley, Arroyo), NAD 83 ,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.source_latitude_dec IS 'Source coordinates of linear feature only (Class = Stream, Valley, Arroyo), NAD 83 ,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.source_longitude_dec IS 'Source coordinates of linear feature only (Class = Stream, Valley, Arroyo), NAD 83 ,DMS-degrees/minutes/seconds,DEC-decimal degrees. ';
COMMENT ON COLUMN test.gnis_pop_places.elevation_meters IS 'Elevation in meters above (-below) sea level of the surface at the primary coordinates from National Elevation Dataset';
COMMENT ON COLUMN test.gnis_pop_places.elevation_feet IS 'Elevation in feet above (-below) sea level of the surface at the primary coordinates from National Elevation Dataset';
COMMENT ON COLUMN test.gnis_pop_places.map_name IS 'Name of USGS base series topographic map containing the primary coordinates.';
COMMENT ON COLUMN test.gnis_pop_places.date_created IS 'The date the feature was initially committed to the database.';
COMMENT ON COLUMN test.gnis_pop_places.date_edited IS 'The date any attribute of an existing feature was last edited.';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment