[ddj] Open data murder tracker in Trinidad and Tobago

Tue Feb 4 07:34:34 UTC 2014

Hi Gerard,

In Egypt we have a website (http://wikithawra.wordpress.com/) counting the
number of death (by police and army) since the Egyptian revolution in 2011,
they use a Google Spreadsheet for that.

https://docs.google.com/spreadsheet/ccc?key=0ApHKfHFs3JYxdGQybmtTVk42bEtxemNjZldJcWxjbnc&usp=drive_web&pli=1#gid=0

For the two problems you mentioned they do the following:

   - Separate record (row) for each person, so for example if 20 persons
   died in the same incident there will be 20 rows, one for each of them.
      - This is to enable them to add the victim's name in each row, also
      for the row count to reflect the actual number of deaths.

   - In each row there are two fields among others, one for the date of
   incident and one for the death, as in some cases, people die days after
   they have been attacked by the police. In your case it can be a field for
   the date the body is discovered and other by the actual date of death.

For them they keep the ID's a a serial number starting from one, since the
date, but as you said since you want to also track the number of killings,
then an additional column with a unique reference number for each incident
should solve your other of counting the number of incidents, as a pivot
table in this case on the incidents ID's will help counting the number of
incidents.

On Fri, Jan 31, 2014 at 8:09 PM, Gerard Best <gerardbest at gmail.com> wrote:

> Hello all,
>
> INTRO
> I'm on a team working on a open data journalism project called Bullet
> Points and I'm hoping you can lend your collective experience/expertise to
> the project.
>
> Bullet Points tracks murders in Trinidad and Tobago. The murders are
> tracked on a Google doc here: http://tinyurl.com/bulletpoints2014
>
> The fatal incidents are listed chronologically and are numbered by victim
> (murder toll). The victims are announced on Twitter (
> twitter.com/bulletpoints_) by their toll number. This system has some
> serious limitations.
>
> PROBLEM
> Firstly, it is geared toward tracking the murder victims but is an
> inadequate way of tracking the actual killings themselves. For example,
> when there is a double murder, the toll goes up by two but the number of
> incidents goes up by only one.
>
> Secondly, killings do not always occur in the same chronological order
> that the victims' bodies are discovered. Sometimes bodies of murder victims
> are discovered after several days. When this happens, the relevant killing
> must be inserted somewhere in the middle of the existing chronological
> list, which obviously changes the toll count of all subsequent victims.
> Unfortunately, because our current system relies on the toll count to
> identify the victim, it has now run into this serious limitation.
>
> PROPOSED SOLUTION
> I think that what I need to do is add another column that contains a
> unique reference number for each incident. The unique reference number can
> follow a standard protocol such as [DATE][INTEGER]. For example, the most
> recent murder, which was discovered today, would have a reference number
> 2014013001. I think this will deal with the problem but I am not sure it is
> the best solution.
>
> Thanks for taking the time to read this. If you think you can help, I
> would be grateful for your perspective on this problem and proposed
> solution.
>
> --
> ---
> Gerard Best
>
> _______________________________________________
> data-driven-journalism mailing list
> data-driven-journalism at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/data-driven-journalism
> Unsubscribe: https://lists.okfn.org/mailman/options/data-driven-journalism
>
>

-- 
Best Regards
Tarek Amr

http://tarekamr.appspot.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/data-driven-journalism/attachments/20140204/f359ba0b/attachment-0003.html>