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();
    }
  }
}