#================================================================================
# 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