{"action":"created","issue":{"url":"https://api.github.com/repos/socrata/opendatanetwork.com/issues/313","repository_url":"https://api.github.com/repos/socrata/opendatanetwork.com","labels_url":"https://api.github.com/repos/socrata/opendatanetwork.com/issues/313/labels{/name}","comments_url":"https://api.github.com/repos/socrata/opendatanetwork.com/issues/313/comments","events_url":"https://api.github.com/repos/socrata/opendatanetwork.com/issues/313/events","html_url":"https://github.com/socrata/opendatanetwork.com/issues/313","id":138548935,"number":313,"title":"crime reports pipeline, take 2: all cities","user":{"login":"zang0","id":1051776,"avatar_url":"https://avatars.githubusercontent.com/u/1051776?v=3","gravatar_id":"","url":"https://api.github.com/users/zang0","html_url":"https://github.com/zang0","followers_url":"https://api.github.com/users/zang0/followers","following_url":"https://api.github.com/users/zang0/following{/other_user}","gists_url":"https://api.github.com/users/zang0/gists{/gist_id}","starred_url":"https://api.github.com/users/zang0/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zang0/subscriptions","organizations_url":"https://api.github.com/users/zang0/orgs","repos_url":"https://api.github.com/users/zang0/repos","events_url":"https://api.github.com/users/zang0/events{/privacy}","received_events_url":"https://api.github.com/users/zang0/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/socrata/opendatanetwork.com/labels/high%20priority","name":"high priority","color":"009800"}],"state":"open","locked":false,"assignee":{"login":"chrismetcalf","id":19971,"avatar_url":"https://avatars.githubusercontent.com/u/19971?v=3","gravatar_id":"","url":"https://api.github.com/users/chrismetcalf","html_url":"https://github.com/chrismetcalf","followers_url":"https://api.github.com/users/chrismetcalf/followers","following_url":"https://api.github.com/users/chrismetcalf/following{/other_user}","gists_url":"https://api.github.com/users/chrismetcalf/gists{/gist_id}","starred_url":"https://api.github.com/users/chrismetcalf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chrismetcalf/subscriptions","organizations_url":"https://api.github.com/users/chrismetcalf/orgs","repos_url":"https://api.github.com/users/chrismetcalf/repos","events_url":"https://api.github.com/users/chrismetcalf/events{/privacy}","received_events_url":"https://api.github.com/users/chrismetcalf/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/socrata/opendatanetwork.com/milestones/11","html_url":"https://github.com/socrata/opendatanetwork.com/milestones/v.data.5","labels_url":"https://api.github.com/repos/socrata/opendatanetwork.com/milestones/11/labels","id":1672793,"number":11,"title":"v.data.5","description":"","creator":{"login":"zang0","id":1051776,"avatar_url":"https://avatars.githubusercontent.com/u/1051776?v=3","gravatar_id":"","url":"https://api.github.com/users/zang0","html_url":"https://github.com/zang0","followers_url":"https://api.github.com/users/zang0/followers","following_url":"https://api.github.com/users/zang0/following{/other_user}","gists_url":"https://api.github.com/users/zang0/gists{/gist_id}","starred_url":"https://api.github.com/users/zang0/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zang0/subscriptions","organizations_url":"https://api.github.com/users/zang0/orgs","repos_url":"https://api.github.com/users/zang0/repos","events_url":"https://api.github.com/users/zang0/events{/privacy}","received_events_url":"https://api.github.com/users/zang0/received_events","type":"User","site_admin":false},"open_issues":27,"closed_issues":1,"state":"open","created_at":"2016-03-29T21:11:48Z","updated_at":"2016-04-19T19:25:36Z","due_on":null,"closed_at":null},"comments":3,"created_at":"2016-03-04T18:29:24Z","updated_at":"2016-04-21T00:44:28Z","closed_at":null,"body":"ticket to cover take 2 of the crime reports pipeline and summary stats. reqs:\r\n\r\n_1 all cities\r\n_2 36 months of history\r\n_3 automated\r\n_4 monitored"},"comment":{"url":"https://api.github.com/repos/socrata/opendatanetwork.com/issues/comments/212672189","html_url":"https://github.com/socrata/opendatanetwork.com/issues/313#issuecomment-212672189","issue_url":"https://api.github.com/repos/socrata/opendatanetwork.com/issues/313","id":212672189,"user":{"login":"chrismetcalf","id":19971,"avatar_url":"https://avatars.githubusercontent.com/u/19971?v=3","gravatar_id":"","url":"https://api.github.com/users/chrismetcalf","html_url":"https://github.com/chrismetcalf","followers_url":"https://api.github.com/users/chrismetcalf/followers","following_url":"https://api.github.com/users/chrismetcalf/following{/other_user}","gists_url":"https://api.github.com/users/chrismetcalf/gists{/gist_id}","starred_url":"https://api.github.com/users/chrismetcalf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chrismetcalf/subscriptions","organizations_url":"https://api.github.com/users/chrismetcalf/orgs","repos_url":"https://api.github.com/users/chrismetcalf/repos","events_url":"https://api.github.com/users/chrismetcalf/events{/privacy}","received_events_url":"https://api.github.com/users/chrismetcalf/received_events","type":"User","site_admin":false},"created_at":"2016-04-21T00:44:28Z","updated_at":"2016-04-21T00:44:28Z","body":"Alright, looking at some of the places missing in the DC area, it looks like a lot of the \"cities\" that are missing there don't actually operate their own police departments. Silver Spring, for example, doesn't have its own police department, and instead the Montgomery County Police Department seems to have jurisdiction over their area. This seems to be pretty common, at least in the DC area.\r\n\r\nFor the new version, my algorithm was:\r\n\r\n- For each agency of \"agency_type=Police Department\" in the moto agencies dataset (https://moto.data.socrata.com/resource/fax2-dw8p):\r\n   - Query the gazeteer dataset (https://odn.data.socrata.com/resource/gm3u-gw57) for top five cities matching a \"$q=agency city name\" search, ordered by the distance from the agency center, to identify what place ID to use\r\n   - Query for aggregated crimes type counts for the current agency and associate them with the place ID of the matched city\r\n\r\nIn Silver Spring's case, their police department is based in Rockville, things didn't match up, so they're missing. Rockville is also 12 miles away from Silver Spring, so just using a simple geo match won't work either.\r\n\r\nI guess it was a bad assumption to make that police departments are actually located in the cities they police. It seems to work better in the wide open spaces we have out west, which is where I was testing.\r\n\r\nUnless we want to just aggregate data by agency jurisdiction, I'm going to have to go back to something similar to what I had before, where we aggregate on crimes occurring within the bounds of a city."}}