USGS_GNIS_FIPS_counties.csv.ps1
bdill |
PRO |
11/26/24 04:03:29 AM UTC |
0 ⭐ |
8022 👁️ |
Never ⏰ |
[Script, powershell, howto, usgs, counties]
#================================================================================
# MS SQL Server and PowerShell using dbatools.io module (https://dbatools.io/)
# 1) Download files from USGS and unzip to desired $UnzipPath
# https://www.usgs.gov/us-board-on-geographic-names/download-gnis-data
# https://prd-tnm.s3.amazonaws.com/StagedProducts/GeographicNames/FederalCodes/FedCodes_AllStates_Text.zip
# 2) Load all the txt files into a single SQL table
# 3) Create a view vw_USGS_Counties to select only the counties
# 4) Export the results from the view to USGS_GNIS_FIPS_counties.csv
# results are here: https://pastebin.com/Uk8D9p22
#--------------------------------------------------------------------------------
# Config parameters
$Server = "MyServer"
$Database = "MyDatabase"
$UnzipPath = "C:\opendata\USGS_GNIS\Text\"
#--------------------------------------------------------------------------------
# Create and populate USGS_GNIS table containint Counties, their FIPS, and USGS FeatureID fields
$query_create = @"
CREATE TABLE dbo.USGS_GNIS (
feature_id VARCHAR(10) NOT NULL
, feature_name VARCHAR(120) NULL
, feature_class VARCHAR(50) NULL
, census_code VARCHAR(5) NULL
, census_class_code VARCHAR(2) NULL
, gsa_code VARCHAR(4) NULL
, opm_code VARCHAR(9) NULL
, state_name VARCHAR(100) NULL
, state_numeric VARCHAR(2) NULL
, county_sequence VARCHAR(3) NOT NULL
, county_name VARCHAR(100) NULL
, county_numeric VARCHAR(3) NULL
, date_created DATE NULL
, date_edited DATE NULL
, bgn_type VARCHAR(12) NULL
, bgn_authority VARCHAR(25) NULL
, bgn_date DATE NULL
, prim_lat_dms VARCHAR(10) NULL
, prim_long_dms VARCHAR(10) NULL
, prim_lat_dec DECIMAL(12, 7) NULL
, prim_long_dec DECIMAL(12, 7) NULL
) ON [PRIMARY];
"@
Invoke-DbaQuery -SqlInstance $Server -Database $Database -Query $query_create
#--------------------------------------------------------------------------------
# loop through files: FederalCodes_AK.txt, ... , FederalCodes_WY.txt
Get-ChildItem $UnzipPath | foreach {
#$_.FullName
Import-DbaCsv -Path $_.FullName -SqlInstance $Server -Database $Database -Schema dbo -Table USGS_GNIS -Delimiter "|"
}
#--------------------------------------------------------------------------------
$query_dedupe = @"
; WITH foo AS (
SELECT ROW_NUMBER() OVER (PARTITION BY feature_id, feature_name, feature_class, census_code, census_class_code, gsa_code, opm_code
, state_name, state_numeric, county_sequence, county_name, county_numeric, date_created, date_edited
, bgn_authority, bgn_date, prim_lat_dms, prim_long_dms, prim_lat_dec, prim_long_dec -- ommiting bgn_type
ORDER BY feature_id, bgn_type ) AS RowNum
, *
FROM dbo.USGS_GNIS
)
--SELECT * FROM Foo WHERE foo.RowNum > 1
DELETE FROM Foo WHERE foo.RowNum > 1
"@
Invoke-DbaQuery -SqlInstance $Server -Database $Database -Query $query_dedupe
#--------------------------------------------------------------------------------
$query_create_view = @"
CREATE OR ALTER VIEW dbo.vw_USGS_Counties
AS
SELECT feature_id AS USGS_FeatureID
, census_code AS CensusCode
, state_numeric AS FIPS_State
, county_numeric AS FIPS_County
, CONCAT(state_numeric, county_numeric) AS FIPS_County5
, state_name AS StateName
, county_name AS CountyName
, prim_lat_dec AS Lat
, prim_long_dec AS Long
, feature_name AS FeatureName
FROM dbo.USGS_GNIS
WHERE census_class_code IN ('H1', 'H6')
"@
Invoke-DbaQuery -SqlInstance $Server -Database $Database -Query $query_create_view
#--------------------------------------------------------------------------------
Invoke-DbaQuery -SqlInstance $Server -Database $Database -Query "SELECT * FROM dbo.vw_USGS_Counties ORDER BY FIPS_County5" | Export-Csv -Path "C:\opendata\USGS_GNIS\USGS_GNIS_FIPS_counties.csv" -NoTypeInformation
Comments
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎
0 B
|👍
/👎