RH SO Update Location List

rafinkanisa commented 2 years ago

Is your feature request related to a problem? Please describe. I'm communicating with the Somalia focal person and he told me that they would like to modify the list of locations in ReportHub. Therefore, we'll have to update the location list in Somalia.

Describe the solution you'd like Update the location list of ReportHub in Somalia

Acceptance Criteria

[ ] The updated list is showing in the Activity Plan at Target Location section of ReportHub in Somalia
[ ] There is no bugs occurring in other countries (AF, NG, ET) after updating the location list (check the Target Location in each countries)

What to do

[x] Send the current location list to the focal person in Somalia
[x] Received the returned modified list file from the focal person
[x] Analyze and confirm the missing part
[ ] Compile the CSV file
[ ] Update the list accordingly to the system

Describe alternatives you've considered No alternatives

Additional context Expected date for this update to be used in February Please be careful when updating.

rafinkanisa commented 2 years ago

I have sent the location list at 17 Jan 2022

rafinkanisa commented 2 years ago

I've just received the list of districts from the focal person. I've analyzed it and it missing the pcode for the newly added district. I need to make sure whether the boundaries are changing or not so I'm currently requesting them to provide me the shapefile. I've checked in https://data.humdata.org/dataset/somalia-administrative-boundaries I could found the pcode but not sure if we could use that data or not. Still waiting for their confirmation

Once it is confirmed we'll update the location in SO.

Before importing the list into the system, we'll have to process it using postgres. Since we have a very tight deadline, @dodiws will be supporting in this regard. After we got the processed data, @fakhrihawari will import it into the system.

@dodiws please do some research about the afghanistan location used in RH. In AF, we have also updated the location previously. If the case is the same, I would like you to ensure that updating the SO location data wouldn't crash something on the platform. In AF, if the old code is used in the report, then there's tag (Old) in the filter 5W Dashboard.

For all of the new Project plan will use the new pcode.

If by chance, the boundaries is not changing, so the latitude, longitude and everything else is the same as the one used by the system I think, adding the district would be far more easier coz we don't have to worry about other locations.

To do list :

[x] Give list of projects in SO that used Banadir district (inform me about project_id, email, focal point name)
[ ] Give list of projects in SO that used Juba Dhexe, Juba Hoose, Shabelle Dhexe, Shabelle Hoose province (inform me about project_id, email, focal point name)
[ ] Inform the focal person in SO about those list so he could inform their partner to change to the new district list
[x] Identify all of collections need to be updated if we update the province name
[x] Compare the points of SO location which given by the focal person with the processed shapefile (determine if it's exact same or not)

Do the following if the lat, long & everything else is EXACTLY same!

[x] Change the province name accordingly (highlighted in orange)
[x] Remove Banadir district but do not remove report that used Banadir district
[x] Add the new districts list into the system

Acceptance Criteria

[x] New project plan could only choose the new province name and the newly added district
[x] If the target location in activity plan is being edited, the province, district shows the new location list
[x] All of old province name has changed to the new one

fakhrihawari commented 2 years ago

The new lists for location were already updated last week on 28 Jan 2022

dodiws commented 2 years ago

Compare lat long values

The ngmReportHub.admin2 Somalia lat long values are the same with lat long calculated from shapefile in https://data.humdata.org/dataset/somalia-administrative-boundaries. Steps to compare the values:

Import Som_Admbnda_Adm2_UNDP.shp shapefile to postgres:

shp2pgsql Som_Admbnda_Adm2_UNDP.shp admin.so_admin_2_import_hdx | psql -h localhost -d immap_afg -U ngmadmin

Open psql:

psql -h localhost -d immap_afg -U ngmadmin

In psql:

-- calculate lat long, store result to admin.so_admin_2_hdx table
DROP TABLE IF EXISTS admin.so_admin_2_hdx;
CREATE TABLE admin.so_admin_2_hdx AS
  SELECT 
      admin2Pcod as admin2pcode,
      admin2Name as admin2name,
      ST_X(ST_PointOnSurface(geom)) as admin2lng,
      ST_Y(ST_PointOnSurface(geom)) as admin2lat
  FROM admin.so_admin_2_import_hdx;

-- export admin.so_admin_2_hdx table to so_admin_2_hdx.csv
\copy (SELECT * FROM admin.so_admin_2_hdx) To 'so_admin_2_hdx.csv' With CSV DELIMITER ',' HEADER

-- exit psql
\q

In mongodb compass export ngmReportHub.admin2 somalia {admin0pcode:'SO'} to csv. Compare the exported lat long values with values from so_admin_2_hdx.csv using Excel.

Import new Banadir data

Store new Banadir data in admin2-so-insert.csv without _id column, then import

mongoimport -d ngmReportHub -c admin2 --headerline --type csv --file admin2-so-insert.csv

Update `admin2` and `adminsite` data

Update manually using mongo shell by @fakhrihawari since mongoimport --mode merge only works in mongo shell version 4.2 but not on version 3.0, mongoimport --upsert also does not work although mentioned in documentation.

Update `admin1name` in collections

Using find_so_banadir.js.zip script to find and update admin1name in collections.

Usage:

// open mongo shell
mongo localhost:27017/ngmHealthCluster find_so_banadir.js --shell

// find using old_admin1name_criterias in collections
show_in_collections(dbs, old_admin1name_criterias)

// update admin1name in six collection
update_admin1name_six_collection()

// check update result
show_in_collections(dbs, old_admin1name_criterias)

iMMAP / ngm-reportDesk