opensrp / opensrp-client-reveal

OpenSRP Client Reveal App
Other
7 stars 8 forks source link

Check for duplicate Operational areas #1461

Closed Rkareko closed 3 years ago

Rkareko commented 3 years ago

Check for instances of duplicate OAs in the Eswatini hierarchy

Rkareko commented 3 years ago

Run the following query to get OAs with the same name

select count(*), name
 from core.location_metadata 
 where  geojson_id IN (

            WITH RECURSIVE locations
            AS
            (
                SELECT lm1.geojson_id, lm1.parent_id, lm1.location_id, lm1.name, 0 as level
                FROM core.location_metadata lm1 WHERE geojson_id = 'bac41d87-b09d-4e76-be3b-3b6d07399891'  AND lm1.status IN ('ACTIVE', 'PENDING_REVIEW')
                UNION
                SELECT lm2.geojson_id, lm2.parent_id, lm2.location_id, lm1.name, level+1 as level
                FROM locations lm1
                INNER JOIN core.location_metadata lm2 ON lm2.parent_id = lm1.geojson_id AND lm2.status IN ('ACTIVE', 'PENDING_REVIEW')
            )
            SELECT geojson_id FROM locations

 ) group by name HAVING count(*) > 1;

Results

count | name
-------+------------- 2 | Hhohho 2 | Shiselweni 2 | Siphumelele

The OAs have the following properties


select * from core.location_metadata where name in ('Hhohho', 'Shiselweni', 'Siphumelele');
 id   | location_id |              geojson_id              | type |              parent_id               | uuid |  status  | server_version |    name     | version |        date_created        |        date_edited         
-------+-------------+--------------------------------------+------+--------------------------------------+------+----------+----------------+-------------+---------+----------------------------+----------------------------
  6246 |        6246 | c1ea63ee-b6b8-41a6-bd47-0ac8e8a47379 |      | 445285d8-72f3-44f1-9b78-fe4935930e66 |      | ACTIVE   |  1591000367562 | Siphumelele |       0 | 2020-10-08 15:40:18.288897 | 2020-10-08 15:40:18.288897
  6247 |        6247 | f987f12c-fee6-4131-97ed-997e4efa35eb |      | 445285d8-72f3-44f1-9b78-fe4935930e66 |      | ACTIVE   |  1591000367563 | Siphumelele |       0 | 2020-10-08 15:40:18.288897 | 2020-10-08 15:40:18.288897
  6309 |        6309 | 3ea27b09-96fe-4be5-b29f-490d63275ace |      | bac41d87-b09d-4e76-be3b-3b6d07399891 |      | ACTIVE   |  1591000611766 | Shiselweni  |       0 | 2020-10-08 15:40:18.288897 | 2020-10-08 15:40:18.288897
  6327 |        6327 | 0990ee63-c5a6-43ab-be08-f5dbcd9a1556 |      | 3ea27b09-96fe-4be5-b29f-490d63275ace |      | ACTIVE   |  1591000735349 | Shiselweni  |       0 | 2020-10-08 15:40:18.288897 | 2020-10-08 15:40:18.288897
 45594 |       38592 | fc5e4d8c-6ada-480e-80bc-c8c0076ad27a |      | bac41d87-b09d-4e76-be3b-3b6d07399891 |      | ACTIVE   |  1596651440576 | Hhohho      |       1 | 2020-10-08 15:40:18.288897 | 2020-10-08 15:40:18.288897
  6286 |        6286 | fc5e4d8c-6ada-480e-80bc-c8c0076ad27a |      | bac41d87-b09d-4e76-be3b-3b6d07399891 |      | INACTIVE |  1596651562207 | Hhohho      |       0 | 2020-10-08 15:40:18.288897 | 2020-10-08 15:40:18.288897
Rkareko commented 3 years ago

The query has some rough edges that's why Hhohho is appearing. This location is not duplicated. Two different locations with different parents share the same name i.e Shiselweni . Siphumelele - two location sharing the same name and same parent id but different ids. We have updatedthe location with id c1ea63ee-b6b8-41a6-bd47-0ac8e8a47379 to be inactive

@Naima-Bashir what course of action should be taken for Shiselweni

madrinekariuki commented 3 years ago

@Rkareko @Naima-Bashir Siphumelele now appearing once see screenshot below.

image