Connecting Windographer to Your Database

Databases are powerful. They provide a  central storage location for enormous quantities of data, allowing access by multiple users simultaneously, and offering regular backups and tight control of data integrity. More and more wind power developers and analysts are turning to databases to store their wind resource assessment data. 

If you store wind resource data in SQL Server, MySQL, or PostgreSQL, the enterprise edition of Windographer can connect directly to your database. It can import a dataset from your database, let you analyze and modify that dataset, and then export the changes back to the database, with no need for windog files. You don’t need a custom version to do this; Windographer is flexible enough to talk to any database that supports stored procedures.

Windographer can even import from the database multiple datasets at once. The Import From Database window, shown below, lets you select all the datasets you want to import. It also enables you to enter a search term to narrow down the list in case your database contains hundreds of datasets. When you click Import, Windographer reads the datasets from the database, allowing you to work with that data exactly as if you had imported it from a data file.

Windographer talks to your database by calling stored procedures, which are functions written within the database. It does not directly execute SQL statements on your database because that would require Windographer to know the names of your tables and your fields, and how the tables relate to each other. Instead, it simply calls a set of stored procedures, which perform the querying and format the results into recordsets (simple table structures) that Windographer can read. 

That means you, or someone on your team, must do some work to make this happen. You have to write a few stored procedures that execute the SQL queries appropriate to your table names and data structure. This is standard for database administrators and database programmers, and the Windographer database specifications provide the necessary guidance. 

As an example, say your database stores some dataset properties (like name and period of record) in a table called Datasets, and other properties (like location and time zone) in a table called Projects:

Datasets database table

Projects database table

When Windographer calls the stored procedure to retrieve the list of datasets, your stored procedure will have to execute the appropriate SQL SELECT statement to query the necessary fields from those tables and combine the results into a single recordset of the structure required by the Windographer database specifications, like this:

Recordset returned to Windographer

That recordset presents the data in the form that Windographer expects and omits extraneous information (like landowner and region) that Windographer doesn’t use. The stored procedure has translated your data into a form that Windographer can understand. Other stored procedures do similar work to allow Windographer to read the data column properties, the time series data itself, the flag status of each data point, and so on, and to write that information back to your database in case the user makes modifications.