|
Philip van der Wel Administrator Posts: 145
8/7/2020
|
Philip van der WelAdministrator Posts: 145
This post is part of a series of posts titled Your Data in Your Database, guiding you through adding, viewing, updating, deleting, and exporting data tables and their data within your JunoViewer database. Click here to view an overview of the series.
Using the Add Table feature to add a new table into your JunoViewer database accomplishes two things:
- Adds the table, columns, data types, etc. to your database
- Links the table metadata, thereby optimising the JunoViewer query engine
In this help post we will cover the steps required to add a new table into your database, followed by further information regarding table links.
2.1 Set up a new table
After your table definition template is populated and uploaded to JunoViewer, go to the Add Table page (under the Data menu).
The image and details below highlight functionality in the top portion of the page:
*Notes:
- Your table name must be a single word cannot contain special characters. For table names with multiple words, we recommend using camelCase, however you can also separate using an underscore (not_camel_case or Not_Camel_Case).
- You cannot prefix custom tables with tbl as this prefix is used by JunoViewer system tables
- After you have selected the template to use and filled in all the required fields, click on the Check Template and Columns button. If your table definition template contains no errors, the bottom portion of the page will appear where you must link your table columns.
2.2 Link a new table
The bottom portion of the Add Table page provides the fields for you to link your table metadata, essentially telling the JunoViewer query engine which of your table columns map to the columns required to run data queries. For example, all data queries rely on a network and section identification code, respectively the Network ID and Section ID. Therefore, you will need to tell JunoViewer which of the columns in your table hold the Network ID and Section ID (these may have different names in your table).
The image and details below highlight functionality in the bottom portion of the page:
* Notes:
- While the framework is available, the Survey Code and Measurement Flag links are not currently utilised by any JunoViewer users. If you have columns you wish to filter or exclude data on, please contact Lonrix Support to discuss.
2.3 Further information on table links
As mentioned above, the term Table Link is used in JunoViewer to denote the metadata (underlying table structure information) related to a specific table in your database. The JunoViewer query engine is optimised to query data based the structure of the table, and specifically on whether or not certain selector columns are available or not. This information is stored in a separate administration database, not stored inside your JunoViewer database. The information held for each table link includes:
- The table name and label
- Whether the table holds date-specific or time-series (and the name of the column holding the data)
- Whether the table holds point or linear data (and the names of the columns holding the start location and, if applicable, the end location of each record
- Whether the table holds lane-specific (for roads), track-specific (for rail), or similar information (and the name of the column containing the code)
In addition to the above, information on the name, label, data type, and whether to allow null values (as defined in your Table Definition template) for each column is stored in table links.
As per the previous image, some columns are required and some are optional (depending on the type of table). We will briefly discuss the optional Lane Code, Measurement Date, and Location End columns:
Lane Code (with regards to roads, but also applicable to other linear assets)
A table where a Lane Code column is regarded as holding lane-specific data. This means that any query run on the table will only select data that matches the lane code supplied to the query. If the lane code passed in the query is NULL, "All" or "none" then all matching rows are returned regardless of the lane value. If no column is assigned as a Lane Code column, then the table is regarded as holding non-lane-specific data, and any lane specifier passed in the query will be ignored when a query is run from the table.
Measurement Date
A table where a Measurement Date column is assigned is regarded as holding date-specific data. This means that any query run on the table will only select data that matches the date range or list of dates supplied to the query. If the date range or list of dates passed in the query is NULL then all matching rows are returned. If no column is assigned as the Measurement Date column, then the table is regarded as holding non-date-specific data, and any date qualifier passed in the query will be ignored when a query is run from the table.
Location End
A table where a Location End column is assigned is regarded as holding linear data. This means that any query run on the table will only select data that overlaps with the location range supplied to the query. If no column is assigned as the Location End column, then the table is regarded as holding point data, in which case only those rows with location values falling inside the query location range will be returned.
Finally, regarding point and linear data:
- Point data refers to data that has a specific single location. An example of point data in the roading industry is a Falling Weight Deflectometer (FWD) reading. Each FWD measurement is taken at a specific point, and therefore FWD data should be held as point data.
- Linear data refers to data that covers a specified length between a specific start and end location. Examples of linear data in the roading industry is maintenance work performed over a length of road, or rut depth measurements aggregated over a 20m or 100m length.
edited by Kiki on 8/10/2023
|