National Agricultural Pest Information System (NAPIS) Reports

The NAPIS database is associated with the Cooperative Agricultural Pest Survey (CAPS), a program sponsored by USDA APHIS (http://ceris.purdue.edu/napis). Jim Pheasant at Perdue University currently handles NAPIS user services (pheasant@perdue.edu, 765-494-9853).

Many of the STS cooperators provide gypsy moth data to the NAPIS database at the end of each trapping season. We have several report scripts that can be used with SQL*PLUS to create the required files for each state: (all files are located on mothsbane at D:\Reports\NAPIS\)

napis_cty_sts_del.sql      - county-based delimiting grid data
napis_cty_sts_det.sql      - county-based detection grid data
napis_cty_state_del.sql   - county-based non-STS delimiting grid data
napis_cty_state_det.sql   - county-based non-STS detection grid data
napis_sts_del.sql            - quad-based delimiting grid data
napis_sts_det.sql            - quad-based detection grid data
napis_state_del.sql         - quad-based non-STS delimiting grid data
napis_state_det.sql         - quad-based non-STS detection grid data
napis_state_wv_del.sql   - quad-based non-STS delimiting grid data, West Virginia only
napis_state_wv_det.sql   - quad-based non-STS detection grid data, West Virginia only

Create a directory and copy files to that directory:

  1. First create a directory for the current year's reports. To do this on mothsbane, move to the proper location:

D:\Reports\Napis

  1. Create a directory named 'yyyy_gm' replacing yyyy with the current year, e.g. for year 2006 you would create 2006_gm
  2. List the script files in the main napis directory (there should be eight files): ls –la napis*.sql
  3. Copy the SQL script files from the main napis directory (D:\Reports\Napis) to the current year directory (D:\Reports\Napis\yyyy_gm) (replace yyyy with current year)
  4. Copy the Perl script file sequencer.pl from the main napis directory to the current year directory.
  5. Move to the current year directory.
  6. Within the current year directory, create directories for the states that will be receiving reports, using the state abbreviation as the directory name: mkdir <dirname> (replace <dirname> with a state abbreviation, for example: mkdir va)

You are now ready to run the SQL scripts. The script files can be divided into several categories: STS and non-STS data, quad-based and county-based data, delimiting and detection surveys (determined by grid type). The following grids are included in each of the survey types:

Delimiting - RANDOM, 250M, 500M, 1K, 4/MI, 5/MI, 9/MI, 16/MI, 3/AC

Detection – 1.5K, 2K, 3K, 4K, 5K, 6K, 7K, 8K, 1MI, 1X2MI, 2MI, 3MI, 4MI, 5MI

You will need to execute the script one time for each state. Run the scripts for each of the states listed in the following table:

Filename

Description

States

napis_cty_sts_del.sql

STS county-based delimiting grid

IL, WI

napis_cty_sts_det.sql

STS county-based detection grid

IL, WI

napis_sts_del.sql

STS quad-based delimiting grid

IN, KY, MI, MN, NC, OH, VA, WV

napis_sts_det.sql

STS quad-based detection grid

IN, KY, MI, MN, NC, OH, VA, WV

napis_state_del.sql

Non-STS quad-based delimiting grid

IN, OH, VA (WV)

napis_state_det.sql

Non-STS quad-based detection grid

IN, OH, VA (WV)

napis_state_wv_del.sql

Non-STS West Virginia delimiting grid

WV (can also use above state scripts)

napis_state_wv_det.sql

Non-STS West Virginia detection grid

WV (can also use above state scripts)


Start SQL*PLUS and execute the scripts:

  1. If you are not already there, move to the current year directory:

cd D:\Reports\Napis\yyyy_gm

  1. Start SQL*PLUS and log in as gypsy at the command prompt, enter the password when prompted. When creating files for the northern states (IL, IN, MI, MN, WI), log in to the database located at Michigan State University. All report files will be created on chaos.

Login at Virginia Tech: sqlplus gypsy@trap

Login at MSU: sqlplus gypsy@STS.scrub.ent.msu.edu

  1. When executing the scripts, you will be prompted for three values (do not include any quotation marks in your responses):
    1. state abbreviation (example: VA)
    2. state FIPS code (example: 51)
      FIPS codes: IL 17, IN 18, KY 21, MI 26, MN 27, NC 37, OH 39, VA 51, WV 54, WI 55
    3. output file path and filename (example: D:\Reports\Napis\2006_gm\va\va_sts_del_2006.txt)
  2. Execute each script by typing at the SQL prompt the appropriate filename preceded by the @ sign. Example: SQL>@napis_sts_del (the .sql extension can be omitted)

The files that are created for detection survey data will require a small amount of editing. The value in the Pest Status field should be either +A or +B. The records are created with the code +A as default for Virginia and West Virginia. The code +B is included for all other states. (There are a greater number of counties in Virginia and West Virginia in which the code +A is appropriate.) +A indicates that gypsy moth is known to be established in the county, and +B indicates that gypsy moth is not known to be established. You should request from each state a list of counties in which gypsy moth is known to be established. This list can also be found at the USDA APHIS PPQ website: http://www.aphis.usda.gov/ppq/ispm/gm/  You can then edit each detection grid file and change the Pest Status field to the appropriate code.

Multiple output files will be created for most states. The files can be concatenated into one file for each state. The final file should contain all of the records for the state, both STS and non-STS data. Every record begins with the value 'GM000'. The Perl script (sequencer) can be executed to change this value to a sequential ID for each record in the file.

Concatenate files and create a unique ID for each record:

  1. Move to the appropriate state's directory. Example: cd nc
  2. Concatenate the state's output files into one file. Example:

type nc_sts_del_2001.txt nc_sts_det_2001.txt > napis_nc_2001.txt

  1. Execute the 'sequencer' script at the prompt. Example:

D:\Perl\bin\perl.exe sequencer.pl < napis_nc_2001.txt > output.txt

  1. Rename output.txt back to the input filename.:

If you scroll through the file, you will see that each record now has a sequential number at the beginning of the record.

The files can then be distributed to the appropriate agencies.

Note: copy Jim Pheasant (pheasant@purdue.edu) on each state’s emailing.

   
  © 2012 Slow the Spread Foundation, Inc.  

Page developed and maintained by Virginia Tech

  Contact Us