Optimization of Spatial Joins Using Filters

When viewing present-day technical applications that rely on the use of database systems, one notices that new techniques must be integrated in database management systems to be able to support these applications efficiently. This paper discusses one of these techniques in the context of supporting a Geographic Information System. It is known that the use of filters on geometric objects has a significant impact on the processing of 2-way spatial join queries. For this purpose, filters require approximations of objects. Queries can be optimized by filtering data not with just one but with several filters. Existing join methods are based on a combination of filters and a spatial index. The index is used to reduce the cost of the filter step and to minimize the cost of retrieving geometric objects from disk. In this paper we examine n-way spatial joins. Complex n-way spatial join queries require solving several 2-way joins of intermediate results. In this case, not only the profit gained from using both filters and spatial indices but also the additional cost due to using these techniques are examined. For 2-way joins of base relations these costs are considered part of physical database design. We focus on the criteria for mutually comparing filters and not on those for spatial indices. Important aspects of a multi-step filter-based n-way spatial join method are described together with performance experiments. The winning join method uses several filters with approximations that are constructed by rotating two parallel lines around the object

Modelling manure NPK flows in organic farming systems to minimise nitrate leaching, ammonia volatilization and nitrous oxide emissions (OF0197)

Manure is an important source of organic matter and nutrients in organic farming systems, principally nitrogen (N), phosphorus (P) and potassium (K). Careful management is required during storage, handling and land-spreading to (a) ensure the most efficient use of the nutrients in the farming system and (b) to limit emissions of nitrate (NO3), ammonia (NH3), nitrous oxide (N2O), methane (CH4) and P to the wider environment. With a likely increase in the organically farmed area, information is needed on best practices for manure management in organic systems to minimise the environmental impacts of these systems. The aim was that software would calculate NPK fluxes associated with each aspect of the livestock system, and provide options to explore the impact of management change at key stages in the manure management process. The end point was to be a working prototype model/decision support system (DSS), which we could be demonstrated to a group of organic farmers and used for discussion of the NPK flows in their systems. Most of the effort in this short-term project was spent on three aspects: 1. Developing databases and the underlying model calculations. 2. Developing the software for the prototype system. 3. Limited validation of the output. The two main challenges in the project were (a) allowing a quick and easy representation of the manure management system, which is often complex and (b) being able to represent complex interactions, simply but robustly. The Manure Model (MANMOD) DSS was developed to allow an iconographic-based model representation of individual farm manure management systems to be readily constructed from a library of system components using a 'drag and drop' operation. This allows the user to construct a diagram of connecting components or ‘nodes’ (e.g. manure source, housing system, storage system) which direct and limit the flow pathway of nutrients through the farming system. Each component or node represents a key stage of the system. Once the system has been constructed, pressing the calculation button calculates the following variates for each component of the system: output (i.e. the amounts of N, P and K that will be transferred from that component of the system to the next); balance (i.e. the amount residing in that component of the system); losses (gaseous and ‘leachate’). Workshops were held at the start and end of the project. The following observations were made as a result of this exercise: - The approach is a relatively quick and simple way of constructing manure management systems. However, it is still quite complex, given the complexity of many management systems. - It may be that it is a better tool for advisers so that they can use it for several clients and become more familiar with the tool, compared with a farmer who might use it as a one-off during planning. - Even at its simplest, some detailed information is required – and in units that the farmer may not be familiar with. For example, washdown volume for the hardstanding, amount of straw (kg/animal/month), etc. However, this is not really a reason for not pursuing this information if it will provide an improvement in management. - One value is the option to scenario test. However, this is reliant on the model being sufficiently refined to be able to fairly represent the changes in response to the system. The aim of the project was to produce a prototype system. We have done this, but because of the complexity of the systems that we are trying to represent, we recognise that much more detailed validation of the model is required before it can be disseminated. There are now several Defra-funded studies that could be used in the next phase of the work. (A more detailed summary is available at the start of the main report

Tangos: the agile numerical galaxy organization system

We present Tangos, a Python framework and web interface for database-driven analysis of numerical structure formation simulations. To understand the role that such a tool can play, consider constructing a history for the absolute magnitude of each galaxy within a simulation. The magnitudes must first be calculated for all halos at all timesteps and then linked using a merger tree; folding the required information into a final analysis can entail significant effort. Tangos is a generic solution to this information organization problem, aiming to free users from the details of data management. At the querying stage, our example of gathering properties over history is reduced to a few clicks or a simple, single-line Python command. The framework is highly extensible; in particular, users are expected to define their own properties which tangos will write into the database. A variety of parallelization options are available and the raw simulation data can be read using existing libraries such as pynbody or yt. Finally, tangos-based databases and analysis pipelines can easily be shared with collaborators or the broader community to ensure reproducibility. User documentation is provided separately.Comment: Clarified various points and further improved code performance; accepted for publication in ApJS. Tutorials (including video) at http://tiny.cc/tango