1. Data Available from DPR to Help you Get Started
2. How to Use Existing Permit and Use Report Data
3. How mapping Individual Permitted Field Sites Enhances Analytical Capabilites

1.   Data Available from DPR to Help You Get Started

1.1 Overview of Available Data

• PLSNet--Section database available by county. Developed by DPR from surveyed section lines that have been extrapolated to form a complete statewide grid. Should be used for use reporting purposes only, as some of the linework is non-surveyed.

• 1995 panchromatic 10-meter SPOT satellite imagery. Tiled by DPR into township sized images and distributed with an ArcView management/navigation extension developed by Rosemary Neal, DPR. The imagery can be distributed to CACs only as part of the restricted materials permit mapping program, and may not be shared with other agencies or individuals.

• Endangered Species and PMZ databases. Section-based data that can be linked to the PLSNet for analysis and display.

• DWR Land Use data. This data may be distributed to CACs as part of the restricted materials permit mapping program. DWR surveys land use by county on a 5-year cycle. Polygons represent adjacent areas of similar land use, so individual field boundaries are often not defined.

• NRCS SSURGO soil survey data. Available directly from NRCS's website as .e00 files or from DPR's library holdings as shapefiles.

1.2 PLSNet Data

• This is a Township/Range/Section database developed from existing surveyed section lines to provide a complete coverage of California for pesticide use reporting. Distributed by county as PLSxx.shp, where xx is the county number, (e.g. 01 = Alameda).

• PLSNet data is used for Endangered Species and PMZ data. It can also be used to display and analyze current permit and use report data that are reported by section.

• In ArcView, displaying PLSNet data as a grid instead of a polygon may improve clarity. Pls.avl (download self-extracting zip file: pls_avl.zip, zipped size = 1K) is a legend developed specifically for this purpose.

• ArcView Tip! You can create a custom legend for a theme by saving it as the same name of the theme. For example, a customized legend for the theme PLS01.shp would be called PLS01.avl. The legend is stored in the same location as the .shp,.dbf. and .shx file. Each time the PLS01 theme is loaded into a view, it is rendered using the customized legend. However, once in the view, the theme's legend can be changed using the Legend Editor.

• Because of licensing restrictions, SPOT satellite imagery can only be distributed to CACs as part of the Restricted Materials Permitting Program.

• The imagery has been tiled into township-sized tiles, each of which occupy approximately 1Mb disk space.

• DPR maintains the imagery in the Albers Equal-Area projection used by the Teale Data Center. As part of DPR's commitment to support GIS development by the CACs, imagery has been, and will continue to be, supplied in other projections as specified by the CACs (e.g. California State Plane).

• An Extension, the Image Navigator Extension (download self-extracting zip file: imgnav_avx.zip, zipped size = 9K) has been developed by Rosemary Neal (DPR) to manage the imagery. The Image Navigator (IN) loads only the image tiles that are required to fill the current view. Tiles are loaded and discarded as the user pans and zooms. In addition, menu choices allow the user to zoom to user-specified townships or sections.

• Both the Endangered Species (ES) data and PMZ data are section-based databases. To perform spatial analyses the data must be attached to the PLSNet spatial data.

• In both datasets there are situations where there may be more than one ES or PMZ in a single section, creating a many-to-one relationship. Therefore, the ES and PMZ data must be linked to the PLS and not joined.

• The common field for linking the ES or PMZ data to the attributes of PLS is COMTRS.

  i. Add the PLS theme to a view and open the theme table ("attributes of PLS").

  ii. Add the ES or PMZ data to the project.

  iii. Highlight the ES or PMZ field COMTRS.

  iv. Highlight the "attributes of PLS" field COMTRS.

  v. Make one of the tables active and link the tables using the Table/Link menu choice.

  vi. To achieve two-way linking, make the other table active and link the tables again using the Table/Link menu choice.

  vii. Now you can query the ES or PMZ table and see the sections highlighted in the view, or select sections in the view and examine the ES or PMZ table for possible candidates.

• Different land use types may be displayed by using a Unique Value legend and setting the values field to "Class1" in the Legend Editor. The legend dwrsolid.avl (download self-extracting zip file: dwrsolid_avl.zip, zipped size = 2K) has been developed by DPR.

• The following query statement can be used in the Query Builder as an example for querying individual crop types, such as tomatoes:
  ([Class1] = "T" ) and ([Subclass1] = "15")    (see DWR documentation for codes)

• Key Points: Data can be up to 5-years old and reflects a snapshot of land use. Useful for general confirmation only.

• SSURGO map units consist of one to three components. (see SSURGO documentation)

• For each component there are 60 properties and interpretations in 84 different component tables.

• For each component, one to six soil layers are possible, and for each layer 28 soil properties are possible, e.g. percent clay.

• A symbol created by the concatenation of the soil survey area symbol (ssaid) and the map unit symbol (musym) uniquely identifies a map unit within the soil survey area.

• To display surface soil type, for example:

  i. Create a new table from codes.txt containing all texture components by:

  a. Performing the query        ( [Domid:c] = "texture" )

  b. Exporting the selected records to a new table e.g. texture.dbf.

  c. Add the new table (texture.dbf) to the project.

  ii. Add tables mapunit.txt and comp.txt

  iii. Add the soil shapefile to a view (e.g. soil.shp)

  iv. Join mapunit.txt (mysum) to the attributes soil.shp (tag)

  v. Join comp.txt (muid) to the attributes of soil.shp (muid)

  vi. Join texture.dbf (code_c) to attributes of soil.shp (surftex)

  vii. Classify legend using codename_c or use soils.avl (download self-extracting zip file: soils_avl.zip, zipped size = 2K).

• For clarity use the single symbol legend setting for performing queries.
  E.g. [codename_c] = "clay"

• Currently all permit and use report data has a spatial component--the section. Using PLSNet data it is possible, therefore, to display and analyze permit and use report data spatially.

• Initially, until such time as the permit and use reporting program structures are modified, it will be necessary to create a concatenated "MTRS" field. This can be done in a number of ways, but here is how to it can be done in ArcView, using the Part B table as an example:

  i. Export Part B from DataFlex into a comma delimited, ASCII text file. You may need to insert a list of column headings at the top of the file using a text editor. The column headings should be in the following format:

  "Permit", "site", "comm_code", "Township", ... ... ...

  ii. Add the text file to the project, making sure that the file type in the Add Table dialog box is set to delimited text.

  iii. The PartB.txt table that is now part of the project, is really a virtual representation of the text file and, as such, cannot be edited within ArcView. A dBase copy must be made. To do this, choose File/Export in the Table GUI. The virtual text table will be exported to PartB.dbf. Now add PartB.dbf to the project.

  iv. Start editing the PartB.dbf table by choosing Table/Start Editing.

  v. Create a new field by choosing Edit/New Field. Set the following properties:

  Name: MTRS
  Type: string
  Length: 9

  vi. Use the Calculator to fill the new field with values. Highlight the MTRS field and click the Calculator button. Enter the following statement into the Calculator Dialog box by selecting the appropriate fields from the fields list on the left-hand side (field names may differ from those used in this example):
  [bas_mer_l] + [Township] + [Range] + [Section].AsString

  This will calculate the MTRS for all the fields. However, the sections which are numbered <10 will be formatted incorrectly, because the leading zero will have been omitted. To rectify this, perform the following steps:
  a. Open the Query Builder and perform the following query:
  ( [Section] < 10 )

  b. With the records selected go back to the Calculator and enter the following calculation:
  [bas_mer_l] + [Township] + [Range] + "0" + [Section].AsString

  The records containing sections which are numbered < 10 will be updated to include the leading zero.

  vii. Stop editing the table and save the changes.

• Now the PartB table can be attached to the PLS spatial data. In most cases there will be more than one permitted site in each section, and so the PartB table and the PLS data form a many-to-one relationship and must be linked.

  i. Highlight the MTRS filed in the PartB table and in the "attributes of PLS" table.

  ii. Make one of the tables active and link the tables using the Table/Link menu choice.

  iii. To achieve two-way linking, make the other table active and link the tables again using the Table/Link menu choice.

• Here are some examples of spatial analysis that can be performed once the PartB or PUR table has been linked to the PLS spatial data. Open the Query Builder for the PartB (or PUR) table:

  i. Query to display the sections in which a particular commodity was grown--

  [Commodity] = "*tomato*"         * = wildcard (example)

  ii. Query to display all sections containing sites permitted to a particular permittee--
  [Permit] = "060033"

  iii. Query to display sections in which certain pesticides have been used--
  [Chemname] = "*Lindane*"

• The PartB or PUR table can also be queried by selecting PLS sections in the view. Records in the PartB or PUR tables linked to the selected sections will be highlighted.

• The PartA table can be joined to the PartB or PUR tables through the common field Permit because these tables have a one-to-one or one-to-many relationship. Queries of PartB or the PUR can now yield the permittee's name and address. The menu choice Table/Create a report allows the user to generate reports using Crystal Reports, which is provided with the ArcView software.

• Use Layouts to generate maps.

3.   How Mapping Individual Permitted Field Sites Enhance Analytical Capabilities

• Each permitted site is uniquely identified in a field border database (e.g. cropsites.shp) and is related by a one-to-one relationship with the PartB table. Concatenating the permit number and the site ID generates the unique identifier (pmtsiteid).
  e.g. Permit Number.......10 2000 10 00001
        Site ID....................Site 1A
        Pmtsiteid.................1020001000001site1a

  In order to relate the crop sites shapefile to the permit and use report data, it will be necessary to create new fields in the PartB and PUR tables containing the pmtsiteid information. To do this, follow steps i-iv in ArcView I-Part I, Section 2 that describe how to add a new field to the PartB table, then:

  v. create a new field by choosing Edit/New Field. Set the following properties:

  Name: pmtsiteid
  Type: string
  Length: 21

  vi. Use the Calculator to fill the new field with values. Highlight the "pmtsiteid" field and click the Calculator button. Enter the following statement into the Calculator Dialog box by selecting the appropriate fields from the fields list on the left-hand side (field names may differ from those used in this example):
  [permit] + [site]

• The PartB table can now be joined to the "attributes of crop sites" table using "pmtsiteid" as the common field, and then the PartA table can be joined through the "permit" field. Here are some examples of queries that can be performed:

  i. Query PartB table to display the crop sites in which a particular commodity was permitted--

  [Commodity] = "*cotton*"        * = wildcard (example)

  ii. Query PartA table to display all registered sites of a particular permit holder--
  [Permitee] = "A. Grower"        (example)

• Identification of individual field sites allows the user to pick a specific site and determine the environmental and other agricultural conditions that surround it. The following scenarios have been designed to demonstrate these concepts:

  Scenario I--A pest that inhabits alfalfa can be seriously damaging to almonds. The pest migrates to any almond orchard once the alfalfa is cut. Question--How does one identify almond orchards that are within a certain radius of a targeted alfalfa field?

  i. PartB and PartA tables should be joined, and the PUR Table linked to the "Attributes of Crop Sites".

  ii. Make sure the View/Properties have been set to show the correct Map Units (meters for UTM and Albers, feet for State Plane) and Display Units (usually miles--sometimes feet).

  iii. Make the "crop sites" theme active, and identify the alfalfa parcel of concern either visually, by using the Query Builder, or the Find button. Zoom into the selected parcel's location.

  iv. Use the Theme/Create Buffers menu choice to generate a buffer graphic around the selected field site (e.g. 0.5 miles).

  v. Select the graphic and use the Select Features Using Graphic button to select all the surrounding crop sites within a radius of 0.5 mile.

  vi. Open the PUR table and activate the Query Builder. Perform the following query:

  ( [Commodity] = "*almond*" )

  and choose Select From Set to perform the query only on those records that have already been selected using the buffer.

  vii. The selected records will be highlighted in the "Attributes of Crop Sites" table, and can be formatted into a report format using Theme/Create Report... For a large number of records, use the Summarize button in the Table GUI to summarize by permittee for example.

  Scenario II--On April 16, 1999 students at a school adjacent to an agricultural field complained of pesticide- related symptoms. How can one determine whether there is any data to support this?

  i. PartB and PartA tables should be joined, and the PUR Table linked to the "Attributes of Crop Sites".

  ii. Make sure the View/Properties have been set to show the correct Map Units (meters for UTM and Albers, feet for State Plane) and Display Units (usually miles--sometimes feet).

  iii. If there is a theme in the view with school locations, use the Query Builder or Find button to locate and zoom to the school in question. Otherwise use paper maps, local knowledge and imagery to approximate the school's location.

  iv. Use the Theme/Create Buffers menu choice to create a buffer graphic around the selected school polygon. If the school is represented by a point, or does not exist as part of a theme, use the Draw Circle Tool to draw a circle of specified radius around the point/location of interest (e.g. 1 mile).

  v. Select the graphic and use the Select Features Using Graphic button to select all the surrounding crop sites within a radius of 1 mile.

  vi. Make the PUR table active and perform the following query using Select From Set:

  ([Appl_date]> "04/13/99") and ([Appl_date < "04/17/99"]

  to obtain the records of pesticide use on the selected field sites that were reported to have taken place three days prior to the incident.

  vii. Examine the records and generate a report for further investigation in the field.