Add a new endpoint to get jurisdiction tree for a plan

[moshthepitt]

Given a certain plan, we would would to get back the jurisdiction tree of the entire hierarchy of jurisdictions that are contained in the plan.

This jurisdiction tree should not include jurisdiction geometries and should have at least these fields, if possible:

• jurisdiction id
• jurisdiction name
• jurisdiction parent id

Proposed data structure

The endpoint should return a flat array of all the jurisdictions with at LEAST these fields

• id
• parentId
• geographic level
• name

Structure counts

To support the planning feature in the web UI, we need to get structure counts for each jurisdiction. It would be nice if this is returned as well.

[moshthepitt]

Related to https://github.com/onaio/reveal-frontend/issues/908

[moshthepitt]

In the reporting DB, we are doing this with two queries - both are materialized views because they are expensive. I'm not sure if they can be combined into one. But might be possible.

Step 1 - Build the jurisdictions tree

This query build the jurisdiction tree in a way that each jurisdiction on the tree has information on where exactly it is on the tree, and the path to get to it from the root.

Step 1 - Build the plan jurisdiction hierarchy

This query gets some assistance from two other tables: the jurisdictions table, and the plan_jurisdictions table (this is a pivot table that stores the many to many relationship between plans and jurisdictions).

The query to get the plan's hierarchy as a materialized view then becomes:

CREATE MATERIALIZED VIEW IF NOT EXISTS plan_hierarchy
AS
SELECT DISTINCT ON (plan_id, jurisdiction_id)
    plan_jurisdiction.plan_id,
    plan_hierarchy.id AS jurisdiction_id,
    plan_hierarchy.parent_id AS jurisdiction_parent_id,
    plan_hierarchy.name AS jurisdiction_name,
    plan_hierarchy.geographic_level AS jurisdiction_geographic_level
FROM plan_jurisdiction
LEFT JOIN lateral (
    SELECT
        jurisdictions.id,
        jurisdictions.parent_id,
        jurisdictions.name,
        jurisdictions.geographic_level
    FROM jurisdictions
    where jurisdictions.id in (
        SELECT unnest(jurisdiction_id || jurisdiction_path)
        FROM jurisdictions_materialized_view
        where jurisdiction_id = plan_jurisdiction.jurisdiction_id
    )
) AS plan_hierarchy ON true;

CREATE UNIQUE INDEX IF NOT EXISTS plan_hierarchy_idx ON plan_hierarchy (plan_id, jurisdiction_id);
CREATE INDEX IF NOT EXISTS plan_hierarchy_plan_idx ON plan_hierarchy (plan_id);
CREATE INDEX IF NOT EXISTS plan_hierarchy_jurisdiction_idx ON plan_hierarchy (jurisdiction_id);

Hopefully this has helpful ideas, would also look feedback on things we can do better.

[moshthepitt]

Sample data for the plan hierarchy is here https://superset.reveal-stage.smartregister.org/superset/slice_json/471?form_data={%22row_limit%22:10}

This is an example for the plan with identifier aadc1c80-23f7-509e-9ca2-1172265c06b9

[
            {
                "plan_id": "aadc1c80-23f7-509e-9ca2-1172265c06b9",
                "jurisdiction_parent_id": "0ddd9ad1-452b-4825-a92a-49cb9fc82d18",
                "jurisdiction_geographic_level": 1,
                "jurisdiction_id": "cec79f21-33c3-43f5-a8af-59a47aa61b84",
                "jurisdiction_name": "ra Luapula"
            },
            {
                "plan_id": "aadc1c80-23f7-509e-9ca2-1172265c06b9",
                "jurisdiction_parent_id": "dfb858b5-b3e5-4871-9d1c-ae2f3fa83b63",
                "jurisdiction_geographic_level": 3,
                "jurisdiction_id": "8d44d54e-8b4c-465c-9e93-364a25739a6d",
                "jurisdiction_name": "ra Kashikishi HAHC"
            },
            {
                "plan_id": "aadc1c80-23f7-509e-9ca2-1172265c06b9",
                "jurisdiction_parent_id": "cec79f21-33c3-43f5-a8af-59a47aa61b84",
                "jurisdiction_geographic_level": 2,
                "jurisdiction_id": "dfb858b5-b3e5-4871-9d1c-ae2f3fa83b63",
                "jurisdiction_name": "ra Nchelenge"
            },
            {
                "plan_id": "aadc1c80-23f7-509e-9ca2-1172265c06b9",
                "jurisdiction_parent_id": "8d44d54e-8b4c-465c-9e93-364a25739a6d",
                "jurisdiction_geographic_level": 4,
                "jurisdiction_id": "fca0d71d-0410-45d3-8305-a9f092a150b8",
                "jurisdiction_name": "ra_ksh_2"
            },
            {
                "plan_id": "aadc1c80-23f7-509e-9ca2-1172265c06b9",
                "jurisdiction_parent_id": "",
                "jurisdiction_geographic_level": 0,
                "jurisdiction_id": "0ddd9ad1-452b-4825-a92a-49cb9fc82d18",
                "jurisdiction_name": "ra Zambia"
            }
        ]

[githengi]

is ra Zambia added to the plan or you are traversing to the root node @moshthepitt

[moshthepitt]

@githengi we are traversing to the root. This plan only has ra_ksh_2 defined for it

[moshthepitt]

This was implemented