Department Table

[huy-d-nguyen]

Hi @ayyubibrahimi , up to now, we don't have any department table in wrgl, we just have the agency field in some table to keep track on the department relation and we will create new department whenever we have a new agency value. We think that later then we need a table to keep track on the departments so that we can remove the duplicated/redundant departments. How do you think about that? Do you agree with us?

[ayyubibrahimi]

Agreed. Thanks for bringing this up!

I planned to talk to @rajivsinclair about this at our next meeting. Currently, on the Django side, the duplication/redundant department is an immediate issue because, for example, news articles are attached to one department (i.e., Evangeline Sheriff's Office) while personnel/allegations data is attached to the duplicate department (i.e., Evangeline SO).

Currently, the agency field is an abbreviation of the official name for the department. For example, the agency names, on the processing side, have been standardized to SO (abbreviation of Sheriff's Office) or PD (abbreviation of Police Department), i.e, Evangeline SO or Hammond PD. These names are in conflict with some of the department names that may be associated with news articles, i..e, Evangeline Parish Sheriff's Office, Evangeline Sheriff's Office, EPSO.

[ayyubibrahimi]

Hey @huy-d-nguyen. Here is the department table that I've come up with. The table includes both the correct slug and name for each department. As far as I know, there are redundant/duplicate departments for all departments, therefore, all redundant departments should be consolidated based on the values in the table below. Is this what you had in mind? Please feel free to let me know if this isn't helpful.

department_name_reference_list.csv

[huy-d-nguyen]

Yes, looks good to us.

[ayyubibrahimi]

Awesome. Please let me know if there is anything else that I can do to help.

[huy-d-nguyen]

Hi @ayyubibrahimi , have you pushed the department data to wrgl? We tend to import the new Department data to remove the duplication soon. So please let us know when the data of the department table is ready.

[ayyubibrahimi]

@huy-d-nguyen here is a link to the wrgl table

[huy-d-nguyen]

Thank you, we will take a look on that.

[baoea]

Hi @ayyubibrahimi, could data team provide fields we need such as city, parish ,address,phone, location_map_url, location? Our importing flow depends on the fields data team provides us. Thanks!

[ayyubibrahimi]

Sure @baoea.

@rajivsinclair @lsukaristone - please advise as to how you generate the location coordinates currently on Django for each agency.

[baoea]

@ayyubibrahimi the current way we get the location coordinates is to google them and then manually input those into the admin page.

[baoea]

Hi @ayyubibrahimi, could you change the agency field in event table and other tables into slug instead of name? Thanks!

[ayyubibrahimi]

Hey @baoea will look into those changes

[baoea]

Thanks @ayyubibrahimi. Since now we have department table, we would like to use slug as department id

[ayyubibrahimi]

@baoea - the department table has been updated on wrgl with location values.

I don't know of any departments, at the moment, which have values for the city, parish ,address, phone, location_map_url, fields that you mentioned above. Is this something that should be prioritized, or is the location sufficient for the immediate future @baoea @rajivsinclair @lsukaristone

[baoea]

@ayyubibrahimi there are some agency_slug with triple dash such as lsu---alexandria-university-pd (LSU - Alexandria University Police Department). I believe we should keep it to be single dash lsu-alexandria-university-pd. slugify library of python generates such result. Thanks!

[ayyubibrahimi]

@baoea agreed. This issue was just addressed in the latest commit to wrgl.

[baoea]

@ayyubibrahimi we check the data and see the dot in agency_slug (for example st.-bernard-so). It should be st-bernard-so. Thanks!

[ayyubibrahimi]

Corrected in the most recent commit. Also, note that the slugs and names have changed for some of the agencies associated with da or the District Attorney's Office

[huy-d-nguyen]

Hi @ayyubibrahimi , we think that there is one missing row in the department table, currently we only have 2 departments related to the feliciana: west and east(see image)

But from the personnel table, we have three, and the missing one is Feliciana SO.

Please take a look on it, and furthermore please let us know when you update all agency column type of all tables to slug field.

[ayyubibrahimi]

@huy-d-nguyen - I'm currently in the process of changing all of the agency column type to represent the slug field in PR #346. This issue should be fixed after this PR is merged. I will keep you updated.

[ayyubibrahimi]

The agency column type in the event table @baoea and personnel table @huy-d-nguyen is now the same as the agency_slug from the department table. Please let me know if there are any issues.

[huy-d-nguyen]

We also see the updated slugs of the table allegation, appeal-hearing, use-of-force. Are those table use the new agency_slug too?

[ayyubibrahimi]

Yes. To avoid redundant departments again, the values have been changed for all relevant tables.

[huy-d-nguyen]

Thank you, we will check those table and continue our import flow soon. We will let you know if we have any issues when we import the data.

[baoea]

@ayyubibrahimi we have issue with some departments that have department in names. In agency table, we have jefferson-fire-department, but it is jefferson-fire-dept in personnel table. Please check it out. Thanks!

[baoea]

@ayyubibrahimi

This should be new-orleans-levee-pd

[ayyubibrahimi]

hey @baoea

1) The personnel table should now have jefferson-fire-department as it's value 2) orleans-levee-pd, as it is in the allegations table is actually the correct slug, therefore, I've made a change to the department table to have orleans-levee-pd as the agency_slug

Please let me know if there are any other issues.

[baoea]

@ayyubibrahimi These agencies below in personnel table that cannot be looked up in agency table Please check other tables also and fix them. Thank you very much in advance!

east-baton-rouge-so medical-center-of-la-no office-of-youth-dev,-dept-of-corrections louisiana-dept-of-justice Abbeville PD delgado-cc-university-pd dept-of-public-safety lafayette-city-park-rec orleans-da orleans-constable louisiana-dept-of-environmental-quality jefferson-1st-court housing-authority-of-no louisiana-dept-motor-vehicle new-orleans-levee-pd orleans-civil-so louisiana-dept-of-revenue louisiana-dept-of-transportation-pd retired baton-rouge-cc-university-pd bossier-cc-university-pd city-park-pd-no jefferson-ist-court dept-of-health-hospitals louisiana-dept-of-insurance 14th-jdc-da lsuhsc-no-university-pd delta-cc-pd-university-pd delhl-pd 5th-jdc-district-attorneys-office d-reserve pox-la-pd xc-stoula-pd 29th-jdc-da louisiana-tech mandbville-pd xavier neveans-pd orleans-coroners-office 42nd-jdc feliciana-so hannon-pd pearl-river-pd-deceased houmap orleans-criminal-district-court houma 25th-jdc river-bridge-pd 40th-jdc se-la-flood-protection-auth-e 3rd-jdc scort-pd probationparoleadult-fulltime nunez-cc-university-pd houma-pl ponciatoula-pd 4th-jdc jeeferson-so louisiana-house-of-rep-sergeant-at-arms oaparish-so nc-oula-pd loyola

[ayyubibrahimi]

@baoea All tables, including the department table have been updated. Please review. Apologies for the lack of standardization as there are still some kinks in the OCR.

[baoea]

@ayyubibrahimi These slugs in personnel table still are not working. Please check. Thanks!

Abbeville PD lafayette-city-park-rec office-of-youth-dev,-department-of-corrections new-orleans-criminal-court lsuhsc-no-university-pd new-orleans-civil-so

[baoea]

@ayyubibrahimi This a good practice for slug. You can find more in here https://docs.djangoproject.com/en/4.1/ref/utils/#django.utils.text.slugify

Also, the comma should not be in slugs because we use slugs for urls

[ayyubibrahimi]

@baoea Thanks for the advice! I've addressed the issues above. Please review and let me know if there are any issues.

[baoea]

@ayyubibrahimi we got some slugs that are in officer and event tables, but not in agency. Please check. Thanks!

broussard-fd vermilion-o my-shal gretnapld sthelena-so ng-oaparish-so

[ayyubibrahimi]

Looks like bad OCR again. Apologies. I'll fix.

[ayyubibrahimi]

@baoea is the officer table the same as the personnel table? If not, not 100% sure as to what the output of the officer table is.

[huy-d-nguyen]

Yes, we refer the officer is the personnel table.

[baoea]

@ayyubibrahimi we still have problem with vermilion-o and broussard-fire-department. Cannot find them in agency table

[ayyubibrahimi]

@baoea still working on it. The bug isn't clear on my side. I'll let you know when it's fixed.

[ayyubibrahimi]

@baoea looks good. Please review.

[baoea]

The slug issue is ok now. Thank you!