PURwebGIS v2

How to use PURwebGIS

Contents:

Overview

This web app allows the user to query and analyze data obtained from the California Department of Pesticide Regulation's (DPR) Pesticide Use Report (PUR) and Surface Water (SURF) databases. Four main functions are included in this version 2: querying, mapping, charting and tabular summarization.

Queries can be restricted by time, space, products used, chemicals used, and/or crops treated. Query results can be mapped at various levels of detail, including the Public Land Survey System "section" level (1 mi²) or at a larger spatial restriction level selected by the user. Results can be graphed with time-series charts or a pie chart. Results can be further summarized using pivot tables. The original, non-summarized rows of data that match the query restrictions can be downloaded as a comma-separated-value (.csv) file.

Getting Started: A simple query as an example

Quickstart instructions: he word "tab" refers to the main menu tabs located at the top of the screen. This example queries for applications of the pesticide active ingredient chlorpyrifos statewide during 2011-2013.

  1. Left click on "Spatial Restriction" tab. Within that tab:
    1. The left hand side is a map with selected spatial summarization regions highlighted in blue. Regions can be selected by clicking with the left mouse button. Multiple regions can be selected by clicking while holding the shift key. Regions can also be selected in the list to the right.
    2. On the top portion of the right column, check "Summarize at section level (1 mi²)" and "Include Surface Water Testing data" to include a detailed map layer and SURF points.
    3. In the middle of the right hand column, titled "Spatial Summarization Layer", left click within the box that says "Statewide". This will display a menu of Spatial Restriction options. For this example query, left click on "County". Below that, click "Select All" to highlight all counties. Individual counties can be selected either by clicking on the county names in the list.
  2. Left click on "Query" tab. Within that tab:
    1. In the left column titled "Query Parameters" click the bar titled "Time summarization". Set “Start Year” as “2011” as follows. Left click the year which appears under the label "Start Year" (near the bottom of the window). Left click on "2011". Then, use similar steps to set "End Year" as "2013".
    2. In the left column titled "Query Parameters" click the bar titled "Add Active Ingredient". Left click within the box labeled "Search Active Ingredient". In that box, begin typing the name "chlorpyrifos". As you type, the program will list the active ingredients that match. Either complete typing "chlorpyrifos" or left click on "chlorpyrifos" from the list that appears. When finished, left click on the "Add Active Ingredient" button.
    3. Left click on "Submit Query" (at the top of the third column titled “Query Actions”). To keep this query simple, we did not search for specific commodities, pesticide products or inert ingredients, but those options work the same as the Add Active Ingredient menu.

      While the query runs, the program will display the message "Executing Query & Calculating Data Size." Once that message disappears PURwebGIS will provide an estimate of the size of the results. Click "Download Results" to continue.

    4. Once the “Downloading data” window closes, left click on the "Map" tab. The results of your query (chlorpyrifos from 2011-2013) are shown on a map of the spatial area that you chose (statewide). Pesticide use is shown in red, and surface-water monitoring results are shown as blue dots. To zoom in, left click on the plus symbol (+) or use a mouse wheel.
    5. Left click on the "Tables" and "Charts" tabs to see some additional summaries of your query results.
    To learn more about how to fine-tune or export your query results, or change other features of the summarizations, continue reading.

To learn more about how to fine-tune or export your query results, continue reading.

Tips & Tricks

  • When the returned dataset exceeds around 100 MB in size, browser performance becomes very slow. After you click “Submit Query”, the estimated size of the dataset is automatically displayed after the “Executing Query & Calculating Data Size” message disappears.
  • If the returned dataset will be too big, cancel the query and then modify the query parameters by doing one or more of the following:
    • Select a narrower time range (fewer years).
    • Select a coarser time summarization (e.g., year instead of week).
    • In the Spatial Restriction tab, de-select the options to return section-level data and SURF data.
    • In the Spatial Restriction tab, select fewer spatial restriction locations: choose only a couple key counties rather than all counties, summarize at the watershed level instead of the HUC12 level, etc.
    • Within the product/AI or site fields, select only the most important items. Do not leave these fields blank, because that selects all products, AIs, and commodities.

User Interface

The user interface is built using jQuery layout and jQuery UI graphical elements.

The PURwebGIS user interface is organized into a hierarchy of elements. At the top of the hierarchy is a row of seven tabs, located at the top of the screen under the title bar. These tabs represent the major functionality and workflow of the app.

Each of the five middle tabs (“Spatial Restriction” "Query", “Map”, "Tables" and "Charts" tabs) is subdivided into sections. You can expand or collapse the contents of an individual section by clicking on the section’s title. Within each tab and section is one or more window panes that are separated by a horizontal or vertical dividing bar. To resize a pane, this bar can be moved by clicking on the bar and dragging it to the new position. Panes along the edges can be minimized and unminimized by double clicking on the dividing bar.

Major functionality is split up into six major tabs:

  • Instructions: This tab describes how to use this web app, sources of data, caveats to consider, and what happens behind the scenes between data and display.
  • Spatial Restriction: This tab specifies which type(s) of pesticide applications the query will include, the level of resolution at which the query data is spatially summarized, and which geographic areas are included in the query results. The user can choose to receive records for agricultural pesticide applications, non-agricultural applications, or both. The agricultural application data is stored in the PUR database at the spatial resolution of “section” (1 mi2 units). Because this is the most detailed resolution, agricultural data can be queried and mapped using any of the spatial summarization layers listed within this tab. In contrast, the non-agricultural data is only stored in the PUR database at the county level of spatial resolution, so it can only be queried and mapped at either the county or state level. Provided that “Agricultural applications” has been selected, the user can select the option “Summarize at section level (1mi²)”. In addition to PUR data, this tab allows the user to choose whether to include surface-water monitoring data from the SURF database that match the query. The SURF data option is available regardless of which pesticide application or spatial resolution has been selected.
  • Query: All non-spatial query restrictions are found here. The query can be restricted by active ingredient, specific pesticide products, inert ingredients, commodities/sites on which the active ingredient was used, and/or date of use. Query results can be summarized by year, month, or week. Non-summarized query results for the PUR and SURF databases can be downloaded as a comma separated value (.csv) file. These non-summarized results are unaltered copies of all rows from the DPR datasets that match the query restrictions. The query specifications can be saved to a JavaScript Object Notation (JSON) formatted file and reloaded at a later time to avoid having to reselect the various query restrictions.
  • Map: This tab contains a map of the query results. Only agricultural applications have a section-level (1mi²) resolution; non-agricultural applications are recorded at the county level. The main window on the left is an interactive map. Displayed on this map is a choropleth (a map that is shaded in proportion to the value of the data) for the spatial restriction areas selected by the user. In addition, the map also displays the Public Land Survey System (PLSS) section boundaries, and the SURF monitoring locations from which surface-water samples were tested for the chosen chemicals during the specified time period. Each choropleth layer can be styled through the selection of color, transparency, classification algorithm, and class count. CIMIS weather stations that were active during the query's time period can be mapped, selected, and queried for weather data to be displayed in the timeseries chart.
  • Tables: This tab contains a simple table of PUR query results, more-advanced pivot tables for both PUR and SURF query results, and basic CIMIS data correlation functions.
  • Charts: Interactive charts summarizing the query results are found here. Both a timeseries line chart and a time-invariant pie chart are included. Below the timeseries charts are sparklines of every SURF site at which water samples were tested for the selected chemicals during the specified time period. The timeseries chart can plot selected CIMIS weather data as an overlay to the PUR data to identify any weather related trends.
  • UI Theme: Menu for changing the user inteface colorization theme.

Spatial Restriction Tab

At the top of the right hand column is a drop down menu to change the displayed basemap. In addition to commercial offerings from Microsoft Bing and DigitalGlobe, there are also five different years of USGS National Agriculture Imagery Program (NAIP) images which include true color, color infrared (CIR), and normalized difference vegetation index (NDVI).

For an explanation of the distinction between agricultural and non-agricultural pesticide use, please visit: http://www.cdpr.ca.gov/docs/enforce/compend/vol_8/chapter1.pdf Only production agriculture applications have a spatial resolution at the section level. All other applications have a spatial resolution at the county level. To include records in the query results that are not for production agriculture (such as applications for structural pest control or mosquito abatement), either select the "Non-Agricultural applications" or the "Both ag & non-ag applications" radio button in the right hand column. Selecting either of these radio buttons will limit the spatial summarization layer choices to State or County and will remove the option for section level (1 mi²) summarization. There is no distinct ag/non-ag flag in the PUR database, the two classes are distinguished by whether a record has a PLSS section associated with it or if that field is blank. Records with a blank section field are considered non-agricultural applications. For the vast majority of records, this classification is accurate, but some agricultural applications lack section data due to data entry errors. These will be treated as “non-ag” along with true non-ag applications

Next are two checkboxes that determine whether the data will be summarized at the section level, and whether to include surface water testing data. If the 'Summarize at section level (1mi²)' box is not checked, then the data will only be spatially summarized by the Spatial Summarization Layer selected in the pane below it. This dramatically reduces the size of the returned query, but will not allow visualization on the map at a fine resolution. If ‘Include SURF data’ is checked, a map of all SURF monitoring sites from which surface-water samples were tested for any chemical in the query will be returned along with a data table of all the testing results. These sites will be displayed on the map and charted below the main charts of the PUR query.

Lastly, there is a dropdown below the title “Spatial Summarization Layer” that selects what spatial restriction layer is used for summarization. Only usage in selected polygons of the spatial restriction layer (for example, a polygon that specifies the boundaries of Fresno County) is retrieved from the server and the returned dataset is formatted such that it can be summed for each selected polygon. The polygons can be selected by either clicking "Select All", clicking individual polygon names in the list that appears below, or clicking polygons on the map. Multiple polygons can be map selected by holding the shift key while clicking additional polygons.

Query Tab

All non-spatial query restrictions are found here, along with options to load previously saved query parameters, save selected parameters, and export the original non-summarized PUR or SURF data rows. This page is divided into three columns: the left column is where all parameters of the query are selected, the middle column contains a description of all selected query parameters for review, and the right column has buttons to save, load, and execute the query.

The "Query Parameters" column contains all the parameters for the query that are not found within the previously described "Spatial Restriction" tab. Each parameter is contained within an “accordion” that can be expanded or closed by clicking on the parameter title.

The first accordion title “Time summarization” contains time restrictions. The query time range is defined by using the "start year" and "end year" dropdown menus. The “Summarize by” dropdown allows data to be summarized by week, month, or year. The coarser the time summarization, the faster the query because less data is transferred between the server and the user. When the results are charted as a line or area timeseries, each data point will be spaced by the selected time period.

The next accordion title, “Add AI by class or type,” has a set of dropdown menus that can be used to add Active Ingredients by class, type, or specific type. These categories are defined by the PUR ai_categories table. When a class or type is selected in the dropdown, clicking 'Add' inserts every AI of that group into the 'Active Ingredients' list in the middle column.

The next four accordion titles restrict the query by active ingredient (AI), product name, inert ingredient declared on the product MSDS, and commodity. To add a restriction, start typing the AI/product/inert/commodity name into the search box, then hit the enter key or click the add button. While typing the restriction in the search box, available possibilities that include the string of letters or numbers that have been entered will appear in a dropdown box. These can be selected with the mouse or keyboard arrows in lieu of typing the full name. In parentheses next to the name is the DPR coding for that AI (chem_code; Chemical Abstract Service (CAS) number if it exists), product (prodno), inert (CAS number if it exists), or commodity (site_code). The restriction is added when you either type it in with the right code numbers or select it in the list below the search box and then either hit the “Return” key or click the “Add..” button. To remove the restriction, click its name in the list in the second column “Query Search” on this page. Only valid restriction names, including the DPR coding numbers listed above, are allowed to be added to the list. If a restriction fails to add when the enter key or the add button is pressed, check that the spelling is correct and that the item has not already been added. Restrictions are cumulative: if an AI, product, and commodity are chosen, only applications that used the specified product containing the specified AI on the specified commodity will be returned.

The last accordion title “Exclude Error Records” contains a list of error types that a PUR record could be flagged with by DPR. If any of these are checked, the query culls any record tagged with that error from the returned results. CalPip, the PUR annual reports, and the raw data all include records flagged as possibly erroneous, so in order to match those data sources bear in mind that all these checkboxes must be left unchecked. When downloading the original PUR records, these checkboxes are ignored and all records are returned, whether or not they are flagged as possible errors.

The middle column has a dynamic list of the query parameters selected in the “Spatial Restriction” and “Query” tabs. Selected AIs, products, inerts, or commodities can be unselected either in bulk by clicking the "Remove all ___" button or individually by clicking the name.

The right column contains buttons to execute actions. The "Submit Query" button sends the parameters to the server and requests that the query be run. Afterwards data will be transferred back to the user where it can be mapped, charted, and tabulated. The next two buttons allow the user to download the original data from PUR or SURF datasets that match the query parameters as csv files. Lastly there are buttons to load query parameters from or save them to a local JSON file. This allows for complex queries to be saved and reloaded at a later date.

There is no need to specify which PUR output data you want (pounds of active ingredient, or pounds of product, etc.): PURwebGIS extracts all output data for the records that match your query restrictions. After the query has been completed, the user can select the data summarization output of their choosing using the Map, Table, or Chart tabs (discussed below).

Below is a screenshot that is returned after the user clicks the “Submit Query” button and the server has run the query and estimated the size of the dataset that matches the query parameters. If the estimated download time is too slow, or the estimated size of the file is too large, refer to the “Tips for avoiding excessively large (slow) dataset downloads” section earlier in these Instructions. Or if the estimates are acceptable, click on the “Download Results” button.

Map Tab

Once the submitted query is completed, results are spatially presented in the "Map" tab.

The main map window displays five layers:

  • Satellite basemap imagery
  • Spatial restriction layer
  • A choropleth of PUR data by the spatial units selected in the “Spatial Summarization” tab
  • SURF sites that have tested for either the selected AI(s) or an AI within a selected product(s) during the selected time period
  • CIMIS weather stations that were active during the selected time period

The current map scale is located in the lower left corner, and basemap attribution information is in the lower right corner and displayed by clicking the "I" icon. The map can be zoomed in or out using the mouse scroll wheel or the buttons in the upper left corner. Clicking on a section or SURF site will produce a dialog box that summarizes the value of that item and will provide a link that, when clicked, adds that section or SURF site to the timeseries chart below the map.

To the right of the map is a pane that allows the user to change how each chloropleth layer is summarized and displayed and gives a legend for the colorization used. Each box can be collapsed or opened by clicking on the bulleted title. Within each box, the first two options control whether the layer is visible and the opacity (i.e., how transparent) the layer will be if it is visible. Above this list of five layer stylization boxes is a "Download Map" button that will download a zip file containing the current map view, data used to create that view, query parameters, the visualization options, and legends associated with the choropleths.

Below the opacity setting in the "Query: spatial restriction level" and "Query: Section level" boxes is the “Summary Units” dropdown menu that selects what value should be summed for the choropleth: pounds AI, pounds product, acres treated, or pounds product per acre treated.

The spatial restriction choropleth also has an option for the user to upload their own data values using the “Load Summary Data Values” button.For example, if the user has a custom model that calculates a risk assessment value for each HUC12 watershed, they can overlay section level usage patterns over their model output. If the user doesn’t supply a value for a specific spatial restriction region, that area will be transparent and ignored during classification To load user specified values, first, select the "Choose File" button to load summary data values. Then, from the "Summary Units" dropdown menu, select "user supplied values." Note, the file must be in the following JSON format: [{"name": "n1", "value": 101},{...},...] with the names being exactly the same as those listed under the "Spatial Restriction" tab. Values should be numbers only.

Below "Summary Units" are three options to define colorization and classification:

  1. "Color Scale" determines the color palette used to represent data ranges
  2. "Number of Colors" determines how many classes will be displayed
  3. "Scale Classification" defines how values will be distributed amongst the color classes. Classification can be:
    1. Equal interval: the restriction is divided into equal amounts (e.g. lbs AI is divided into 0-50 lbs, 50-100 lbs, 100-150 lbs, and 150-200 lbs)
    2. Quantile: bins contain an equal number of polygons (e.g. lbs AI is divided into 0-25 lbs, 25-100 lbs, 100-183 lbs and 183-200lbs, with each bin containing 5 polygons).
    3. Jenks natural break: the variance of items within a bin is minimized while the variance between bins is maximized (e.g. there is less than 5% variance in each bin, but up to 20% variance between bins).

Below “Scale Classification”, a legend correlates bin range and number of polygons in a bin range by color. To the left of the colored bars is the range of values associated with that bin. Below the colored bars, a number axis quantifies how many polygons are within each bin with the exact number of polygons in each bin printed to the right of each bar.

In the “CIMIS Weather Stations” box is a visibility checkbox and an opacity slider. Below these is a list of CIMIS stations active during the query's time period. Checking the check box or clicking the station's point on the map will select that station. Multiple stations can be selected by clicking on the map, and holding down the “Shift” key while clicking. Once stations of interest are selected, clicking the "Download CIMIS data" button will generate a popup of data fields available. After selecting which data fields to download, the user can obtain weather data and plot it with PUR data in the timeseries map as well as download the raw dataset. If a selected station does not have the selected data fields, then it will not show up in the chart.

Tables Tab

Four different ways of summarizing the query data in tabular form are found in this tab, arranged in an accordion.

"Data Table" is a sortable, two column table. Above the table are two dropdown menus. "Dependent variable" determines what variable is summed. "Summarized by" determines which variable will be used to define the rows. Clicking a column header within the table will sort the table by that column, and multiple clicks will toggle between ascending and descending order. This table is useful for determining the major contributors to the total usage that fits the query parameters, which can help identify additional restrictions the user could add to narrow the scope of the query.

Clicking the ‘Save Table’ button will download a zip file containing the data table as well as the query parameters and table options that created the table.

The next two tables are pivot tables for the queried PUR results and the SURF results. A generalized tutorial on how to use the pivot table is found here: https://github.com/nicolaskruchten/pivottable/wiki/UI-Tutorial

The PUR pivot table has a “Summarize by” dropdown menu allowing the user to summarize the results by chemical AI, the pesticide product or associated inert ingredients (if any). In the pivot table itself, the dropdown menu in the upper left corner, with default value of “Heatmap”, selects how the value cells are displayed:

  • "Table" displays unadorned numbers.
  • "Table barchart" adds a small grey bar to each cell to visually show the relative size of each cell's value.
  • "Heatmap" colors the cells with darker shades of red if they are much larger compared to others in the same row, column, or whole table depending on the style of heatmap chosen.

To its right is another dropdown, with default value of “Sum”, that defines how each cell is summarized: summing, counting, counting unique values, etc. Below that is another dropdown, with default value of “lbsai”, that defines which PUR variable is being summarized.

Variables can be dragged to the space below or to the right of the summarization dropdown, making the unique values of that variable into either row or column headers, respectively. Each draggable variable is a dropdown menu that allows the user to select a subset of values to be used as headers if they do not want all values to be included.

The SURF pivot table functions similarly to the PUR pivot table, only using the DPR surface water monitoring data.

The last accordion title, “CIMIS Correlation Table”, has a simple table that allows for calculating Correlation and Covariance statistics between usage of AIs, products, or inerts and the selected weather data.

Charts Tab

The "Charts" tab contains two different interactive charts within an accordion: a timeseries line chart, and a pie chart. When the mouse is moved over any data point in any of these charts, the value under the mouse is displayed in a popup window. To the left of all charts are dropdowns which allow the user to select what variable should be summarized ("dependent variable"), how the data should be split up ("summarize by"), and a number to indicate how many series should be displayed ("limit to top"). A pounds active ingredient dependent variable summarized by active ingredient will have a separate data series for each AI with the y-axis representing the total pounds of that AI used each time period. Summarizing by 'site_code' will generate a separate series for each crop. By choosing a "limit to top" value other than "all" only the top specified number of series will be displayed with order determined by the cumulative total of each series. These selections can be different for either of the timeseries line or pie charts. Below these settings is a ‘Download Chart’ button that will download the current chart view along with its associated data, parameters, and options. Below that button is a legend that lists the distinct values present in the 'summarize by' category, and associates each value with a color on the chart. In the header for each chart

To the right of the chart are options to control how the weather data is displayed, if the user chose to retrieve it in the query specifications. CIMIS data can be summarized by any of the variables that were selected in the data download stage. Below the summarization dropdown is a button that allows the user to download to their local disk the full dataset retrieved from the database during the data download stage. Currently, only a daily data download is supported.

The timeseries line chart has a pane below the main chart that lists each SURF site within the geographic restriction that tested for the selected AI(s) (or tested for an AI contained within the selected product(s)), and provides a sparkline of concentration values over time for each site. Next to the site's name is a checkbox that is linked to the selected SURF chart below the map. Checking the box adds that SURF site to the map's SURF chart and highlights the site on the map.

The pie chart is useful for quickly visualizing the relative contribution of each series in a time invariant format. Like the simple table, the pie chart's main utility is for quickly determining which categories have the most significant contributions. This allows the user to refine a query that highlights only the important items.

UI Theme Tab

This tab contains a dropdown dialog box that changes the coloring and button styling of the web page.

About PUR

California's pesticide use reporting (PUR) program is recognized as the most comprehensive and unique in the world. In 1990, California became the first state to require full reporting of agricultural pesticide use in response to demands for more realistic and comprehensive pesticide use data. Under the program, all agricultural pesticide use must be reported monthly to county agricultural commissioners, who in turn, report the data to DPR.

California has a broad legal definition of "agricultural use" so the reporting requirements include pesticide applications to parks, golf courses, cemeteries, rangeland, pastures, and along roadside and railroad rights-of-way. In addition, all postharvest pesticide treatments of agricultural commodities must be reported along with all pesticide treatments in poultry and fish production as well as some livestock applications. The primary exceptions to the reporting requirements are home-and-garden use and most industrial and institutional uses.

Data flow

When this web page is first opened by a user, the user's computer queries the web server requesting lookup tables for chem_code to chem_name, prodno to prod_name, site_code to site_name and the year range available in the PUR database. These tables are used in the "Query" tab to make sure the various query restrictions are valid entries. The public web server compiles these lookup tables by querying the private database, compiling the database query output into a JSON file, and sending it to the client. In addition to the database driven dynamic content, during the initial load a map of all Public Land Survey System sections that appear in PUR is downloaded for future visualization.

When the user clicks the "Submit Query" button, their computer packages an array of JSON lists that specify the query restrictions. The public web server turns these lists into an SQL query, queries the database, and then packages the results as an array of JSON lists that include the PUR results, an AI-Product lookup table for the AIs and products in the PUR results, the SURF tabular results, and the spatial SURF points.

Data Sources

Software Libraries Used

Software Use Source
PostgreSQL Database http://www.postgresql.org/
PostGIS Spatial database extension http://postgis.net/
socket.io Client-server communication. http://socket.io/
node.js Serverside javascript mediates between client and database http://nodejs.org/
express.js Web framework for node.js. http://expressjs.com/
D3.js Low level charting and data summarization http://d3js.org/
Chart.js Charting framework http://chartjs.org/
OpenLayers3 Client side mapping http://openlayers.org/
topojson.js Javascript library for the creation and manipulation of topoJSON spatial data https://github.com/mbostock/topojson
ColorBrewer2 Cartographic color sets http://colorbrewer2.org/
simple-statistics.js Jenks Natural Break calculations https://github.com/tmcw/simple-statistics
JQuery Low level javascript library http://jquery.com/
JQuery-UI Library for user interface elements http://jqueryui.com/
JQuery-UI Layout Library for creating and manipulating panes tabs and other layout elements. http://jqueryui.com/
JQuery BlockUI Plugin Page blocking library http://malsup.com/jquery/block/
liteAccordion Horizontal accordion user interface element http://stitchui.com/liteaccordion/
tablesorter Simple sortable table. http://tablesorter.com/docs/
PivotTable.js Detailed pivot table. https://github.com/nicolaskruchten/pivottable

Disclaimers

The site is managed by the University of California Davis.

Disclaimer of Liability: With respect to documents available from this site, neither UC Davis nor any of their employees or partners, makes any warranty, express or implied, including the warranties of merchantability and fitness for a particular purpose, or assumes any legal liability or responsibility for the accuracy, completeness, or usefulness of any information, apparatus, product, or process disclosed, or represents that its use would not infringe privately owned rights.

Acknowledgement

We wish to thank Dr. Larry Wilhoit, Kimberley Steinmann, and Doug Downie from California Department of Pesticide Regulation and Dr. John Steggall from California Department of Food and Agriculture for providing valuable suggestions and testing the earlier version of the tool. We wish to thank Dr. Mike Zeiss and Dr. Mike Grieneisen to shape this instruction for easy reading.

Lab Members

Professor

Minghua Zhang

Minghua Zhang

Ph.D.

Professor Environmental & Resource Science

mhzhang@ucdavis.edu

Staff

Michael L. Grieneisen

Michael L. Grieneisen

Ph.D.

Project Scientist

Dept. Land Air & Water Resources

mgrien@ucdavis.edu

Current Students

Christopher DeMars

Christopher DeMars

PhD Candidate

Dept. Land Air & Water Resources

chdemars@ucdavis.edu

Wan-Ru Yang

Wan-Ru Yang

PhD Candidate

Dept. Land Air & Water Resources

wryang@ucadvis.edu

Yu Zhan

Yu Zhan

PhD Candidate

Dept. Land Air & Water Resources

yzhan@ucdavis.edu

Huajin Chen

Huajin Chen

PhD student

Dept. Land Air & Water Resources

huachen@ucdavis.edu

Example SQL used for queries

In the following queries '{...}' is replaced by the user submitted query restriction value(s).

Sample PUR summarization Query

-- purquery: 
SELECT Row_to_json(s) AS json
FROM   (SELECT Count(t.*)                                                  AS
               row_count
                      ,
               Bit_length(Array_to_string(Array_agg(Row_to_json(t)),
                          ' ')) AS bits,
               Array_to_json(Array_agg(Row_to_json(t)))                    AS
               results
        FROM   (WITH p
                     AS (SELECT DISTINCT pu.use_no,
                                         pu.prodno,
                                         pu.applic_dt,
                                         pu.site_code,
                                         pu.comtrs,
                                         pu.aer_gnd_ind,
                                         Initcap(FORM.formula_dsc)   AS
                                         formula_dsc,
                                         Initcap(county.county_name) AS county,
                                         s.loc                       AS spatial,
                                         CASE
                                           WHEN s.pct IS NOT NULL THEN
                                           s.pct * pu.lbs_prd_used
                                           ELSE pu.lbs_prd_used
                                         END                         AS
                                         lbs_prd_used,
                                         CASE
                                           WHEN pu.unit_treated = 'A'
                                                AND s.pct IS NOT NULL THEN
                                           s.pct * pu.acre_treated
                                           WHEN pu.unit_treated = 'A'
                                                AND s.pct IS NULL THEN
                                           pu.acre_treated
                                           ELSE 0
                                         END                         AS
                                         acre_treated
                         FROM   pur.product AS prod,
                                pur.formula AS FORM,
                                pur.county AS county,
                                pur.udc AS pu
                                left outer join purwebgis.comtrs_county AS s
                                             ON pu.comtrs = s.comtrs
                         WHERE  pu.year >= 2011
                                AND pu.year <= 2012
                                AND pu.prodno = prod.prodno
                                AND prod.formula_cd = FORM.formula_cd
                                AND pu.county_cd = county.county_cd
                                AND ( chem_code = 253 )
                                AND ( s.loc IS NULL
                                       OR s.loc = 'Fresno' ))
                SELECT p.prodno                       AS product_code,
                       p.site_code,
                       p.comtrs,
                       p.aer_gnd_ind,
                       p.formula_dsc,
                       p.county,
                       Extract(year FROM p.applic_dt) AS year,
                       Extract(year FROM p.applic_dt) AS TIME,
                       p.spatial,
                       SUM(p.lbs_prd_used)            AS lbs_prd_used,
                       SUM(p.acre_treated)            AS acre_treated
                 FROM   p
                 GROUP  BY product_code,
                           p.site_code,
                           p.aer_gnd_ind,
                           p.formula_dsc,
                           p.county,
                           p.comtrs,
                           year,
                           TIME,
                           spatial) t) s; 

Sample Product-AI lookup table query

--prodchem query:
SELECT Row_to_json(s) AS json
FROM   (SELECT Count(t.*)                                                  AS
               row_count
                      ,
               Bit_length(Array_to_string(Array_agg(Row_to_json(t)),
                          ' ')) AS bits,
               Array_to_json(Array_agg(Row_to_json(t)))                    AS
               results
        FROM   (WITH p
                     AS (SELECT DISTINCT pu.prodno    AS prodno,
                                         pu.chem_code AS chem_code
                         FROM   pur.product AS prod,
                                pur.formula AS FORM,
                                pur.county AS county,
                                pur.udc AS pu
                                left outer join purwebgis.comtrs_county AS s
                                             ON pu.comtrs = s.comtrs
                         WHERE  pu.year >= 2011
                                AND pu.year <= 2012
                                AND pu.prodno = prod.prodno
                                AND prod.formula_cd = FORM.formula_cd
                                AND pu.county_cd = county.county_cd
                                AND ( chem_code = 253 )
                                AND ( s.loc IS NULL
                                       OR s.loc = 'Fresno' ))
                SELECT pc.prodno       AS prodno,
                       pc.chem_code    AS chem_code,
                       pc.chemstat_sw  AS chemstat_sw,
                       pc.prodchem_pct AS prodchem_pct
                 FROM   pur.dpr_prod_chem AS pc,
                        p
                 WHERE  p.prodno = pc.prodno
                        AND p.chem_code = pc.chem_code) t) s; 

Sample SURF summarization Query

--surface water data query:
WITH p
     AS (SELECT DISTINCT pu.chem_code
         FROM   pur.product AS prod,
                pur.formula AS form,
                pur.county AS county,
                pur.udc AS pu
                LEFT OUTER JOIN purwebgis.comtrs_county AS s
                             ON pu.comtrs = s.comtrs
         WHERE  pu.year >= 2011
                AND pu.year <= 2012
                AND pu.prodno = prod.prodno
                AND prod.formula_cd = form.formula_cd
                AND pu.county_cd = county.county_cd
                AND ( chem_code = 253 )
                AND ( s.loc IS NULL
                       OR s.loc = 'Fresno' )),
     dsextract
     AS (SELECT dsw.*
         FROM   purwebgis.dprsurfacewater AS dsw,
                p
         WHERE  p.chem_code = dsw.chem_code
                AND Extract(year FROM dsw.samp_date) >= 2011
                AND Extract(year FROM dsw.samp_date) <= 2012),
     dstime
     AS (SELECT NAME,
                Min(samp_date) AS first_samp,
                Max(samp_date) AS last_samp
         FROM   dsextract
         GROUP  BY NAME)
SELECT Row_to_json(s) AS json
FROM   (SELECT Count(t.*)                                                  AS
               row_count
                      ,
               Bit_length(Array_to_string(Array_agg(Row_to_json(t)),
                          ' ')) AS bits,
               Array_to_json(Array_agg(Row_to_json(t)))                    AS
               results
        FROM   (SELECT dsextract.*
                FROM   dstime,
                       purwebgis.dprmonitoringpoints AS dmp,
                       purwebgis.comtrs_county AS s,
                       dsextract
                WHERE  dstime.NAME = dmp.NAME
                       AND s.comtrs = dmp.comtrs
                       AND ( s.loc IS NULL
                              OR s.loc = 'Fresno' )
                       AND dsextract.NAME = dmp.NAME
                       AND dsextract.NAME = dstime.NAME) t) s; 

Sample SURF spatial location Query

--surface water point query:
WITH p
     AS (SELECT DISTINCT pu.chem_code
         FROM   pur.product AS prod,
                pur.formula AS form,
                pur.county AS county,
                pur.udc AS pu
                LEFT OUTER JOIN purwebgis.comtrs_county AS s
                             ON pu.comtrs = s.comtrs
         WHERE  pu.year >= 2011
                AND pu.year <= 2012
                AND pu.prodno = prod.prodno
                AND prod.formula_cd = form.formula_cd
                AND pu.county_cd = county.county_cd
                AND ( chem_code = 253 )
                AND ( s.loc IS NULL
                       OR s.loc = 'Fresno' )),
     dsextract
     AS (SELECT dsw.*
         FROM   purwebgis.dprsurfacewater AS dsw,
                p
         WHERE  p.chem_code = dsw.chem_code
                AND Extract(year FROM dsw.samp_date) >= 2011
                AND Extract(year FROM dsw.samp_date) <= 2012),
     dstime
     AS (SELECT NAME,
                Min(samp_date) AS first_samp,
                Max(samp_date) AS last_samp
         FROM   dsextract
         GROUP  BY NAME)
SELECT dmp.gid,
       dmp.NAME,
       dmp.comtrs,
       St_asgeojson(dmp.geom) AS geom
FROM   dstime,
       purwebgis.dprmonitoringpoints AS dmp,
       purwebgis.comtrs_county AS s
WHERE  dstime.NAME = dmp.NAME
       AND s.comtrs = dmp.comtrs
       AND ( s.loc IS NULL
              OR s.loc = 'Fresno' ); 
Basemap






Spatial Summarization Layer

Query Parameters

Time summarization

Add AI by class or type

Add by AI Class


Add by AI Type


Add by AI Specific Type

Add Active Ingredient

Add Product

Add Inert

Add Commodity

Exclude Error Records

Query Summary

Query within the following Statewide spatial regions:

  • California


For agricultural applications only



Summarized at the spatial region only



Not including SURF sites



Summarized by year from 2013 to 2014



For any product



Containing any active ingredient



Containing any inert ingredient



Applied to any commodity



With no error flagged rows excluded.



Query Actions







Save query:

Load query:


Basemap

Click on layer nodes below to change their properties
  • Basemap




  • Query: spatial restriction level choropleth




    Load Summary Data Values:







    Legend:


  • Query: section level choropleth












    Legend:


  • DPR Surfacewater Monitoring




  • CIMIS Weather Stations




    Select stations:
    Select All


Data Table

PUR Pivot Table

Summarize by:

SURF Pivot Table

CIMIS Correlation Table

Timeseries Line Chart

SURF chemical concentration (ppb) vs time by site

Select SURF site on map

PUR chart parameters

Dependent Variable:

Summarize by:

Limit to top:

PUR Legend

CIMIS chart parameters

Summarize by:

CIMIS Legend

Pie Chart

Dependent Variable: Summarize by: Limit to top:

Legend