200 likes | 476 Views
CCIAA 2006 Annual Conference. Crime Analysis Data Management. Christopher W. Bruce Crime Analyst Danvers Police Past President Massachusetts Association of Crime Analysts Vice President of Administration International Association of Crime Analysts. Historical Outline.
E N D
CCIAA 2006 Annual Conference Crime AnalysisData Management Christopher W. Bruce Crime Analyst Danvers Police Past President Massachusetts Association of Crime Analysts Vice President of Administration International Association of Crime Analysts
Historical Outline • Paper Records and Index Files (1900-60) • Early Computerized RMS and CAD (1960-85) • Limited queries within RMS • Crime analysis still done on paper • Beginning of Desktop Computer Era (1985-95) • Crime analysis transferred to computer applications (Word, Excel, Access) • No direct link to RMS • Modern Era (1995-present) • Better RMS but still limited querying • ODBC allows direct RMS access
Your Goal …to bypass your RMS… Use modern technology… …and link directly to your data… …to query, aggregate, filter, sort, clean, map, and analyze how you want!
RMS and CAD Systems APPLICATION/INTERFACE Visual Basic C++ Java Access DBMS Oracle SQL Server Informix VMS Access
Establishing ODBC Access • Determine what DBMS your RMS uses • Install the appropriate drivers for that DBMS (you may already have them!) • Start a new connection in the ODBC Control Panel • Get the appropriate settings from your RMS vendor • Test and try the connection • Get a data dictionary from your vendor • Determine a daily process for data management • Analyze!
RMS Data Structure • Core Data Tables • Store substantive information about incidents, people, vehicles, property, etc. • Library Tables • Store “lookup” values for drop-down lists and other forced-choice selections • System Tables • Store values (temporary or permanent) needed by the system to perform various tasks • Link/Activity Tables
Core Tables • Incident • Offense and modus operandi • Person (or Master Name) • Property • Vehicles • Drugs • Citations
Library Tables • Incident or Offense Types • Person “Roles” • Colors • Vehicle Makes & Models
System Tables • Agency Name & Address • NIBRS Submissions • Units on Duty • Users Logged In
Link Tables • Incident/People • Incident/Vehicles • Person/Address History • Organizations/Members
What you can do withunrestricted access • Filter • Error Check/Clean • Functions/Calculations • Aggregation • Cross-tabulation • Data mining: looking for patterns in large amounts of data Examples!
Managing Crime Data • Using the RMS/CAD • Straight ODBC Link to RMS/CAD • Link with Joined Data Tables • Your Own “Shadow” RMS
Thank You! • Handouts and sample files at: • http://www.cbruce.net Christopher W. Bruce Crime Analyst Danvers Police Department 120 Ash Street Danvers, MA 01923 978-853-3502 cbruce@mail.danvers-ma.org