QueryVis: Logic-based diagrams help users understand complicated SQL queries faster

Understanding the meaning of existing SQL queries is critical for code maintenance and reuse. Yet SQL can be hard to read, even for expert users or the original creator of a query. We conjecture that it is possible to capture the logical intent of queries in \emph{automatically-generated visual diagrams} that can help users understand the meaning of queries faster and more accurately than SQL text alone. We present initial steps in that direction with visual diagrams that are based on the first-order logic foundation of SQL and can capture the meaning of deeply nested queries. Our diagrams build upon a rich history of diagrammatic reasoning systems in logic and were designed using a large body of human-computer interaction best practices: they are \emph{minimal} in that no visual element is superfluous; they are \emph{unambiguous} in that no two queries with different semantics map to the same visualization; and they \emph{extend} previously existing visual representations of relational schemata and conjunctive queries in a natural way. An experimental evaluation involving 42 users on Amazon Mechanical Turk shows that with only a 2--3 minute static tutorial, participants could interpret queries meaningfully faster with our diagrams than when reading SQL alone. Moreover, we have evidence that our visual diagrams result in participants making fewer errors than with SQL. We believe that more regular exposure to diagrammatic representations of SQL can give rise to a \emph{pattern-based} and thus more intuitive use and re-use of SQL. All details on the experimental study, the evaluation stimuli, raw data, and analyses, and source code are available at https://osf.io/mycr2Comment: Full version of paper appearing in SIGMOD 202

Colorful Niches of Phytoplankton Shaped by the Spatial Connectivity in a Large River Ecosystem: A Riverscape Perspective

Large rivers represent a significant component of inland waters and are considered sentinels and integrators of terrestrial and atmospheric processes. They represent hotspots for the transport and processing of organic and inorganic material from the surrounding landscape, which ultimately impacts the bio-optical properties and food webs of the rivers. In large rivers, hydraulic connectivity operates as a major forcing variable to structure the functioning of the riverscape, and–despite increasing interest in large-river studies–riverscape structural properties, such as the underwater spectral regime, and their impact on autotrophic ecological processes remain poorly studied. Here we used the St. Lawrence River to identify the mechanisms structuring the underwater spectral environment and their consequences on pico- and nanophytoplankton communities, which are good biological tracers of environmental changes. Our results, obtained from a 450 km sampling transect, demonstrate that tributaries exert a profound impact on the receiving river’s photosynthetic potential. This occurs mainly through injection of chromophoric dissolved organic matter (CDOM) and non-algal material (tripton). CDOM and tripton in the water column selectively absorbed wavelengths in a gradient from blue to red, and the resulting underwater light climate was in turn a strong driver of the phytoplankton community structure (prokaryote/eukaryote relative and absolute abundances) at scales of many kilometers from the tributary confluence. Our results conclusively demonstrate the proximal impact of watershed properties on underwater spectral composition in a highly dynamic river environment characterized by unique structuring properties such as high directional connectivity, numerous sources and forms of carbon, and a rapidly varying hydrodynamic regime. We surmise that the underwater spectral composition represents a key integrating and structural property of large, heterogeneous river ecosystems and a promising tool to study autotrophic functional properties. It confirms the usefulness of using the riverscape approach to study large-river ecosystems and initiate comparison along latitudinal gradients

On the relevance of preprocessing in predictive maintenance for dynamic systems

The complexity involved in the process of real-time data-driven monitoring dynamic systems for predicted maintenance is usually huge. With more or less in-depth any data-driven approach is sensitive to data preprocessing, understood as any data treatment prior to the application of the monitoring model, being sometimes crucial for the final development of the employed monitoring technique. The aim of this work is to quantify the sensitiveness of data-driven predictive maintenance models in dynamic systems in an exhaustive way. We consider a couple of predictive maintenance scenarios, each of them defined by some public available data. For each scenario, we consider its properties and apply several techniques for each of the successive preprocessing steps, e.g. data cleaning, missing values treatment, outlier detection, feature selection, or imbalance compensation. The pretreatment configurations, i.e. sequential combinations of techniques from different preprocessing steps, are considered together with different monitoring approaches, in order to determine the relevance of data preprocessing for predictive maintenance in dynamical systems

Determinants of the urinary and serum metabolome in children from six European populations

Background Environment and diet in early life can affect development and health throughout the life course. Metabolic phenotyping of urine and serum represents a complementary systems-wide approach to elucidate environment–health interactions. However, large-scale metabolome studies in children combining analyses of these biological fluids are lacking. Here, we sought to characterise the major determinants of the child metabolome and to define metabolite associations with age, sex, BMI and dietary habits in European children, by exploiting a unique biobank established as part of the Human Early-Life Exposome project (http://www.projecthelix.eu). Methods Metabolic phenotypes of matched urine and serum samples from 1192 children (aged 6–11) recruited from birth cohorts in six European countries were measured using high-throughput 1H nuclear magnetic resonance (NMR) spectroscopy and a targeted LC-MS/MS metabolomic assay (Biocrates AbsoluteIDQ p180 kit). Results We identified both urinary and serum creatinine to be positively associated with age. Metabolic associations to BMI z-score included a novel association with urinary 4-deoxyerythronic acid in addition to valine, serum carnitine, short-chain acylcarnitines (C3, C5), glutamate, BCAAs, lysophosphatidylcholines (lysoPC a C14:0, lysoPC a C16:1, lysoPC a C18:1, lysoPC a C18:2) and sphingolipids (SM C16:0, SM C16:1, SM C18:1). Dietary-metabolite associations included urinary creatine and serum phosphatidylcholines (4) with meat intake, serum phosphatidylcholines (12) with fish, urinary hippurate with vegetables, and urinary proline betaine and hippurate with fruit intake. Population-specific variance (age, sex, BMI, ethnicity, dietary and country of origin) was better captured in the serum than in the urine profile; these factors explained a median of 9.0% variance amongst serum metabolites versus a median of 5.1% amongst urinary metabolites. Metabolic pathway correlations were identified, and concentrations of corresponding metabolites were significantly correlated (r > 0.18) between urine and serum. Conclusions We have established a pan-European reference metabolome for urine and serum of healthy children and gathered critical resources not previously available for future investigations into the influence of the metabolome on child health. The six European cohort populations studied share common metabolic associations with age, sex, BMI z-score and main dietary habits. Furthermore, we have identified a novel metabolic association between threonine catabolism and BMI of children

Achievable tolerances in robotic feature machining operations using a low-cost hexapod

Portable robotic machine tools potentially allow feature machining processes to be brought to large parts in various industries, creating an opportunity for capital expenditure and operating cost reduction. However, robots lack the machining capability of conventional equipment, which ultimately results in dimensional errors in parts. This work showcases a low-cost hexapod-based robotic machine tool and presents experimental research conducted to investigate how the widely researched robotic machining challenges, e.g. structural dynamics and kinematics, translate to achievable tolerance ranges in real-world production to highlight currently feasible applications and provide a context for considering technology improvements. Machining trials assess the total dimensional errors in the final part over multiple geometries. A key finding is error variation which is in the sub-millimetre range, although, in some cases, upper tolerance limits < 100 μm are achieved. Practical challenges are also noted. Most significantly, it is demonstrated that dimensional machining error is mainly systematic in nature and therefore that the total error can be dramatically reduced with in situ measurement and compensation. Potential is therefore found to achieve a flexible, high-performance robotic machining capability despite complex and diverse underlying scientific challenges. Overall, the work presented highlights achievable tolerances in low-cost robotic machining and opportunities for improvement, also providing a practical benchmark useful for process selection

Relation, Transition and Comparison Between the Adaptive Nearest Neighbor Rule and the Hypersphere Classifier

The Adaptive Nearest Neighbor (ANN) rule and the Hyper- sphere Classifier (HC) are two very simple and relatively new variants of the classical nearest neighbor (1NN) rule. Even if they share a simi- lar formulation\u2014they correct the query-to-prototype distance by taking into account the distance of the prototype to the nearest one from other classes\u2014their relation has never been investigated. The main goal of this paper is studying this relation and providing an exhaustive perfor- mance comparison of both methods, highlighting occasions when their performances differ as well as identifying cases in which their application is advisable or leads to poorer results. Moreover, we propose a smooth transition between the two classifiers by studying the use of several con- vex combinations of their penalized distances. Experiments show that a combination is particularly helpful when both ANN and HC are worse than 1NN