Overdose Data

July 18, 2014 in Blog by Tiana Antul

I thought that this would be an interesting topic to write about as it lines up nicely with next month's meeting presentation by HIDTA on Fentanyl-laced heroin. In recent months there has been a lot of interest surrounding the quickly escalating heroin epidemic in Massachusetts. I personally have fielded recent requests for heroin overdose data from within my Department, from the Dept. of Public Health, and from the US Attorney General's Office.

This past Monday I attended a meeting in Boston at the US Attorney General's Office to discuss the current state of the issue and what data, if any, is available in order to shed light on the scope of the problem. The meeting was attended by several other major cities including Springfield and Boston PDs, Mass. State Police, the Department of Public Health, Drug Enforcement Administration, the Medical Examiner's Office, and several representatives from the US Attorney General's Office.

Generally speaking, law enforcement recognized similar limitations across the board as far as being able to provide real-time accurate statistics on heroin overdoses. Namely, overdoses are often reported as medical calls, for which reports are generally not taken by police, and when the incident is known to be an overdose, the specific drug involved might or might not be mentioned. Other agencies in attendance reported a variety of other problems that prevented them from having accurate statistics as well.

While I am limited by the data as far as being able to query heroin overdoses specifically, I am able to query incidents reported as overdoses (not to a drug-specific level). I expected to see an upward trend, but what I found amazed me. Here are the highlights:

* Reported Overdose incidents to WPD have increased for the last 7 consecutive years.

* Year to date (7/15/14) there have been 259 reported ODs; should they continue at this rate there will be a projected/forecasted 482 overdoses reported in 2014, which will constitute an 8th consecutive year of increase.

* From 2006 (where there is a natural break in the data with a low of 96 reported ODs) and 2013 (the current height of the recent upward trend) there has been a 366% increase in the number of annually reported overdoses.

* The average year-to-year increase in reported ODs since 2006 is 25.93%. However, it has ranged from as low as 1.48% (change from 2010 to 2011) to as high as 58.28% (the change from 2007 to 2008).

New Picture (1)

Breathing New Life Into Your Analysis

May 7, 2014 in Blog by Tiana Antul


Stuck in a rut? So many of the tasks we perform are done on a daily or other routine basis that it's easy to find yourself doing the same thing day in and day out. And while that might get you by, try to never fall victim to adopting the "good enough for government work" adage; working yourself into a rut of mundane routine can result in stale products and an audience that quickly loses interest in your work. Instead, challenge yourself every week or two do something new that will keep things "fresh", keep your PD interested in the work that you do and impressed with your dedication to develop and hone your skills. This is not only an investment in your community and your Agency, but an important investment in yourself.


It won't take much to wow your audience. You need not spend weeks working on an intricate link analysis for a crime series that will be long over with by the time you finish charting it. Simply stay connected with the people in your agency and you'll discover endless ways to support them with celerity and ease. In my experience, many people don't know what it is that they want, that they can ask for it, or how to ask for it. Don't be afraid to take the initiative to do something that you weren't asked for and say, "Here, I thought this might be useful to you for your next meeting". People will be impressed with your initiative and professionalism, never mind the work that you've done. It will inspire *some* people to come up with ideas of their own and to ask you for more assistance. Building these bridges is what keeps analytical gears moving smoothly in your agency.

As a short example to this point, I used to work with a Detective Sergeant when I first started working as a crime analyst, providing statistical and analytical support on domestic violence cases. My responsibilities broadened over the years to include much more than just domestic cases, his specialized Unit eventually got absorbed into our Detective Bureau, and ultimately another analyst took over my former role. Several years went by without much contact between him and I other than a passing "hello" in the hall here and there (which can easily happen in a department this size). Recently I did some work on escalating offenders using arrests as an indicator, and decided to apply the same concept to domestic offenders using incident involvement as an indicator. This yielded a short list of about a dozen or so names of people who had had 3 consecutive years of increased domestic violence incident involvements as an arrestee/suspect. No one asked me for this, but I thought it might be useful.

I brought the list of "escalating offenders" up to this Detective Sergeant, who with appreciation took the list, looked it over, and then pulled a 3X5" index card out of his pocket (old school record keeping in Worcester dies hard with the veteran cops) that coincidentally had the name of one of the offenders on my list written on it… It was someone he was currently looking into for a domestic-related incident. 

He then asks if we (an of course by "we" he meant "yours truly") can expand on the list and add any other violent history outside the domestic realm. "Yes", I reply. Then another detective walks in and the Detective Sergeant makes a copy of the list for him. Turns out this Detective attends a monthly meeting with other local agencies and groups to identify high risk/dangerous domestic offenders. He asks if we can find out which offenders are gang-involved. "Yes", I reply, with a grin. "Can we get a list of all the offenders who have prior firearm charges?". "Yes", I reply again, more than a little excited to head back downstairs and get to work building a few new queries. Today he emailed me and asked if we could incorporate attempted murder and stalkng charges to the existing weapons data I pulled. "Yes", I reply, excited to have another query to build. And just like that, with a small amount of unsolicited toughtfulness, the door of communication flung wide open, a conversation began, ideas started flowing and haven't stopped flowing, and new life was breathed into a topic.



Batch Files, Task Scheduler, and Automation, Oh My!

April 25, 2014 in Blog by Tiana Antul

Happy Friday to everyone!

It was wonderful to see such a great turnout to the Watertown meeting this month. A huge thank you to Lloyd Burke for what I thought was an outstanding presentation.

How outstanding was it? I went back to work and toiled and tried and erred and today I got my first batch file to run properly! He was right… it’s so easy a monkey could do it. 

Once you’ve written your first successful batch file and gotten over the little bumps that you’re likely to run into along the way, you’re going to want to automate everything you do on a regular basis. I thought I’d share my experience and some of the mistakes I made (including one rather LARGE rookie one) to spare others the same headaches.

I started with a new report I was recently asked to provide to the Department of Public Health. They are analyzing trends in opiate use and were interested in getting weekly reports on all overdose incidents reported to our Police Department.

I started with a relatively simple select query to pull the data. Knowing that I intend to create a scheduled task that will run this report every Sunday for the previous week, I set the date parameters to “Between Date()-7 and Date()-1 + #11:59:59#”. I have to add the #11:59:59# because in our RMS the date field is a combined date and time. This ensures that I’m capturing everything that was reported from the previous Sunday all the way through midnight on following Saturday.


Next step? Macro time! I created a macro that would run the above-mentioned query and export the results to my local drive in Excel file format.

For the intents of creating a batch file that you will run by using Task Scheduler, you will want this Macro to automatically execute when Access is opened. There are two ways to do this (that I know of), but the easiest way is to simply name the Macro “AutoExec”. The name will bold face and the macro will automatically run whenever you open the database.

Lesson Learned #1: If you use this database for any other work, DO NOT use an AutoExec Macro to shut down Access when it finishes running. Rather, have the batch file shut down Access. I made this mistake and nearly had a panic attack the next time I went to open my database and it immediately closed on me because of this epic mistake. There’s really no way to delete or edit a macro in a database without being able to open the database and keep it open (not that I’m aware of anyway). Thank goodness for computer backups (I guess those IT people we complain about all the time actually do come in handy now and then! Phew!).


Okay, step 3. Write your batch file. For our intents and purposes, these really truly are easy to write. I promise you. I ran into one glitch where I was asking the batch to delete a file on my PC before running my macro so that Access could write over the existing file without having to ask an end user if it was okay. It took me a while, but I figured it out, and this is lesson #2:

Lesson Learned #2: Make sure that all your files are located in the same directory. This makes the batch file happy.smiley I keep my database, the notepad text file, batch file, and the Excel file that gets exported from Access, all in the same folder on my local drive.


The next problem I ran into was the batch file not being able to locate folders and files that I was trying to point to by using a directory. Solution?


Lesson Learned #3: When pointing to a file or folder in batch script, make sure that there are no spaces in the folder or file names. I always knew this to be good programming practice, but it honestly never caused me any issues until now, and apparently I got sloppy in my naming practices. Once I removed the spaces and tried running the batch file again, everything worked like a charm. And this stuff runs fast!


The next step was to create the scheduled task. I couldn’t see my task scheduler when I opened up my control panel so I had to do a search for it. Scheduling a task is kind of fool proof since you’re walked through every step. I told my computer to run the batch file every Sunday at noon. There was even an option for the task to run whether I was logged in or not. (This coming Monday I’ll find out if it ran as expected). With any luck the report will sitting on my computer waiting for me to email it off. If not, then I know the issue is with the scheduled task and I will trouble shoot from there.


The next and final step is to look for a way to include script in the batch file that will email this report automatically. My initial research points to SMTP, but most solutions involve using a 3rd party program, which isn’t an option for me because I don’t have Admin privileges and I can’t download software on my work PC.


So this is what I’ve done a la the Great and Powerful Lloyd Burke, and my missteps which hopefully help you to avoid the same mistakes that I made. Feel free to call or email me if you want help doing this. I can’t guarantee to know the answer but there’s no problem we can’t figure out a solution to. Please share your experiences too! This is great stuff and Lloyd is leading the automation revolution.

by KCurtis

Military Intelligence Background Aids Crime Analyst – Pittsfield Police Depatment

March 10, 2014 in Blog by KCurtis

(The following was taken from the Berkshire Eagle entitled Pittsfield Police Crime Analst Helped by Military Intelligence Background by Jim Therrien)

PITTSFIELD — With training in military intelligence, Amanda O'Connor thought there was little chance she'd find civilian employment matching that skill in her native Berkshire County.

But that changed for the 25-year-old Sheffield resident when the position of crime analyst was established last year in the Pittsfield Police Department. O'Connor was selected from among more than 50 applicants for the job and began work at the Allen Street police station just before New Year's Day.

"It was a dream come true," she said. "I get to do what I want to do and stay in the Berkshires at the same time."

O'Connor is a 2006 graduate of Mount Everett Regional High School and a 2010 graduate of Williams College.

While the job of crime analyst is new to the city, it is rapidly catching on in police departments across Massachusetts and in other states. In requesting funding for the new position, Chief Michael Wynn said he immediately suggested an analyst when asked what one change would have the greatest positive impact on his department.

O'Connor said she uses data and statistical analysis and computer skills to assist officers in the field, and the information also helps Wynn and other leaders in managing the force and developing effective crime-countering strategies. She also is working to broaden the PPD's electronic connections with other law enforcement entities.

To gain some background on the job, O'Connor said that before starting she and Wynn met with members of Massachusetts Association of Crime Analysts, who work in other police departments, institutions such as colleges and state agencies. "They are very supportive of each other," she said.

"When I started, I had a whole bunch of ideas," O'Connor said. "Chief Wynn and I sat down to arrange the priorities. The first thing I did was organize the [daily police] logs."

She said she wanted to organize the logs — which list the calls officers respond to in chronological order, a condensed version of which is published in The Eagle — in part to help her learn how the department operates. Almost immediately, she said, officers noticed her categorized call lists and began referring to them.

Right away, the categories were useful to officers, Wynn said. "And from a management point of view, that's gold," he said.

Her lists break down calls over a given time period into such categories as Alarm, Burglar; Disturbance; Domestic; Harassment; Homeless Assist; Motor Vehicle Stop; Smoke Odor; Assist Citizen; B&E Motor Vehicle; Assault; Serve Arrest Warrant; Sex Offender Registry; Well Being Checks, and Unwanted Suspect.

"But our main priority has been the Hot Sheet," O'Connor said.

The "sheet" is a modern version of the old shift change roll call briefing, usually delivered in TV cop shows by a gruff, no-nonsense sergeant, as in the landmark series, "Hill Street Blues."

Today, O'Connor is overseeing a transition from the current Word document format, which officers can read or print out and take with them on the road, to a PowerPoint-style presentation with color photos, maps, and arrest, prison release and "wanted" information. It also includes information about crime trends and locations, state or national news relating to street drugs, weapons encountered by police or other information.

The Hot Sheets are shown on a TV screen in a slide-show format, but will soon be available to officers in a continuously running loop, O'Connor said, and the goal is to make it available on computer consoles in police cruisers.

She and Wynn spoke about her job during a recent city Police Advisory Committee meeting. The chief said O'Connor quickly had an impact by noticing burglary trends that led to an arrest. By looking at the entire stream of report data from officers, "she can find commonalties," he said, such as a link between three shooting incidents in different areas that hadn't been recognized.

"A lot of people [at the department] don't have the time to look through the data bases," O'Connor said.

A software program the department hopes to acquire will add a GPS mapping component, she said, allowing her to easily plug data on types of crimes, accidents or other call details into a city map. "This lets us see what is actually happening," she said. "It shows the trends, such as in the accident reports, where we need to improve responses."

Police work is something O'Connor said she had always considered for a career. After graduating from Williams, she enlisted in the Army National Guard and spent two years on active duty, including training in military intelligence, and also learning Korean.

She had noticed on a Williams website that Wynn also graduated from the college, and she asked him about police work, eventually learning that the crime analyst position would be created.

"It's a job that has a whole bunch of aspects," O'Connor said, "and it will not be boring."

She added: "It's engaging, and I am doing something to help the community and people in general, which is something I've always wanted to do."

To reach Jim Therrien: jtherrien@berkshireeagle.com,
or (413) 496-6247
On Twitter: @BE_therrien

Analyzing Non-Criminal Events

September 4, 2013 in Blog by Tiana Antul

As the title Crime Analyst implies, we analyze crime. But what about all the non-criminal events that get reported every day?  If we choose to simply ignore them then we’re leaving out a very significant piece of the entire law enforcement picture and neglecting to fill our professional role.

This is the significance of examining non-criminal events. So let’s get started thinking about how we might be able to make better use of this data.

A quick look at incidents reported in a one hour window yesterday revealed that our Communications center generated 17 incidents during that time. Only three of these incidents involved actual crimes including a sexual assault, a heroin overdose, and a motor vehicle violation.

All the other incidents involved non-criminal events including a verbal domestic dispute, a concerned citizen reporting an open residential door, a motorist in need assistance after his vehicle got stuck where the Sewer Dept. was working to replace a grate, an elderly assist for a woman who had fallen and needed help getting up, and a lot of police-initiated incidents for departmental work like investigations, serving warrants, and serving restraining orders.

Your department is likely to have a similar breakdown of incidents, maybe not so much insofar as the substance of the calls, but as far as the ratio of criminal to non-criminal events. I.e., the overwhelming majority of incidents are probably for non-criminal events.

Making good use of this information might require going above and beyond the traditional role of an analyst. You have the data (and power) to easily identify people in your community who might be at risk, who might benefit from some type of services, or who might show signs of potential to escalate from non-criminal behavior to criminal behavior. Making connections within your Department as well as with other professionals in your community will allow you to help citizens in a very real way, reduce the number of future calls concerning specific people or events surrounding specific circumstances, ultimately freeing up limited police resources. Perhaps you’ll even prevent a crime.

That domestic dispute…can you perform a dangerousness assessment on both parties? Is there anything in either party’s history suggesting risk factors or the potential to escalate? The elder who fell and needed assistance… can you refer her to Elder Services? They likely have a Fall Prevention Program aimed at educating seniors on how to minimize their fall risk. How about the stuck vehicle (which might have caused consequential traffic-related issues during a critical commute time)? The Police and Sewer Departments can work together to prevent such incidents while grates are actively being worked on. What about mental health in your community and its relationship with crime and disorder? Homelessness? How can you use non-criminal events to help alleviate the social factors associated with criminal and non-criminal events?