Skip to content

Instantly share code, notes, and snippets.

@andest01

andest01/NhdPlusHrImport.cs

Created April 13, 2021 17:18
Show Gist options
  • Save andest01/cf50aa198fa14397608e953c5f930da7 to your computer and use it in GitHub Desktop.
Save andest01/cf50aa198fa14397608e953c5f930da7 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
NhdPlusHrImport.cs
Skip to content
Search or jump to…
Pull requests
Issues
Marketplace
Explore
@andest01
troutspotr
/
troutspotr-backend
Private
0
00
Code
Issues
3
Pull requests
Actions
Projects
Wiki
Security
Insights
Settings
troutspotr-backend/TroutStreamMangler/NHDImporter/NhdPlusHrImport.cs /
@andest01
andest01 new version?
Latest commit 612d27b on Jan 19, 2019
History
1 contributor
352 lines (307 sloc) 13.5 KB
using System;
using System.IO;
using System.Linq;
using TroutDash.DatabaseImporter.Convention;
using TroutDash.DatabaseImporter.Convention.DatabaseImporter;
namespace NHDImporter
{
public static class NhdPlusHrImport
{
public static readonly string FlowlineName = "NHDFlowline";
public static readonly string ValueAddedAttributesName = "NHDPlusFlowlineVAA";
public static readonly string EPROMName = "NHDPlusEROMMA";
public static readonly string StrahlerOrderTablePrefix = "cleaned_streams_order_";
public static readonly int[] DesiredStrahlerOrders = { 2, 3, 4 };
public static readonly string StreamOrderColumnName = "streamorde";
public static readonly string DivergenceColumnName = "divergence";
public const string NhdPlusHrLinkId = "nhdplusid";
public const string NhdPlusLinkId = "permanent_identifier";
private const string DropDatabaseTemplate = @"DROP DATABASE IF EXISTS ""{0}"";";
private const string CreateDatabaseTemplate =
@"CREATE DATABASE ""{0}"" WITH OWNER = {1} ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1";
private const string AlterNewTableTemplate =
@"ALTER DATABASE ""{0}"" SET search_path = '$user', public, topology, tiger;";
private const string AddPostGisExtension = @"CREATE EXTENSION postgis;";
// private const string AddPostGisTopologyExtension = @"CREATE EXTENSION postgsis_topology;";
private const string AddPostGisFuzzyStrMatchExtension = @"CREATE EXTENSION fuzzystrmatch;";
private const string AddPostGisTigerGeocoderExtension = @"CREATE EXTENSION postgis_tiger_geocoder;";
public const string gdbTemplate = @"ogr2ogr -overwrite -f ""PostgreSQL"" PG:""host={0} port={3} dbname={1} user={2}"" ""{4}"" ""{5}"" -nlt CONVERT_TO_LINEAR -progress --config PG_USE_COPY YES -t_srs ""EPSG:4326"" -sql ""{7}"" -nln ""{6}""";
public const string dbfTemplate = @"ogr2ogr -overwrite -f ""PostgreSQL"" PG:""host={0} port={3} dbname={1} user={2}"" ""{4}"" -nlt CONVERT_TO_LINEAR -progress --config PG_USE_COPY YES -sql ""{6}"" -nln ""{5}""";
public const string shpTemplate = @"ogr2ogr -{8} -f ""PostgreSQL"" PG:""host={0} port={3} dbname={1} user={2}"" ""{4}"" -nlt {7} -progress --config PG_USE_COPY YES -t_srs ""EPSG:4326"" -sql ""{6}"" -nln ""{5}""";
private const string _uniqueConstraintVaa = @"ALTER TABLE public.{0}
ADD CONSTRAINT {0}_unique_constraint_{1} UNIQUE({1});";
private const string _createIndexOrderIndexOnPublicUsingBtree = @"CREATE INDEX {0}_order_index
ON public.{0}
USING btree
({1});";
public static void CreateZeDatabase(IDatabaseConnection dbConnection)
{
Console.WriteLine("Creating database...");
var createScript = String.Format(CreateDatabaseTemplate, dbConnection.DatabaseName, dbConnection.UserName);
var createCommand = String.Format(@"psql -q --host={0} --port={3} --username={1} -d postgres --command ""{2}""",
dbConnection.HostName, dbConnection.UserName, createScript, dbConnection.Port);
ExecuteShellCommand.ExecuteProcess(createCommand);
Console.WriteLine("Done creating database {0}", dbConnection.DatabaseName);
}
public static void CreateDatabase(IDatabaseConnection connection)
{
Console.WriteLine("Attempting to delete database {0}", connection.DatabaseName);
DropDatabase(connection);
CreateZeDatabase(connection);
var alterTableScript = String.Format(AlterNewTableTemplate, connection.DatabaseName);
var alterCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""",
connection.HostName,
connection.UserName, connection.DatabaseName, alterTableScript);
ExecuteShellCommand.ExecuteProcess(alterCommand);
try
{
AddPostGisExtension.ExecuteNonQuery(connection);
AddPostGisFuzzyStrMatchExtension.ExecuteNonQuery(connection);
AddPostGisTigerGeocoderExtension.ExecuteNonQuery(connection);
} catch(Exception) {
}
}
public static void DropDatabase(IDatabaseConnection connection)
{
Console.WriteLine("Dropping database...");
try
{
var dropCommand = String.Format(DropDatabaseTemplate, connection.DatabaseName);
var dropScript = String.Format(@"psql -q --host={1} --port={3} --username={2} -d postgres --command ""{0}""",
dropCommand, connection.HostName, connection.UserName, connection.Port);
ExecuteShellCommand.ExecuteProcess(dropScript);
}
catch (Exception)
{
Console.WriteLine("Couldn't drop database... moving on.");
}
}
public static string GetFlowlineTableName(string watershed)
{
return (watershed + "_" + FlowlineName).ToLower();
}
public static string GetValueAddedTableName(string watershed)
{
return (watershed + "_" + ValueAddedAttributesName).ToLower();
}
public static string GetEpromTableName(string watershed)
{
return (watershed + "_" + EPROMName).ToLower();
}
public static string GetSlopeTableName(string watershed)
{
return (watershed + "_" + "slope").ToLower();
}
public static void ImportNhdPlusHdGdb(DirectoryInfo targetDirectory, string watershedName, IDatabaseConnection connection,
bool overwrite = true)
{
var isLikelyGdbFile = targetDirectory.FullName.IndexOf(".gdb") >= 0;
if (isLikelyGdbFile == false)
{
Console.WriteLine("Hmmmm... you sure this is a ESRI gdb?");
}
var flowlineNewTableName = GetFlowlineTableName(watershedName); //(watershedName + "_" + FlowlineName).ToLower();
var valueAddedAttributesNewTableName = GetValueAddedTableName(watershedName);
var epromNewName = GetEpromTableName(watershedName);
var flowlineSql = String.Format(
"select cast(gnis_id as integer), gnis_name, reachcode, permanent_identifier, cast(lengthkm as float), ftype, fcode, cast(nhdplusid as bigint), vpuid from {0}",
FlowlineName
);
var vaaSql = String.Format(
"select cast(nhdplusid as bigint), streamleve, streamorde, streamcalc, divergence, slope, slopelenkm, elevfixed, cast(totdasqkm as float), vpuid from {0}",
ValueAddedAttributesName
);
var epromSql = String.Format("select cast(nhdplusid as bigint), cast(qcma as float) as qc, cast(qema as float) as qe, cast(vcma as float) as vc, cast(vema as float) as ve, vpuid from {0}", EPROMName);
ImportGdb(targetDirectory, connection, FlowlineName, flowlineNewTableName, flowlineSql);
ImportGdb(targetDirectory, connection, ValueAddedAttributesName, valueAddedAttributesNewTableName, vaaSql);
ImportGdb(targetDirectory, connection, EPROMName, epromNewName, epromSql);
}
public static void ImportGdb(DirectoryInfo targetDirectory, IDatabaseConnection connection,
string fromGdbTableName,
string toPostgresTableName,
string valueAddedAttributesName)
{
var flowlineCommand = String.Format(gdbTemplate,
connection.HostName,
connection.DatabaseName,
connection.UserName,
connection.Port,
targetDirectory.FullName,
fromGdbTableName,
toPostgresTableName,
valueAddedAttributesName);
ExecuteShellCommand.ExecuteProcess(flowlineCommand, targetDirectory);
}
public static void ImportDbf(DirectoryInfo targetDirectory, FileInfo file,
IDatabaseConnection connection, string toPostgresTableName,
string sql)
{
var flowlineCommand = String.Format(dbfTemplate,
connection.HostName,
connection.DatabaseName,
connection.UserName,
connection.Port,
file.FullName,
toPostgresTableName,
sql);
ExecuteShellCommand.ExecuteProcess(flowlineCommand, targetDirectory);
}
public static void ImportShp(DirectoryInfo targetDirectory, FileInfo file, IDatabaseConnection connection, string toPostgresTableName,
string sql = null,
string geomType = "LINESTRING",
string appendOrOverwrite = "append")
{
var flowlineCommand = String.Format(shpTemplate,
connection.HostName,
connection.DatabaseName,
connection.UserName,
connection.Port,
file.FullName,
toPostgresTableName,
sql,
geomType,
appendOrOverwrite);
ExecuteShellCommand.ExecuteProcess(flowlineCommand, targetDirectory);
}
public static void UpdateIndexes(string watershedName, IDatabaseConnection connection,
string flowlineVaaLinkColumnName = NhdPlusHrLinkId,
string vaaIdentifierName = NhdPlusHrLinkId)
{
var flowlineTableName = GetFlowlineTableName(watershedName);
var vaaTableName = GetValueAddedTableName(watershedName);
String.Format(_uniqueConstraintVaa, vaaTableName, vaaIdentifierName).ExecuteNonQuery(connection);
var uniqueConstraintFlowline = @"ALTER TABLE public.{0}
ADD CONSTRAINT {0}_uc_{1} UNIQUE ({1});";
String.Format(uniqueConstraintFlowline, flowlineTableName, flowlineVaaLinkColumnName).ExecuteNonQuery(connection);
// GNIS ID
String.Format(@"CREATE INDEX {0}_gnis_id_index
ON public.{0}
USING btree
(gnis_id);", flowlineTableName).ExecuteNonQuery(connection);
String.Format(_createIndexOrderIndexOnPublicUsingBtree, vaaTableName, StreamOrderColumnName).ExecuteNonQuery(connection);
String.Format(@"CREATE INDEX {0}_divergence_index
ON public.{0}
USING btree
({1});", vaaTableName, DivergenceColumnName).ExecuteNonQuery(connection);
String.Format(@"CREATE INDEX {0}_fcode_idx
ON public.{0}
USING btree
(fcode);", flowlineTableName).ExecuteNonQuery(connection);
}
public static void CreateDerivedTables(string watershedName, IDatabaseConnection connection,
string flowlineLinkId,
string vaaLinkId)
{
var flowlineTableName = GetFlowlineTableName(watershedName);
var vaaTableName = GetValueAddedTableName(watershedName);
// Delete old table
var queryTemplate =
@"
INSERT INTO {1}(
gnis_id,
gnis_name,
streamorder,
lengthkm,
canallengthkm,
intermittentlengthkm,
pipelinelengthkm,
pereniallengthkm,
max_strahler_order,
min_strahler_order,
numgeom,
geomtype,
watershed,
geom
)
SELECT
gnis_id,
gnis_name,
{0} as streamorder,
sum({2}.lengthkm) as lengthkm,
sum(case when ftype = 336 then {2}.lengthkm else 0 end) as canallengthkm,
sum(case when {2}.fcode = 46003 then {2}.lengthkm else 0 end) as intermittentlengthkm,
sum(case when ftype = 428 then {2}.lengthkm else 0 end) as pipelinelengthkm,
sum(case when {2}.fcode = 46006 then {2}.lengthkm else 0 end) as pereniallengthkm,
max({3}.{6}) as max_strahler_order,
min({3}.{6}) as min_strahler_order,
ST_NumGeometries(ST_LineMerge(ST_Union(wkb_geometry))) as numgeom,
ST_GeometryType(ST_LineMerge(ST_Union(wkb_geometry))) as geomType,
'{4}' as watershed,
ST_Multi(ST_LineMerge(ST_Union(ST_Force2D(wkb_geometry)))) as geom
FROM PUBLIC.{2}
INNER JOIN {3}
ON {2}.{5} = {3}.{8}
WHERE gnis_id IS NOT NULL
AND gnis_id > 0
AND {6} >= {0}
AND {3}.{7} != 2
GROUP BY gnis_name, gnis_id";
DesiredStrahlerOrders
.AsParallel()
.ForAll(order =>
{
var tableName = "cleaned_streams_order_" + order;
var query = String.Format(
queryTemplate,
order,
tableName,
flowlineTableName,
vaaTableName,
watershedName,
flowlineLinkId,
StreamOrderColumnName,
DivergenceColumnName,
vaaLinkId);
query.ExecuteNonQuery(connection);
});
}
public static void TruncateTables(IDatabaseConnection connection)
{
DesiredStrahlerOrders.ToList()
.ForEach(order =>
{
var tableName = StrahlerOrderTablePrefix + order;
String.Format("TRUNCATE {0} CASCADE;", tableName).ExecuteNonQuery(connection);
});
}
public static void MakeStrahlerTable(int order, IDatabaseConnection connection)
{
var tableName = StrahlerOrderTablePrefix + order;
String.Format(@"DROP TABLE IF EXISTS public.{0} CASCADE", tableName).ExecuteNonQuery(connection);
String.Format(@"DROP SEQUENCE IF EXISTS public.{0}_gid_seq", tableName).ExecuteNonQuery(connection);
String.Format(@"CREATE SEQUENCE public.{0}_gid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 135714
CACHE 1;", tableName).ExecuteNonQuery(connection);
String.Format(@"CREATE TABLE public.{0}
(
gid integer NOT NULL DEFAULT nextval('{0}_gid_seq'::regclass),
gnis_id character varying(10),
gnis_name character varying(65),
streamorder integer,
lengthkm double precision,
canallengthkm double precision,
intermittentlengthkm double precision,
pipelinelengthkm double precision,
pereniallengthkm double precision,
max_strahler_order integer not null,
min_strahler_order integer not null,
numgeom integer,
geomtype text,
watershed text,
geom geometry(MultiLineString,4326)
)
WITH (
OIDS=FALSE
);", tableName).ExecuteNonQuery(connection);
String.Format(@"ALTER TABLE public.{0}
OWNER TO postgres;", tableName).ExecuteNonQuery(connection);
}
public static void ExportTable(string dbName, string tableName, string targetDbName, string targetTableName)
{
throw new Exception();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment