Creating Delaware Databases

Current Databases

RSMS uses 5 Databases.

1. aspnet-rsms2016-20151214042838
2. RiverBasin
3. SimulationResults
4. Spills
5. USGS_Stations

For now we are going to be keeping the 1st database the same. It has the user athentication.

RiverBasin

RMI Table

LatLong Table

We found a file for lat longs at the !('https://www.nj.gov/drbc/basin/map/GIS.html')[https://www.nj.gov/drbc/basin/map/GIS.html]

These contained shape files for the Delaware River one of which was for each river mile at 1/2 a mile intervals from the convergence of the East and West Delaware into the Delaware river down to the Delaware Bay. We opened the shape file in QGIS and exported a CSV with the Lat Long.

To spot check the Lat Longs and to make sure that the RMI and Lat Longs table are flowing the same way I am going to run a quick check on Google by taking the Lat Longs with river mile and looking at google. The River runs from 330 to 0 in the RSMS RMI table it runs from 330 to 127. 127 should be at Trenton NJ.

River RiverMile Latitude Longitude Delaware River 0 38.8424793 -75.05471307

River RiverMile Latitude Longitude Delaware River 127 40.13653637 -74.73892293

127 is a little past Trenton.

River RiverMile Latitude Longitude Delaware River 330 41.93637406 -75.27869348

Renaming this to RiverBasin_Delaware

USE [RiverBasin]
GO
/****** Object:  Table [dbo].[HEC_RASFilesProcessed]    Script Date: 5/20/2024 3:21:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HEC_RASFilesProcessed](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](255) NULL,
    [ProcessedOn] [datetime2](7) NULL,
    [Success] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[HEC_RASFlow]    Script Date: 5/20/2024 3:21:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HEC_RASFlow](
    [FlowDateTime] [bigint] NOT NULL,
    [RiverMile] [float] NULL,
    [TributaryMile] [float] NULL,
    [Flow] [float] NULL,
    [Area] [float] NULL,
    [UDID] [int] NOT NULL,
    [UpdatedOn] [bigint] NULL,
    [Stage] [float] NULL,
PRIMARY KEY CLUSTERED
(
    [FlowDateTime] ASC,
    [UDID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RiverLatLong]    Script Date: 5/20/2024 3:21:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RiverLatLong](
    [ID] [int] NOT NULL,
    [River] [nvarchar](255) NULL,
    [RiverMile] [float] NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RivermileIndex]    Script Date: 5/20/2024 3:21:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RivermileIndex](
    [ID] [int] NOT NULL,
    [MRM] [float] NULL,
    [TRM] [float] NULL,
    [UDIntCode] [int] NULL,
    [River] [nvarchar](255) NULL,
    [Reach] [nvarchar](255) NULL,
    [NearestRiverStation] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RiversXref]    Script Date: 5/20/2024 3:21:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RiversXref](
    [USGSRiverCode] [int] NOT NULL,
    [USGSRiverDescription] [varchar](64) NULL,
    [AllRiversName] [varchar](64) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [USGSRiverCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RSMSRiver]    Script Date: 5/20/2024 3:21:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RSMSRiver](
    [River] [varchar](75) NOT NULL,
    [IENCRiverCode] [varchar](2) NULL,
 CONSTRAINT [PK_RSMSRiver] PRIMARY KEY CLUSTERED
(
    [River] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SimulationResults

Renaming this to SimulationResults_Delaware

USE [SimulationResults]
GO
/****** Object:  Table [dbo].[CTPLTResults]    Script Date: 5/20/2024 3:21:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CTPLTResults](
    [ResultsID] [int] NULL,
    [Miles] [float] NULL,
    [Hours] [float] NULL,
    [Concentration] [float] NULL
) ON [PRIMARY]
GO
/****** Object:  UserDefinedFunction [dbo].[PeakConcDistance]    Script Date: 5/20/2024 3:21:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[PeakConcDistance](@ResultsID int, @ConTol float)
RETURNS TABLE
AS
RETURN
    SELECT Miles as PeakDistance, max(Concentration) as PeakConc
    FROM CTPLTResults
    WHERE ResultsID = @ResultsID and Concentration >= @ConTol group by Miles
GO
/****** Object:  Table [dbo].[CXPLTResults]    Script Date: 5/20/2024 3:21:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CXPLTResults](
    [ResultsID] [int] NULL,
    [Miles] [float] NULL,
    [Hours] [float] NULL,
    [Concentration] [float] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[INF]    Script Date: 5/20/2024 3:21:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[INF](
    [ResultsID] [int] NULL,
    [hour] [float] NULL,
    [xPeak] [float] NULL,
    [peak] [float] NULL,
    [xtrail] [float] NULL,
    [trail] [float] NULL,
    [xlead] [float] NULL,
    [lead] [float] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MassBalance]    Script Date: 5/20/2024 3:21:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MassBalance](
    [ResultsID] [int] NULL,
    [Hours] [float] NULL,
    [PMASS] [float] NULL,
    [DZMass] [float] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Runs]    Script Date: 5/20/2024 3:21:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Runs](
    [ResultsID] [int] IDENTITY(1,1) NOT NULL,
    [RunDateTime] [datetime2](7) NULL,
    [SpillID] [int] NULL,
    [SpillDesc] [nvarchar](255) NULL,
    [SpillAuthor] [nvarchar](255) NULL,
    [SpillRiver] [nvarchar](255) NULL,
    [SpillRiverMile] [float] NULL,
    [SpillQuantity] [float] NULL,
    [SpillStartDateTime] [datetime2](7) NULL,
    [SpillDuration] [float] NULL,
    [SpillTimeZone] [nvarchar](255) NULL,
    [SimParamsID] [int] NULL,
    [SimParamsDesc] [nvarchar](255) NULL,
    [SimParamsSegments] [int] NULL,
    [SimParamsFlowTolerance] [float] NULL,
    [SimParamsConcentrationtolerance] [float] NULL,
    [SimParamsMinimumVelocity] [float] NULL,
    [SimParamsDispersion] [float] NULL,
    [SimParamsLeadingEdgeParameter] [float] NULL,
    [SimParamsTrailingEdgeParameter] [float] NULL,
    [SimParamsGlobalFlowMultiplier] [float] NULL,
    [SimParamsTimeStepLength] [float] NULL,
    [SimParamsReportingTimestepLength] [float] NULL,
    [SimParamsDecayRate] [float] NULL,
    [SimParamsSimulationDuration] [int] NULL,
    [DeadZoneAverageVelocity] [float] NULL,
    [DeadZoneExchangeRate] [float] NULL,
    [DeadZoneFlowAreaMultiplier] [float] NULL,
    [FirstTimeStep] [float] NULL,
    [LastTimeStep] [float] NULL,
    [LastMile] [float] NULL,
    [MaxConcentration] [float] NULL,
    [SpillLocationMainStem] [float] NULL,
    [SpillLocationTributary] [float] NULL
) ON [PRIMARY]
GO

Spills

Renaming this to Spills_Delaware

USE [Spills]
GO
/****** Object:  Table [dbo].[InitConc]    Script Date: 5/20/2024 2:48:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InitConc](
    [InitConcID] [int] IDENTITY(1,1) NOT NULL,
    [Values] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED
(
    [InitConcID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[SimParams]    Script Date: 5/20/2024 2:48:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SimParams](
    [SimParamsID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](255) NULL,
    [Segments] [int] NULL,
    [FlowTol] [float] NULL,
    [ConcTol] [float] NULL,
    [MinVelocity] [float] NULL,
    [DispersionFactor] [float] NULL,
    [LeadingEdgeParam] [float] NULL,
    [TrailingEdgeParam] [float] NULL,
    [FlowMultiplier] [float] NULL,
    [Timestep] [float] NULL,
    [OutputStep] [float] NULL,
    [DecayRate] [float] NULL,
    [SimDuration] [int] NULL,
    [DZ_AverageVelocityMainStem] [float] NULL,
    [DZ_ExchangeRate] [float] NULL,
    [DZ_FlowAreaMultiplier] [float] NULL,
PRIMARY KEY CLUSTERED
(
    [SimParamsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Spill]    Script Date: 5/20/2024 2:48:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Spill](
    [SpillID] [int] IDENTITY(1,1) NOT NULL,
    [SpillDesc] [nvarchar](255) NULL,
    [Author] [nvarchar](255) NULL,
    [Date] [datetime2](7) NULL,
    [InitConcID] [int] NULL,
    [Quantity] [float] NULL,
    [Start] [datetime2](7) NULL,
    [Duration] [float] NULL,
    [TimeZone] [nvarchar](255) NULL,
    [RiverSelected] [nvarchar](255) NULL,
    [ORSANCORiverMile] [float] NULL,
PRIMARY KEY CLUSTERED
(
    [SpillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USGS_Stations

Renaming this to USGS_Stations_Delaware

USE [USGS_Stations]
GO
/****** Object:  Table [dbo].[Regions]    Script Date: 5/20/2024 3:22:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Regions](
    [AccountingUnitsCode] [int] NOT NULL,
    [AccountingUnitsName] [varchar](64) NULL,
PRIMARY KEY CLUSTERED
(
    [AccountingUnitsCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Rivers]    Script Date: 5/20/2024 3:22:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rivers](
    [CatalogingUnitsCode] [int] NOT NULL,
    [CatalogingUnitsName] [varchar](64) NULL,
    [States] [varchar](16) NULL,
    [AccountingUnitsCode] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [CatalogingUnitsCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Stations]    Script Date: 5/20/2024 3:22:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stations](
    [SiteCode] [bigint] NOT NULL,
    [SiteName] [varchar](64) NULL,
    [latitude] [float] NULL,
    [longitude] [float] NULL,
    [HydrologicUnitCode] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [SiteCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rivers]  WITH NOCHECK ADD FOREIGN KEY([AccountingUnitsCode])
REFERENCES [dbo].[Regions] ([AccountingUnitsCode])
GO
ALTER TABLE [dbo].[Stations]  WITH NOCHECK ADD FOREIGN KEY([HydrologicUnitCode])
REFERENCES [dbo].[Rivers] ([CatalogingUnitsCode])
GO