Relational database structures for structural monitoring data

(1)

Relational database structures for structural monitoring data

Daniele Inaudi, Samuel Vurpillot

SMARTEC SA

Via al Molino 6,CH-6916 GRANCIA, Switzerland Tel: +41 91 993 09 24, Fax: +41 91 993 09 40 email: [email protected], Web: www.smartec.ch

ABSTRACT

The permanent or regular monitoring of structures with sensors of any type can generate a

consistent volume of data. Furthermore, it is often necessary to store additional information that is useful for the analysis of the measurements. Our experience has shown that the use of relational database structures can greatly simplify the handling of this large data- flow. With as appropriate data structure, the measurement data and other related information on the monitoring ne twork, the structure and its environment can be organized in a single file that will follow the structure's life in the years.

This paper describes the data-structure that has been developed in the framework of the SOFO project. This database is capable of storing data from multiple types of sensors. It is now used at the unique interface between the data-acquisition (DAQ) software packages (for the different sensor types) and the data-representation and analysis packages (DRA). This approach has the advantage that the addition of new sensor types does generally not require changes in the DRA software and improvements in the DRA software are immediately applicable to all sensor types. The database structure is open, and any DAQ or DRA package can add fields and tables that are specific to its needs.

In our experience, the use of relational database structures also greatly simplifies the quality management and can help in the certification of monitoring systems.

1. INTRODUCTION

The installation of a permanent monitoring system in a structure brings with it the necessity of managing a large volume of data. Even in a structure with only a few tens of sensors that are measured every hour, the number of data points that are generated can become so large that a manual analysis becomes tedious and very time-consuming. Furthermore, the data acquisition phase is only the first step in a chain of operations that ultimately lead to high- level information useful for the structure’s owner. These analysis steps are more-and- more based on the use of software tools that require access to the raw data. This leads to the need for a structured data-storage system that can be accessed by the data acquisition software (DAQ) and by the data representation and analysis packages (DRA).

The natural choice for storing data is of course a database. In the last 5 years we have developed and deployed a database structure that proved its usefulness and reliability in more than 100

applications. This relational database is called SDB (for SOFO Data Base) and allows the storage of data from many different types of structures. Furthermore, the structure was designed to be open, so that it is possible to add fields and tables that might be needed for a particular sensor system. Since many research groups and companies worldwide are currently developing DAQ and DRA applications, we feel that a standard core database structure could help making these tools more inter-operable. For this reason we propose the core SDB structure as an open standard for storing structural monitoring data.

(2)

2. DATABASE STRUCTURE

As the DB suffix implies, SOFO DB works using the database concept.

All data relative to a given Project are stored into a single file called a SOFO database and with a *.SDB file extension. A Project can include all the measurements of a set of Sensors installed in a structure or in a group of correlated structures. The organized database structure permits a rational storage of all measurements and other relevant information. Having all the data in a single and well-structured file reduces the possibility of accidentally loosing some of the data or mixing data from different Projects or Sensors.

SOFO DB is based on Microsoft™ Jet Engine and accesses it only through standard SQL queries. This means that it can be easily scaled up to other SQL servers (like MS SQL Server or Oracle) if necessary.

SOFO DB is organized in different tables as resumed in the following figure:

Figure 1. SDB Database schematic organization

Let's now analyse the different tables and data.

Projects

The Project table contains all information relative to a Project: its creation date, its name and description.

The Project table should be called “Project” and contain at least the following fields:

Name Type Description:

ProjectID Long Integer Counter Identifies the project in multi-project DBs ProjectName Text, 30 characters Project name

(3)

DBVersion Text, 30 characters Defines the revision of the DB structure (currently "2.0")

CreateDate Date/Time Creation date and time

Sensors

The Sensor table contains the information relative to a given Sensor. The Sensor properties include the Sensor name and description, its connection properties as well as the definition of the associated sub sensors.

Sub-sensors are defined as sensors that are read in a single operation; for example the X, Y and Z displacement of a GPS sensor would be three sub-sensors of a single GPS sensor. In general a sensor will provide a "primary" measurement (for example a resistance) the is successively transformed in a more useful "secondary" unit (for example a temperature).

The core- fields of the sensor table (called “Sensor”) are:

SensorID Long Integer Counter Unique identifier of the sensor ProjectID Long Integer Link to the Project table

SensorType Integer Defines the type of sensor (the list of Sensor types is maintained by SMARTEC SA)

SensorName Text, 30 characters Sensor name

Description Memo Sensor description, freeform

The core- fields of the sub-sensor table (called “PeakDef”) are:

PeakDefID Long Integer Counter Unique identifier of the sub-sensor SensorID Long Integer Link to the Sensor table

PeakDefName Text, 20 characters Sub-sensor name

Unit Text, 8 characters The unit of the measured quantity

Coef0 Double Constant coefficient for secondary unit calculation Coef1 Doub le Linear coefficient for secondary unit calculation Coef2 Double Quadratic coefficient for secondary unit calculation Coef3 Double Cubic coefficient for secondary unit calculation

The secondary unit is calculated with the following formula: 3

3 2

1

0 Coef PU Coef PU2 Coef PU Coef

SU = + + +

where SU is the secondary unit and PU is the primary unit.

Campaigns

The Campaign table contains the information about a measurement Campaign. Under Campaign we understand a set of measurements that relate to a given state of the structure. This could be a

particular day when all or some of the installed Sensors are measured or a given load level on a test specimen.

The Campaign properties include information about the Campaign creation date, the Operator who performed the measurements and a description.

Even if the concept of Campaign might at first seem unnecessary since each measurement is provided with a time-stamp, you will find it very useful at the data analysis stage, when it will enable to correlate measurements of different Sensors taken at the same time.

(4)

The required fields of the campaign table (that must be named “Campaign”) are the following:

CampaignID Long Integer Counter Unique identifier of the campaign ProjectID Long Integer Link to the Project table

StartDate Date/Time Campaign start date and time CampaignName Text, 20 characters Campaign name

Description Memo Campaign description, freeform

Forget Boolean True if the Campaign and the related measurements should be disregarded in future ana lysis

Measurements

The Measurements table (that must be named “Measurement”) contains the measurements and the relative data. Each Measurement relates to a single Sensor and a single Campaign. For a given Sensor and Campaign it is possible to have one, multiple or no measurements. A Measurement includes additional information like its time-stamp and information on the DAQ unit used to make the measurement.

Required fields in the Measurement table are:

MeasurementID Long Integer Counter Unique identifier of the Measurement CampaignID Long Integer Link to the Campaign table

SensorID Long Integer Link to the Sensor table

DateTime Date/Time Timestamp of the measurement

Forget Boolean True if the Measurement should be disregarded in future analysis

For each measurement there should be a corresponding entry in the "Peak" table for each entry in the PeakDef table.

Required fields in the Peak table are:

PeakID Long Integer Counter Unique identifier of the Peak MeasurementID Long Integer Link to the Measurment table PeakDefID Long Integer Link to the PeakDef table

Value Double The Measurement value expressed in primary units Forget Boolean True if the Sub-sensor measurement should be

disregarded in future analysis

Agendas

An Agenda is a list of Sensors that have to be measured. Agendas can be used to measure the Sensors automatically or manually. The Agenda properties also contain instruction on how the Sensors should be measured, how the DAQ should interact with the user, whether new Campaigns have to be created and other information relative to the Agenda execution. It is possible to create multiple Agendas containing a different set of Sensors that have to be measured in different situations (e.g. an Agenda for manual measurement of only a few Sensors, one for automatic measurements on all Sensors). An Agenda can contain multiple references to the same Sensors if it has to be measured more than once in each Campaign, for example for statistical reasons.

(5)

The core fields are:

For the Agenda table (called “Agenda”):

AgendaID Long Integer Counter Identifies the Agenda ProjectID Long Integer Link to the Project table AgendaName Text, 30 characters Agenda name

Description Memo Age nda description, freeform

CloneCampaign Boolean Automatically creates a new campaign on each agenda run

Active Boolean Sets the Agenda active for automatic execution CycleEvery Integer Number of minutes between successive Agenda

executions

AgendaStart Date/Time Defines the start date and time for automatic execution AgendaEnd Date/Time Defines the end date and time for automatic execution

For the Agenda entries table (called “AgendaItem”):

AgendaItemID Long Integer Counter Identifies the Agenda Item AgendaID Long Integer Link to the Agenda table

SensorID Long Integer Link to the Sensor table, defines the sensor to be measured

Active Boolean Sets the Agenda item active for automatic execution

3. EXTENSIONS

This basic set of tables and fields allows to cover most DAQ and DRA functionalities, however each sensor system and software package will probably require additional fields and in some cases even additional tables. These additions should be done respecting the following conventions:

- Adding fields and tables should not interfere with the basic functions of the database structure

- Each DAQ software can add fields to each table, but can modify the values of these fields only for sensor types that are under its control. Whenever possible the so-called Hungarian naming convention should be used. This avoids that two DAQs tray to add two fields with the same names but different data types.

- Each software package must assume that some of the fields that he requires might be

missing (and must be added) or have already been added by another program. It is therefore good practice to perform a database check and upgrade when opening a file.

- It is suggested, but not required, that software packages requiring additional tables create them in separated databases and link them externally.

4. IMPLEMENTATION EXAMPLES

The presented Database structure is the core of SMARTEC's SOFODB software. This software is a DAQ used to measure our SOFO sensors and other compatible instruments (e.g. Advantech's ADAM 4000 Modules). Besides the SOFO DB software we have developed a data visualization package called SOFO View allowing the representation of data from an SDB database, a web interface and a data analysis package called SPADS and dedicated to the analysis of curvature measurements from bridge monitoring projects. These modules are schematically represented in Figure 2.

(6)

SOFO Database ADAM SOFO GPS Cl Distrib. Sensors Conventional Monitoring System SOFO DB Data Acqisition SOFO View Data Display SPADS Bridge Analysis Web SOFO Web data access

Figure 2. DAQ and DRA components from SMARTEC SA

Figure 3. Implementation example of a DAQ software. The navigation section shows how it is possible to move within different Agendas, Sensors, Campaigns and Measurements.

(7)

5. CONCLUSIONS

The use of relational database structures greatly simplifies the management of large data sets generated by automatic monitoring systems. The Database structure can be used as a central node between the data acquisition (DAQ) and the data representation & analysis (DRA) software packages. This allows new measurement systems to be immediately recognised by the existing DRA packages and vice versa. We propose the SDB database structure as an open standard allowing interoperability between DAQ and DRA packages from different producers and developers.

REFERENCES

D. Inaudi, N. Casanova, P. Kronenberg, S. Vurpillot “Embedded and surface mounted sensors for civil structural monitoring”, Smart Structures and Materials, San Diego Mars 1997, SPIE Vol. 3044-23.

S. Vurpillot, D. Inaudi, A. Scano, “Mathematical model for the determination of the vertical displacement from internal horizontal measurement of a bridge” smart structures and materials, San Diego February 1996, SPIE Volume 2719-05.

N. Perregaux, S. Vurpillot, J.-S. Tosco, D. Inaudi, O. Burdet, “Vertical Displacement of Bridges using the SOFO System: a Fiber Optic Monitoring Method for Structures”, 12th ASCE Engineering Mechanics. San Diego, La Jolla. May 1998.D. Inaudi, N. Casanova, B. Glisic, P. Kronenberg, S. Lloret, L. Pflug, S. Vurpillot, “SOFO: Structural Monitoring with Optical Fiber Deformation Sensors”.

S. Vurpillot, D. Inaudi, “Object-Oriented Concept Classification of Large Measurement Data Sets in Civil Structures”, 12th ASCE Engineering Mechanics. San Diego, La Jolla. May 1998.

D. Inaudi, N. Casanova, G. Martinola, S. Vurpillot, P. Kronenberg "SOFO: Monitoring of Concrete Structures with Fiber Optic Sensors", 5th International Workshop on Material Properties and Design, Weimar, October 1998, Aedificatio Publishers, pp. 495-514.