Iowa - Record Layout of Traffic Stop Data

Peninsula Press filed this request with the Iowa Department of Public Safety of Iowa.


From: Vignesh Ramachandran

To Whom It May Concern:

I am the managing editor of Peninsula Press, a multimedia news website based out of Stanford University in California. We are working on collecting data on traffic stops and searches made by state patrols across the country, but I'm initially gathering information about what datapoints each state collects.

Pursuant to the Iowa Open Records Law (Code Chapter 22), I hereby request the following records:

I’d like to please request the record layout (the format of a data record, which includes the name, type and size of each field in the record) or data dictionary for your state’s records of traffic stops made on your roads. (What are all the datapoints you collect and keep record of when traffic stops are made?)

The requested data will be analyzed, eventually be made available to the general public and be used for editorial purposes. This request is not being made for commercial purposes. We kindly ask the request be filled electronically — via Excel or another database format. Data can be emailed to or shared via a Dropbox link. In the event data has to be sent physically on a CD or thumb drive, please mail to Peninsula Press, Stanford Journalism Program, 450 Serra Mall, Building 120, Stanford, CA 94305.

Thank you in advance for your anticipated cooperation in this matter. I look forward to receiving your response to this request within 10 business days, as the statute requires.


Vignesh Ramachandran

Vignesh Ramachandran
Managing Editor, Peninsula Press
Stanford Journalism Program
phone 650.723.0703
physical mail 450 Serra Mall, Building 120, Stanford, CA 94305

From: Ludwig, Nathan [DPS]

I have received your request and will be looking into this next week.

Thank you

Sergeant Nathan Ludwig •390•
Iowa State Patrol
Public Information Officer

39th Basic D.P.S. Academy
Curriculum Coordinator
Iowa Department of Public Safety
Office: 515-725-6093
Cell: 515-745-2863<>


To Whom It May Concern:

I wanted to follow up on the following Freedom of Information request, copied below, and originally submitted on June 29, 2015. Please let me know when I can expect to receive a response, or if further clarification is needed.

Thank you for your help.

From: Ludwig, Nathan [DPS]

Your request information is below:

TSB provided the below list of database columns/fields and their type and size. Obviously the list that appears under “create table ECCO” corresponds to our electronic citations, and the list that appears under “create table EWC” corresponds to our electronic warning citations.

create table ECCO
actionbailsetbyjudge VARCHAR2(30),
actionconfinedin VARCHAR2(40),
actioninitialaction VARCHAR2(1),
actionschedbail VARCHAR2(9),
actionschedfine VARCHAR2(9),
actionsetbail VARCHAR2(9),
actualspeed VARCHAR2(3),
admincode VARCHAR2(20),
badgenumber VARCHAR2(10),
bond VARCHAR2(9),
carrierkey VARCHAR2(100),
city VARCHAR2(5),
cmv VARCHAR2(1),
color VARCHAR2(10),
comments BLOB,
constructzone VARCHAR2(1),
county VARCHAR2(3),
courtaddress VARCHAR2(100),
courtcity VARCHAR2(50),
courtcosts VARCHAR2(9),
courtdate DATE,
courtreq VARCHAR2(1),
courtstate VARCHAR2(2),
courttime VARCHAR2(15),
courtzipcode VARCHAR2(10),
damgassessment VARCHAR2(1),
datacode VARCHAR2(4),
defendantsign BLOB,
disp_nonscheduled VARCHAR2(2),
dispadjudicationchargecode VARCHAR2(20),
dispadjudicationdate DATE,
dispadjudicationtype VARCHAR2(4),
dispbail VARCHAR2(9),
dispcdlcmv VARCHAR2(2),
dispcitationnumber VARCHAR2(20),
dispconvictdchrgcode VARCHAR2(20),
dispcountynumber VARCHAR2(2),
dispcourtcost VARCHAR2(9),
dispdaysinjail VARCHAR2(5),
dispdispdate DATE,
dispdispvioltncode VARCHAR2(20),
dispfinaldisposition VARCHAR2(1),
dispfine VARCHAR2(9),
dispjudicialdistrict VARCHAR2(2),
dispjurisdiction VARCHAR2(1),
dispjurisdictnchrgtype VARCHAR2(2),
displocalcodeprefix VARCHAR2(4),
dispsentencetype VARCHAR2(2),
dispsurcharge VARCHAR2(9),
dispviolationcode VARCHAR2(20),
fatal VARCHAR2(1),
iareasoncode VARCHAR2(2),
indivkey VARCHAR2(100),
injury VARCHAR2(1),
localordinance VARCHAR2(20),
loccolkey VARCHAR2(100),
lockaddress VARCHAR2(50),
lockage VARCHAR2(5),
lockcity VARCHAR2(50),
lockcmv VARCHAR2(1),
lockcounty VARCHAR2(2),
lockdateofbirth DATE,
lockdl VARCHAR2(25),
lockdlclass VARCHAR2(9),
lockdlendorsements VARCHAR2(9),
lockdlrestrictions VARCHAR2(11),
lockfirstname VARCHAR2(50),
lockgender VARCHAR2(1),
lockhazmatreq VARCHAR2(1),
lockheight VARCHAR2(3),
lockiccnumber VARCHAR2(10),
locklastname VARCHAR2(50),
locklicensestate VARCHAR2(2),
lockmiddlename VARCHAR2(20),
lockrace VARCHAR2(1),
lockstate VARCHAR2(2),
locksuffixname VARCHAR2(5),
lockusdotnumber VARCHAR2(10),
lockvehiclecolor VARCHAR2(15),
lockvehiclemake VARCHAR2(30),
lockvehiclemodel VARCHAR2(30),
lockvehicleplatenumber VARCHAR2(10),
lockvehicleplatestate VARCHAR2(2),
lockvehicleplateyear VARCHAR2(4),
lockvehiclestyle VARCHAR2(15),
lockvehicleyear VARCHAR2(4),
lockweight VARCHAR2(3),
lockzipcode VARCHAR2(10),
nonscheduled VARCHAR2(1),
notaryother VARCHAR2(50),
notarysignature BLOB,
offenselocatn VARCHAR2(50),
offensetype VARCHAR2(1),
officersign BLOB,
officersigndate DATE,
officerswearname VARCHAR2(50),
officerswearsignature BLOB,
other VARCHAR2(1),
plaintiff VARCHAR2(1),
plaintiffkey VARCHAR2(100),
postedspeed VARCHAR2(2),
prdkey VARCHAR2(100) not null,
propdamage VARCHAR2(1),
scheduledfine VARCHAR2(9),
sectionviolated VARCHAR2(20),
seriouspi VARCHAR2(1),
surcharge VARCHAR2(9),
swearagency VARCHAR2(10),
swearcity VARCHAR2(8),
sweardate DATE,
totalcost VARCHAR2(9),
vehiclekey VARCHAR2(100),
violationdate DATE,
violationtime VARCHAR2(15),
violdescription VARCHAR2(150),
locationkey VARCHAR2(100),
countysurcharge VARCHAR2(9),
courtmailaddress VARCHAR2(100),
courtmailcity VARCHAR2(50),
courtmailzipcode VARCHAR2(10),
masteragencyid VARCHAR2(20),
masterbadgenumber VARCHAR2(10),
masterofficername VARCHAR2(50),
masteruserid VARCHAR2(10),
courtseqnumber VARCHAR2(1),
localsection VARCHAR2(20),
lockcarrypass VARCHAR2(1),
lockethnicity VARCHAR2(1),
lockidviewed VARCHAR2(1),
officerfirstname VARCHAR2(50),
officerlastname VARCHAR2(50),
referencenumber VARCHAR2(7),
statesection VARCHAR2(20),
gvwr VARCHAR2(2),
roadwaytype VARCHAR2(2),
speeddetectionmethod VARCHAR2(2),
tact VARCHAR2(2),
video VARCHAR2(2),
dispatchcasenum VARCHAR2(20),
citationclass VARCHAR2(3),
citationtype VARCHAR2(3),
lockvehiclevin VARCHAR2(20),
defendantsign_pictype BLOB,
defendantsign_type VARCHAR2(150),
notarysignature_pictype BLOB,
notarysignature_type VARCHAR2(150),
officersign_pictype BLOB,
officersign_type VARCHAR2(150),
officerswearsignature_pictype BLOB,
officerswearsignature_type VARCHAR2(150)
) ;

create table EWC
actualspeed VARCHAR2(3),
badgenumber VARCHAR2(10),
defendantsign BLOB,
departmentaddress VARCHAR2(50),
departmentcityline VARCHAR2(100),
departmentname VARCHAR2(50),
equiprepairorder VARCHAR2(2),
equipviol VARCHAR2(25),
iareasoncode VARCHAR2(2),
indivkey VARCHAR2(100),
offenselocatn VARCHAR2(50),
officersign BLOB,
postedspeed VARCHAR2(2),
prdkey VARCHAR2(100) not null,
sectionviolated VARCHAR2(20),
vehiclekey VARCHAR2(100),
violationdate DATE,
violationtime VARCHAR2(15),
violdescription VARCHAR2(150),
referencenumber VARCHAR2(10),
defendantsign_pictype BLOB,
defendantsign_type VARCHAR2(150),
officersign_pictype BLOB,
officersign_type VARCHAR2(150),
codesection VARCHAR2(50),
defendantdealernumber VARCHAR2(5),
ordinance VARCHAR2(1),
sectionviolatednew VARCHAR2(6),
violdescriptionnew VARCHAR2(150)

Sergeant Nathan Ludwig •390•
Iowa State Patrol
Public Information Officer

39th Basic D.P.S. Academy
Curriculum Coordinator
Iowa Department of Public Safety
Office: 515-725-6093
Cell: 515-745-2863<>