qaqc updates

[SPTKL]

• [x] Combine qc_versioncomparison.csv with qc_versioncomparisonnownullcount.csv; sort by field name. (Resulting report will show if the value changed to a different value or to/from a null value).
• [x] Add a difference column to qc_frequencychanges.csv; sort by field name. Add two fields to the BBL diff report
• [ ] Flag indicating that lot intersects with a rezoning done since the last version
• [ ] Flag indicating that the area of the lot (taken from DTM) has changed by more than +/- 10% since the last version
• [x] Rename fields in BBL diff report for the fields showing the new data, using similar naming convention as used for previous data set, e.g., ZD1NEW.

[SPTKL]

SELECT field, count as diff_count, 
percent as diff_percent,  
        newnullcount, oldnullcount, 
        countnew as total_count_new, 
        countold as total_count_old, 
        (countnew-countold) as total_count_diff 
from(
    SELECT * FROM
        ztl_qc_versioncomparisoncount a 
    JOIN 
        ztl_qc_versioncomparisonnownullcount b
    USING (field)) c
JOIN
frequencychanges d
USING (field)
ORDER BY field;

[AmandaDoyle]

For Flag indicating that the area of the lot (taken from DTM) has changed by more than +/- 10% since the last version this should be its own report and process since it'll be useful for ZTLDB, PLUTO, and PAD updates

[SPTKL]

with dtm_compare as (
    SELECT bbl, geom_new, geom_old, 
        (case when geom_new = geom_old then 0 else 1 end) flag FROM 
    (select bbl, ST_Multi(ST_Union(f.wkb_geometry)) as geom_new FROM dof_dtm."2019/09/03" f GROUP BY bbl ) a
    JOIN 
    (select bbl, ST_Multi(ST_Union(f.wkb_geometry)) as geom_old FROM dof_dtm."2019/08/23" f GROUP BY bbl ) b
    USING(bbl))
, changed as (
Select *, (st_area(geom_new)-st_area(geom_old))/st_area(geom_old) as area_diff from dtm_compare
where flag = 1)
select * from changed where area_diff > 0.1 or area_diff < -0.1;

[github-actions[bot]]

Stale issue message