------------------------Monitor------------------------- The Newsletter for PC-Based Data Acquisition and Control Issue 44 www.windmill.co.uk March 2002 --------------------ISSN 1472-0221---------------------- In the Northern Hemisphere, the 21st of March marks the beginning of Spring. A time when night and day are nearly the same length. Today we try to shed light on data acquisition in Geographical Information Systems, highlighting how automatic collection of data can save time and money. Plus, we resume our Excel corner series. We only send this newsletter to people who have subscribed - should you wish to cancel your free subscription please visit https://www.windmill.co.uk/newsletter.html CONTENTS ======== * Windmill News: Mapping Dye Fluorescence in Coastal Waters * Geographical Information Systems and Data Acquisition - Acquiring Data - GIS: A Decision-Making Tool - Low-Cost GIS Applications * Excel Corner ________________________________________________________ ________________________________________________________ Windmill News: Mapping Dye Fluorescence in Coastal Waters ________________________________________________________ Marine scientists in New Zealand are studying the dispersion and dilution of sewage in coastal waters. They release a stable, non-toxic, dye immediately downstream of the effluent outfall pump, then use a GPS receiver and fluorometer to monitor: the position of the boat they are in, dye fluorescence and temperature. To capture this data to the PC they are using Windmill 4.3 software, which is free to Monitor subscribers. The GPS and fluorometer are connected to a Notebook computer's RS232 (COM) port. Windmill saves all the readings to a single text file, time stamping each data set. The sampling interval varies depending on location, and ranges from one reading per second to one reading every five seconds. This system enables real-time measurement of the position, fluorescence and temperature in the receiving water. After collecting the data, they develop a map of dilution within the effluent plume using a Geographic Information System software package (Arcview 3.2). The scientists have used the system on other outfalls, and for other applications. For example, using the same setup but with the fluorometer calibrated to a different wavelength, they've measured the in-situ depletion of chlorophyll-a in the water column downstream of mussel farms. For more information on this system see https://www.windmill.co.uk/fluorescence.html * Our thanks to the Cawthron Institute for their help with this article. They are a private, independent, not-for-profit research centre which has been operating in New Zealand for 82 years. Their purpose is to benefit the region through science and technology, especially the aquaculture of shellfish and seaweeds, biosecurity issues, marine and freshwater science, and analytical chemistry and microbiology. http://www.cawthron.org.nz/ ________________________________________________________ ________________________________________________________ Geographical Information Systems (GIS) ________________________________________________________ The term Geographical Information System was coined by Roger Tomlinson in the 1960s. He realised that computers could be used to map and analyse the vast quantities of data that were then being collected by the Canada Land Inventory. However, it wasn't until the 1990's advances in personal computers, that GIS became more widely available to individual scientists, engineers and planners. During this decade article started to be published in technical magazines highlighting applications for GIS. For example, for mobile test and measurement, search and recovery of lost objects on the seafloor, habitat delineation and soil fertility mapping. The diverse applications all have in common the fundamental properties of a GIS. That is a computer running software capable of assembling, storing and displaying data identified according to its location. In fact, a GIS is a special case of a data acquisition system. Acquiring Data ============== Like many DAQ systems, a GIS needs to assimilate data from many different sources, to store and to analyse the data. A GIS, though, always references "attribute" data (such as temperature, fluorescence or biodiversity) to "spatial" data (such as latitude and longitude). Sources of spatial data include digitised charts and, as highlighted in our Windmill News section above, co- ordinates automatically collected from GPS receivers. Sources of attribute data include sensors and instruments connected to a PC as in other data acquisition applications. Alternatively, some attribute data - such as information from videos - may need to be entered into the system by hand. In all cases attribute data needs to be linked to the spatial data. When data is collected from GPS receivers and PC-linked instruments, this is automatically achieved as all data can be logged to the same date- and time-stamped file. Video evidence, for example, can be later added to the file using the time as a reference. GIS: A Decision-Making Tool for Scientists, Engineers and Planners ======================================================== The prime objective of a GIS is to be a decision-making tool. A useful, and often essential, aid to planning and condition assessment. It uniquely links different sets of data. The data can be presented as tables, graphs, maps or 3-dimensional images. Of course, careful planning is required before implementing a GIS. Not just of what data needs to be collected and how it should be analysed, but of how close together the samples need to be taken. For mobile applications in boats, trains, vehicles, etc, where readings are automatically logged, this translates to sampling speed. Too slow and precision diminishes, too fast and excess data needlessly occupy valuable storage space. Low-Cost GIS Applications ========================= For a simple, low-cost, GIS application where data is automatically collected - you need: - A Computer - Software like Windmill to simultaneously collect spatial and attribute data and pass these to Windows mapping and analysis software - A GPS receiver and other instruments or sensors to collect spatial and attribute data - Mapping or analysis software Monitor subscribers can download Windmill for free. This lets you automatically acquire data from GPS receivers and other instruments with RS232 ports. Windmill, however, is not limited to RS232 communications. It can also handle GPIB, Ethernet, USB, RS485, RS422, Modbus and ISA-bus devices. However, in these cases the instrument driver is not free and costs £145 (around US$210 or Euro 235). Contact sales@biodataltd.com for details. Further Reading =============== Windmill GIS Resources and Applications Monitor Issue 30: GPS https://www.windmill.co.uk/monitor30.html Monitoring Wheel Slip in Trains https://www.windmill.co.uk/train.html Mapping Dye Dispersion https://www.windmill.co.uk/fluorescence.html Interfacing GPS Receivers https://www.windmill.co.uk/gps.html Seafloor Mapping https://www.windmill.co.uk/mapping.html Monitor Issue 30: GPS https://www.windmill.co.uk/monitor30.html * Other Resources GIS@Development Magazine http://www.gisdevelopment.net/ US Geological Survey: Geographical Information Systems http://www.usgs.gov/research/gis/title.html Your Applications ================= If you have used Windmill to log GPS and other data - we'd be interested in hearing from you, and possibly featuring your application in this newsletter. E-mail the editor at monitor@windmillsoft.com or fill in the form at the bottom of https://www.windmill.co.uk/gps.html ________________________________________________________ ________________________________________________________ Excel Corner: Finding When the Maximum Data Value was Logged ________________________________________________________ Suppose you have imported a Windmill Logger file into Excel. This file contains data from 1 day, with the time each reading was collected in the first column and data values in subsequent columns. For example: A B Time Input A Secs volts 18:23:39 1.2186 18:23:40 2.4192 18:23:41 3.5836 18:23:42 4.6947 18:23:43 5.7357 18:23:44 6.6913 You want to find when the largest voltage reading was taken. To do this use this formula =INDEX(A:A,MATCH(MAX(B:B),B:B,0)) Make sure you format the cell containing the above formula as a time. Select a Custom cell format of hh:mm:ss. How we arrived at the Formula ============================= 1. First we want to find the maximum value in column B. To do this we use the MAX function. MAX (B:B) Using B:B will look through all rows in column B. In our example this returns 6.6913 2. Now we want to determine the location of the value. To do this we use the MATCH function. MATCH(lookup_value, lookup_array, match_type) Where: Looup_value is the value in which we are interested - the maximum data value. Lookup_arrray is the range of cells in which we are looking. Match_type gives options for finding the lookup_value. Match_type can be -1, 0 or 1. For 1, the default, MATCH finds the largest value that is less than or equal to the lookup_value. For 0 MATCH finds the first value that is exactly equal to lookup_value. For -1 MATCH finds the smallest value that is greater than or equal to the lookup_value. In our example the lookup_value is the MAX function, the lookup_array is column B and we want an exact match and so use 0 as the match_type. This gives us MATCH(MAX(B:B),B:B,0) This returns the row number of the maximum value in column B - row 8 in our example. 3. Finally, we want to show this row's time stamp. To do this we use the INDEX function. INDEX(reference, row_num, column_num) Where: Reference is one or more cell ranges. Row_num is the row from which to return a value. Column_num is the column from which to return a value. INDEX(A:A,MATCH(MAX(B:B),B:B,0),1) As we are only referencing 1 column, we can dispense with the column_num and we're left with INDEX(A:A,MATCH(MAX(B:B),B:B,0)) This example works with Excel 95 and later. For how to send data collected by Windmill directly to Excel, see https://www.windmill.co.uk/excel/ ________________________________________________________ ________________________________________________________ * Copyright Windmill Software Ltd * Reprinting permitted with this notice included * For more articles see https://www.windmill.co.uk We are happy for you to copy and distribute this newsletter, and use extracts from it on your own web site or publication, providing the above notice is included and a link back to our website is in place. An archive of previous issues is at https://www.windmill.co.uk/newsletter.html and an index of articles at https://www.windmill.co.uk/newsletter.html Windmill Software Ltd, PO Box 58, North District Office, Manchester, M8 8QR, UK Telephone: +44 (0)161 834 6688 Facsimile: +44 (0)161 833 2190 E-mail: monitor@windmillsoft.com https://www.windmill.co.uk/ https://www.windmillsoft.com/
Subscribing
To receive Monitor every month please fill in your e-mail address below. We will not pass your address to any third parties, nor send you any unsolicited e-mail.
You will receive an e-mail asking you to confirm your subscription. On doing so you will be sent details of how to download the free software. If you don't receive this e-mail please contact monitor@windmillsoft.com.
You can read an archive of previous issues on our Data Acquisition Intelligence site.
Cancel Subscription
You can cancel your free subscription at any time - just return to this page or follow the instructions in the newsletter.
