Agency Deduplication via Meta URLs

[maxachis]

Increased focus on meta urls has given me an opportunity to note cases where the same meta URL is shared by multiple agencies.

In at least some of the cases, this suggests that some of our agencies may be too granular and need consolidated.

Given the large number of cases, we'd probably want to do this in some form of semi-automated fashion -- full manual deduplication would be asking a lot.

Metrics

Duplicates by Agency ID Count

• 996 Meta URLs have two or more Agency IDs
• 159 have 3 or more
• 59 have 4 or more
• 29 have 5 or more
• 14 have 6 or more
• 5 have 7 or more

Number of Agencies Affected

• There are 2276 approved agencies sharing two or more meta URLs, out of 22,782 approved agencies and 16223 distinct Meta URLs

Queries

Find Meta URLs Shared by Multiple Agencies

select
    amu.url,
    count(amu.agency_id) as "Agency Count",
    count(distinct lal.location_id) as "Location Count",
    array_agg(amu.agency_id) as "Agency IDs",
    array_agg(distinct lal.location_id) as "Location IDs"
from
    agency_meta_urls amu
    left join link_agencies_locations lal on lal.agency_id = amu.agency_id
group by
    url
order by
    count(lal.agency_id) desc

Get Data Count and Locations Associated with Agencies

select
    agencies.name,
    count(data_sources.id) as data_sources_count,
    array_agg(distinct link_agencies_locations.location_id) "Associated Locations"
    from agency_meta_urls
    join agencies on agencies.id = agency_meta_urls.agency_id
    left join link_agencies_data_sources on link_agencies_data_sources.agency_id = agencies.id
    left join data_sources on data_sources.id = link_agencies_data_sources.data_source_id
    left join link_agencies_locations on link_agencies_locations.agency_id = agencies.id
-- Below using the agencies for http://www.miami-police.org/index.asp as an example
where agency_meta_urls.agency_id in (5386,5402,5403,5407,5412,5418,5420,5422,5424,5427,5428,5429,5430,5441,5443,5449,5450)
group by agencies.name

Notable Cases

http://www.miami-police.org/index.asp (17 agencies)

Of these, only Agency ID 5386 (Miami Police Department) has data sources (24, to be precise). It is associated with one location (5746 - Miami Beach, Miami-Dade, Florida)

The remainder have names that are variations of "Miami Police Department" ( "Miami Police Department - Overtown NET", "Miami Police Department - Marine Patrol", etc.), have 0 data sources, and are all associated with a single different location (8056 - Miami, Miami-Dade, Florida).

https://www.riversideca.gov/rpd/ (10 agencies)

Of these, only Agency ID 3904 (Riverside Police Department) has data sources (3).

The remainder are again variations on the name ("Riverside Police Department - Arlanza Police Center", "Riverside Police Department UNET", etc.)

All are associated with location ID 5047 (Riverside, Riverside, California)

http://baltimorecountymd.gov/police (10 Agencies)

None have data sources. All are variations of the "Baltimore County Police Department Precinct {#} - {Name}.

Each agency has its own location. (3183,5434,5603,7757,8898,8964,9782,10255,14578,15795)

[josh-chamberlain]

@maxachis this is a nice way to find duplicate agencies! There are indeed many cases where an agency will be split into many sub-agencies, which need to have their sources moved to the primary agency and be summarily deleted.

It is indeed a large list, but I think automation will be just as cumbersome to develop as it would be to simply sit down and work through them. I can do that. Automation could help us find a list of agencies which might be duplicates by checking for things like - –  — division, or those which have similar names, and put them at the top of the list. I would then generate a sheet that looks like this with all our agencies, for processing:

id	name	location_id	location_pretty_name	merge_into_agency_id
123	Pittsburgh Bureau of Police	1234	Pittsburgh	to be entered by me

Then, you could take care of this merge as a one-time operation using our API.

[maxachis]

@josh-chamberlain Sounds good! I will put this ball in your court 🏒.

[josh-chamberlain]

@maxachis oops, I let this slip through the cracks—I was thinking you were going to generate me a list of agencies to comb through, but I can probably manage it.