Differences

This shows you the differences between two versions of the page.

--- visual3d:documentation:definitions:file_formats:sql_database_definition [2024/06/26 20:21] – created sgranger
+++ visual3d:documentation:definitions:file_formats:sql_database_definition [2025/09/12 19:04] (current) – [SQL Database Definition] wikisysop
@@ Line 1: / Line 1: @@
-====== SQL_Database_Definition ======
+====== SQL Database Definition ======
-The exporting of data from Visual3D requires an external database with a predefined data structure.
+The exporting of data from Visual3D requires an external database with a predefined data structure. There are no restrictions as to which database system is used since connection to it is provided by ODBC connections. HAS-Motion’s sample code is provided using Visual Basic for Application in Microsoft’s Access database system. The data structure is normalized to eliminate redundant data entry. The specific data values for signals, metrics, and analog data, however, are stored in a large single string that must be parsed prior to using the data for analysis purposes. This approach was necessary due to the inherent speed limitations a relational database has with storing tremendous amounts of data. Note that some RDBMS systems may need add-on modules or extensions to support Transforms or Pivot Table capabilities. \\
+\\
+MS Access has the functionality built-in, so some example code provided by C-Motion may not transfer directly to other systems.) The picture below illustrates the how the Visual3D signal and event tree structure maps to database tables. The Patient Info table is provided as a sample illustration on where the relationship to patient information may be managed. The BLOB table is where the raw data is stored after the exporting process is done, and a Raw Data table is provided for the parsed results. Sample database definitions and sample routines for parsing and managing data are provided by HAS-Motion. \\
+{{:Exporting_Data_to_a_Relational_Database_doc_27915504.jpg }} \\
-\\
+===== Table Structure Definition =====
-There are no restrictions as to which database system is used since connection to it is provided by ODBC connections. C-Motion’s sample code is provided using Visual Basic for Application in Microsoft’s Access database system.
-\\
-The data structure is normalized to eliminate redundant data entry. The specific data values for signals, metrics, and analog data, however, are stored in a large single string that must be parsed prior to using the data for analysis purposes. This approach was necessary due to the inherent speed limitations a relational database has with storing tremendous amounts of data.
-\\
-Note that some RDBMS systems may need add-on modules or extensions to support Transforms or Pivot Table capabilities. MS Access has the functionality built-in, so some example code provided by C-Motion may not transfer directly to other systems.)
-\\
-The picture below illustrates the how the Visual3D signal and event tree structure maps to database tables. The Patient Info table is provided as a sample illustration on where the relationship to patient information may be managed. The BLOB table is where the raw data is stored after the exporting process is done, and a Raw Data table is provided for the parsed results. Sample database definitions and sample routines for parsing and managing data are provided by C-Motion.
-\\
-{{Exporting_Data_to_a_Relational_Database_doc_27915504.jpg}}
-\\
-=== Table Structure Definition ===
 The actual SQL database tables loosely follow the Visual3D data tree layout, and are defined below:
@@ Line 38: / Line 23: @@
 **Sample SQL scripts** to create this table structure are available for SQL Server, MySQL, and MS Access.
-\\
+===== Parsing Raw Data =====
-=== Parsing Raw Data ===
 The data exported from Visual3D into a database is too complex and dynamic for a rapid database transfer. The overhead associated with SQL Insert commands is quite high, even with optimization. For that reason, the data is formatted into a delimited structure to make intelligent post-process parsing possible. For each signal, a database row is created consisting of the signal identifier (i.e. foreign key) and a big string of raw data. The raw data - whether target data per frame, analog data, or calculated metrics – is retrieved and parsed into the following structure.
-\\
+<code>
 ^ frame_number @ label 1 @ data_value 1 @label 2 @ data_value 2 …. @ label x @ data_value x
+</code>
-\\
+===== Signal Example =====
+For a target signal called “LANK” we would see a row in the database for the signal name in L4_name table with a system generated ID called dnameID and a dataname. For example: 849 “LANK” \\
-=== Signal Example ===
+\\
-For a target signal called “LANK” we would see a row in the database for the signal name in L4_name table with a system generated ID called dnameID and a dataname. For example: 849 “LANK”
-\\
 In the L6_framedata table, we would then see the raw data associated with “LANK” represented as a row containing a system generated ID, the foreign key linking back to the L4_name table, and the data string. For example, for signal 849 (i.e. “LANK”), we would see 10 frames of data like this:
-\\
+<code>
-849 ^1@X@0.106221@Y@0.292641@Z@1.214349@Residual@0.000000@Camera\\
+849 ^1@X@0.106221@Y@0.292641@Z@1.214349@Residual@0.000000@Camera
-bits@0.000000^2@X@0.106105@Y@0.292334@Z@1.214423@Residual@0.000000@Camera\\
+bits@0.000000^2@X@0.106105@Y@0.292334@Z@1.214423@Residual@0.000000@Camera
-bits@0.000000^3@X@0.106000@Y@0.292041@Z@1.214490@Residual@0.000000@Camera\\
+bits@0.000000^3@X@0.106000@Y@0.292041@Z@1.214490@Residual@0.000000@Camera
-bits@0.000000^4@X@0.105904@Y@0.291758@Z@1.214550@Residual@0.000000@Camera\\
+bits@0.000000^4@X@0.105904@Y@0.291758@Z@1.214550@Residual@0.000000@Camera
-bits@0.000000^5@X@0.105815@Y@0.291485@Z@1.214604@Residual@0.000000@Camera\\
+bits@0.000000^5@X@0.105815@Y@0.291485@Z@1.214604@Residual@0.000000@Camera
-bits@0.000000^6@X@0.105729@Y@0.291216@Z@1.214653@Residual@0.000000@Camera\\
+bits@0.000000^6@X@0.105729@Y@0.291216@Z@1.214653@Residual@0.000000@Camera
-bits@0.000000^7@X@0.105646@Y@0.290951@Z@1.214699@Residual@0.000000@Camera\\
+bits@0.000000^7@X@0.105646@Y@0.290951@Z@1.214699@Residual@0.000000@Camera
-bits@0.000000^8@X@0.105563@Y@0.290686@Z@1.214741@Residual@0.000000@Camera\\
+bits@0.000000^8@X@0.105563@Y@0.290686@Z@1.214741@Residual@0.000000@Camera
-bits@0.000000^9@X@0.105480@Y@0.290420@Z@1.214782@Residual@0.000000@Camera\\
+bits@0.000000^9@X@0.105480@Y@0.290420@Z@1.214782@Residual@0.000000@Camera
 bits@0.000000^10@X@0.105395@Y@0.290150@Z@1.214822@Residual@0.000000@Camera bits@0.000000
+</code>
-\\
 This approach makes parsing the data fairly straightforward using the built-in parsing tools of today’s programming languages. For example, In Visual Basic, using the SPLIT command and Direct Access Object (DAO) methods for managing databases, the code to parse and store the data into the L5_framedata table would look like:
-\\
 <code>
@@ Line 114: / Line 90: @@
 </code>
-\\
+**Note** this routine could potentially take a very long time to run since a SQL INSERT is performed for every component of every frame of data. \\
-Note that this routine could potentially take a very long time to run since a SQL INSERT is performed for every component of every frame of data.
+However, once parsed and inserted into the L5_framedata table, it is very easy and fast to access the data for analysis. This is where pivot tables play an important role. They permit the dynamic storage of data components to be managed since it is impossible to accommodate a data structure for every potential digital and analog motion capture related signal generator. \\
-\\
-However, once parsed and inserted into the L5_framedata table, it is very easy and fast to access the data for analysis. This is where pivot tables play an important role. They permit the dynamic storage of data components to be managed since it is impossible to accommodate a data structure for every potential digital and analog motion capture related signal generator.
-\\
 However, pivot tables can be avoided if only certain information, in a consistant format is needed, then new tables can be created to improve the speed of the parsing process.