Pisces Programming

Writing code to manage a Pisces Database

All the operations that you perform using the Pisces user interface can be done with .net code. For managing a large number of series -- writing code is the way to go. The examples here will be in C#.

Programming Setup

Pisces programming will reference two assemblies: Reclamation.Core.dll and Reclamation.TimeSeries.dll

Connect to a Database

The database connection is specific to your favorite database. But don't worry -- the rest of your code is the same regardless of what database you use. Pisces is setup to work with mysql, postgresql, sqlite, or sqlserver. Below are examples for each database.

Sqlite http://www.sqlite.org (Pisces default for working on a local file based database)

    var filename = @"c:\data\mydb.pdb";
    var server = SQLiteServer(filename);

Postgresql http://postgresql.org

    var server = PostgreSQL.GetPostgresServer("mydatabase","myserver" ); 

mysql https://mariadb.org/

    var dbname = ConfigurationManager.AppSettings["MySqlDatabase"];
    var server = ConfigurationManager.AppSettings["MySqlServer"];
    var user = ConfigurationManager.AppSettings["MySqlUser"];
    var server = MySqlServer.GetMySqlServer(server, dbname, user);

Create and populate your TimeSeriesDatabase with Reclamation and USGS data

Create an SqlLite Pisces Database

    var filename = @"c:\Temp\mySqlLiteDb.pdb"; // Define a Pisces database filename using a valid directory location
    var server= new SQLiteServer(filename); // Create the Pisces database
    var db = new TimeSeriesDatabase(server); // Set a variable to invoke for working with the database

Query and add Grand Coulee (GCL) dam water level (FB) elevations from 7 days ago to today. Available Site and Parameter codes are available at this link: http://www.usbr.gov/pn/hydromet/arcread.html

    var sHydromet = new Reclamation.TimeSeries.Hydromet.HydrometDailySeries("GCL", "FB"); // Define query parameters
    sHydromet.Read(DateTime.Now.AddDays(-7), DateTime.Now); // Query data for the specified period
    db.AddSeries(sHydromet); // Add data to the Pisces database

Query and add Colorado River flows at the Lees Ferry gage from 7 days ago to today. Available Site codes are available via USGS NWIS at this link: http://waterdata.usgs.gov/nwis/rt

    var sUSGS = new Reclamation.TimeSeries.Usgs.UsgsDailyValueSeries("09380000", Reclamation.TimeSeries.Usgs.UsgsDailyParameter.DailyMeanDischarge); // Define query parameters
    sUSGS.Read(DateTime.Now.AddDays(-7), DateTime.Now); // Query data for the specified period
    db.AddSeries(sUSGS); // Add data to the Pisces database

Manage your TimeSeriesDatabase

Once you have the server variable you begin working with the timeseries database by creating an object like this.

     var db = new TimeSeriesDatabase(server);

This object called 'db' can be used for many tasks:

      // Get a list of series and sites in the database
         var seriesCatalog = db.GetSeriesCatalog();
         var siteCatalog = db.GetSiteCatalog();     
  
          Console.WriteLine("Series in Catalog: "+GetSeriesCatalog().Count());
          Console.WriteLine("Sites in SiteCatalog: " + GetSiteCatalog().Count());

     // get a reference to a series and compute the daily minimum and maximum (one value per day)
         s = db.GetSeriesFromName("AmazonRiverFlow");
         Series max = Math.DailyMax(s);
         Series min = Math.DailyMin(s);

Example of setting Series Properties on specific series

The example below creates or updates a property called 'WholeWaterYear' on any series that has a name with the pattern (underscore PU) "_pu". In this database pu is cumulative precipitation. By setting this property calculations will always go back to October 1 -- the beginning of the water year.

// set WholeWaterYear on all PU series
            var svr = PostgreSQL.GetPostgresServer("timeseries", "server1");
            var db = new TimeSeriesDatabase(svr, Reclamation.TimeSeries.Parser.LookupOption.TableName);
            var prop = db.GetSeriesProperties();

            var sc = db.GetSeriesCatalog("name like '%\\_pu'");

            foreach (var item in sc)
            {
                System.Console.WriteLine(item.Name);
                SeriesProperties p = new SeriesProperties(item.id, db);
                p.Set("WholeWaterYear", "True");
                p.Save();
            }

Here are some complete examples that create Pisces databases on the hard drive. This csv file is used in one example.

Example	Description
TestPiscesProgrammingEasy	simple example that minimize coding effort -- not super fast.
TestPiscesProgrammingFast	High performance database management. However, this takes a little more programming

These examples create the following database.