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 were included in this version: 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 (1mi²) or at a larger spatial restriction level selected by the user. Results can be graphed with timeseries 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: The word "tab" refers to the main menu tabs located at the top of the screen. This example queries for applications of the pesticide chlorpyrifos during 2011-2013, statewide.

  1. Left click on "Spatial Restriction" tab. Within that tab:
    1. 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 simple query, left click on "County". Below that, click "Select All" to highlight all counties. Individual counties can be selected using the list below or by clicking on the map. On the top portion of the right column, check "Summarize at section level (1mi²)" and "Include Surface Water Testing data" to include a detailed map layer and SURF points.
  2. Left click on "Query" tab. Within that tab:
    1. In the left column titled "Query Parameters" click the bar titled "Time summarization". Left click within the box labeled "Start Year" (near the bottom of the window). Left click on "2011". 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. When finished, left click on "Add Active Ingredient" button.
    3. Left click on "Submit Query" (at the top right of the window). To keep this query simple, we did not search for specific pesticide products or inert ingredients, but that option works the same as Active Ingredient and Commodity menus.
    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.
  3. Left click on "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.
  4. 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, continue reading.

Tips & Tricks

  • When the returned dataset exceeds around 100MB in size, browser performance becomes very slow. The estimated size of the dataset is automatically displayed when you click "Submit Query".
  • 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 and all sites.

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.

The tab that is now active ("Instructions"), as well as the "Query", "Tables" and "Charts" tabs, contain an accordion element where individual sections can be expanded or collapsed by clicking on the title bar. Within each tab and accordion sections are 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: The currently active tab. It 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 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 applications, non-agricultural applications or both. The agricultural application data is at section level spatial resolution which can be queried and mapped at any of the spatial restriction layers listed. The non-agricultural production data is at county level spatial resolution which can only be queried and mapped at county level or state level. This is also where the user can choose to include section-level (1mi²) summarization and/or include SURF monitoring data that match their query.
  • Query: All non-spatial query restrictions are found here. The query can be restricted by chemicals used, products used, commodities/sites on which the chemical was used, and/or date of use. Queried 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: Spatial query results are mapped here. 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 section boundaries, and the DPR surface water-monitoring locations that 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 and more-advanced pivot tables for both PUR and SURF query results.
  • 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 that tested for the selected chemicals during the specified time period. The timeseries chart can plot selected CIMIS weather data as an overlay of 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).

Only production agriculture applications have a spatial resolution at the section level. All other records have a spatial resolution at the county level. To include records in the query results that are not for production agriculture (urban applications, mosquito abatement, etc), either select "Non-Agricultural applications" or "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 (1mi²) summarization. There is no distinct ag/non-ag flag in the PUR database, the two classes are distinguished by whether or not 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 there are some agricultural applications that lack section data due to data entry errors or grower oversight and these will be flagged as non-ag along with true non-ag applications like commercial structural pest abatement.

Next are two checkboxes that determine whether or not the data is summarized at the section level, or whether or not to include surface water testing data. If 'Summarize at section level (1mi²)' is not checked, then the data will only be spatially summarized by the spatial restriction layer. 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 DPR monitoring sites that tested for any chemical in the query will be returned along with a tabular data table of all the testing. These sites are displayed on the map and charted below the main charts of the PUR query.

Lastly, there is a dropdown that selects what spatial restriction layer is used for summarization. Only usage in selected polygons of the spatial restriction layer 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 below, or clicking polygons on the map (multiple polygons can be map selected by holding shift 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 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.

Within the "Query Parameters" column, there is an accordion that has all the parameters for the query that are not found within the previously described "Spatial Restriction" tab.

The first accordion title contains time restrictions. The query time range is defined by using the "start year" and "end year" dropdown menus. Query results can be summarized by week, month, or year. The coarser the time summarization, the 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 has a set of dropdown menus 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 type/category into the 'Active Ingredients' list in the middle column.

The next four accordion titles restrict the query by active ingredient (AI), product, inert ingredient, and commodity. To add a restriction, type 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 match what was 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, product, inert, or commodity (chem_code, prodno, cas number if it exists, and site_code respectively). The restriction is added when it appears in the list below the search box. To remove the restriction, click its name in the list. Only valid restriction names 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 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 report, and the raw data all include records flagged as possibly erroneous, so in order to match those data sources, leave all these checkboxes unchecked. When downloading the original PUR records, these checkboxes are ignored and all records are returned, whether or not they are flagged as an error.

The middle column has a list of the selected query parameters. Selected AIs, products, inerts, or commodities can be unselected in bulk by clicking the "Remove all ___" button or unselected individually by clicking the name.

The right column contains buttons to execute actions. The most important is "Submit Query" which sends the parameters to the server and requests a query be run, afterwards data will be transferred locally and 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 a csv file. Lastly there are buttons to load or save query parameters from or 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 format you want (pounds of active ingredient, or pounds of product, etc.): PURwebGIS extracts all output for the data 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).

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 1-square-mile sections
  • SURF sites that have tested for either the selected AI or an AI within a selected product during the selected time period.
  • CIMIS weather stations that were active during the query's time period

The current map scale is located in the lower left corner, and basemap attribution information is in the lower right corner 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 chlorpleth 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 or not and how transparent the layer will be if it is visible. Above this list of 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.

The next option in "Query: spatial restriction level" and "Query: Section level" is a 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 values: 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 "browse" 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 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, 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 station 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. To select multiple stations by clicking on the map, hold down shift while clicking. Once stations of interest are selected, clicking the "Download CIMIS" 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 in ascending order. Clicking again sorts the table by that column in descending order. This table is useful for determining the major contributors to the total, 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 dropdown menu to summarize the results by chemical AI or the product. In the pivot table, the dropdown menu in the upper left corner 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.

Below the display dropdown is another dropdown that defines how each cell is summarized: summing, counting, counting unique values, etc. Below that is another dropdown 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 row or column headers, respectfully. Each draggable variable is a dropdown menu that allows the user to select a subset of values to be used as headers.

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

The last accordion title 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 of these charts, the value under the mouse is displayed in a popup window. All charts have a header where the user can select how the data should be split up ("summarize by"), what variable should be summarized ("dependent variable"), and how many series should be displayed ("limit to top"). 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 charts. To the left of each chart 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 is a ‘Download Chart’ button that will download the current chart view along with its associated data, parameters, and options.

To the left of the chart are options to control how the PUR data is displayed. "Dependent Variable" specifies what summation will be used in the plotting while "Summarize by" defines how the data is summarized into series. 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. The number of data series displayed can be limited with the "Limit to top:" dropdown which keeps the specified number series with the highest total summation over the whole time period. Below the limit dropdown is a button to download the chart, legends, data that is charted, and the query that generated the original dataset. Below that button is the legend.

To the right of the chart are options to control how the weather data is displayed. CIMIS data can be summarized by whatever variables 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 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 (or tested for an AI contained within the selected product), 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 quickly determining which categories have significant contributions and which don't. 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.

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