Supply Data to FOM Business Team - manual extract

[basilv]

The FOM business team is requesting access to FOM data, including standard FOM attributes, comment count statistics, and spatial object statistics. This data is needed by late January at the latest for a short-term need, and is likely needed on a quarterly basis going forward. The primary immediate business purpose of this data is to help respond to questions about the reported lack of public comments for some FOMs.

Multiple solutions are possible. For this ticket, we're looking at doing some sort of manual data extract in CSV format to meet their immediate needs and validate that we are providing the right data. In a subsequent ticket we can look at providing a longer-term solution (*)

Data attributes/statistics required (**):

• FSP ID
• Forest Client
• District
• FOM Name
• Count of comments
• Count of comments per resolution category
• Count of spatial objects (overall and/or by type)
• Overall spatial area (bounding box?) covered by these spatial objects

The business also asked for additional attributes (like type of tenure, something like that) that appear to be available in FSPTS, but isn't available in FOM. So for this we're just planning to provide the FSP ID of each FOM, and the business will need to join with FSPTS data to get these other fields.

(*) Longer-term solutions could include allowing business to pull a data extract via the FOM API, or to email out an extract periodically to the business, or more fancy building a BI / analytics environment that users can access.

(**) We will likely provide other attributes that are easy to provide, like FOM ID, created date, comment open/close date, etc.

[webgismd]

I see maybe a data model updates to capture these metrics? and something like Metabase? ie like the Optimize Team - https://iitco-metabase.apps.silver.devops.gov.bc.ca/browse/2 (or PSU, Mines, and other teams use for an easy analytics dashboard on top of postgres..

[basilv]

Preliminary query, work in progress...
(remove where clause and limit, need to validate spatial area and whether that's useful, maybe convert to km^2 or ha, need to show comment counts per response category, maybe show spatial object count for cutblocks vs roads - do the rentenion areas matter)

SELECT
p.project_id as "FOM ID",
p.name AS "FOM Name",
p.workflow_state_code as "Status",
p.fsp_id AS "FSP ID",
fc.name AS "Forest Client Name",
d.name AS "District Name",
(select count(1) from app_fom.public_comment pc where pc.project_id = p.project_id) as "Comments Count",
(select count(1) from app_fom.spatial_feature sf where sf.project_id = p.project_id) as "Spatial Objects Count",
(select ST_Area(ST_Envelope(ST_Collect(cb.geometry))) from app_fom.cut_block cb join app_fom.submission s on cb.submission_id = s.submission_id where s.project_id = p.project_id) as "Cutblock Bounding Area m^2",
(select ST_Area(ST_Envelope(ST_Collect(rs.geometry))) from app_fom.road_section rs join app_fom.submission s on rs.submission_id = s.submission_id where s.project_id = p.project_id) as "Road Section Bounding Area m^2"

FROM
app_fom.project p
LEFT JOIN
app_fom.forest_client fc ON p.forest_client_number = fc.forest_client_number
LEFT JOIN
app_fom.district d ON p.district_id = d.district_id
WHERE
p.workflow_state_code = 'COMMENT_CLOSED'
LIMIT 1;

[basilv]

Latest version of query, probably pretty close:
SELECT
p.project_id as "FOM ID",
p.name AS "FOM Name",
p.workflow_state_code as "Status",
p.fsp_id AS "FSP ID",
fc.name AS "Forest Client Name",
d.name AS "District Name",
(select count(1) from app_fom.public_comment pc where pc.project_id = p.project_id) as "Comments Count",
(select count(1) from app_fom.public_comment pc where pc.project_id = p.project_id and response_code = 'ADDRESSED') as "Addressed Comments Count",
(select count(1) from app_fom.public_comment pc where pc.project_id = p.project_id and response_code = 'CONSIDERED') as "Considered Comments Count",
(select count(1) from app_fom.public_comment pc where pc.project_id = p.project_id and response_code = 'IRRELEVANT') as "Irrelevant Comments Count",
(select count(1) from app_fom.public_comment pc where pc.project_id = p.project_id and response_code is null) as "No Response Comments Count",
(select count(1) from app_fom.spatial_feature sf where sf.project_id = p.project_id) as "Spatial Objects Count",
(select ST_Area(ST_Envelope(ST_Collect(cb.geometry))) from app_fom.cut_block cb join app_fom.submission s on cb.submission_id = s.submission_id where s.project_id = p.project_id) as "Cutblock Bounding Area m^2",
(select ST_Area(ST_Envelope(ST_Collect(rs.geometry))) from app_fom.road_section rs join app_fom.submission s on rs.submission_id = s.submission_id where s.project_id = p.project_id) as "Road Section Bounding Area m^2"
FROM
app_fom.project p
LEFT JOIN
app_fom.forest_client fc ON p.forest_client_number = fc.forest_client_number
LEFT JOIN
app_fom.district d ON p.district_id = d.district_id
;

[basilv]

To get the extract - start openshift command line, establish port fowarding e.g.
oc port-forward fom-prod-db-85f4444b94-s5pb5 6000:5432

Then use pg-admin to connect to database (e.g. localhost:6000), run query and save results to file.