QAQC: Number of changes reported in QAQC site does not match what's in qc_bbldiffs shapefile.

[mgraber]

@AmandaDoyle When I download the shapefile from the ReadMe, I see 155 features. When I look at the qc_bbldiffs table on the QAQC app, I also see a table with 155 rows. Can you elaborate on this issue? It it a problem with the values, or simply the number of records?

[AmandaDoyle]

I'm stumped, and it looks like the QAQC site is broken.

The question is why do out_bbldiffs.sql and mismatch.sql not align.

In the latest version there are 106 records in qc_bbldiffs but only 50 changes recorded in the mismatch result, while you would think there would be at least 106 mismatches.

Even if I compute the differences for mismatch on the bbldiff subset I still get the same result/

WITH bbl_diff as (    
SELECT
        a.boroughcode,
        a.taxblock,
        a.taxlot,
        a.bbl as bblnew,
        a.zoningdistrict1 as zd1new,
        a.zoningdistrict2 as zd2new,
        a.zoningdistrict3 as zd3new,
        a.zoningdistrict4 as zd4new,
        a.commercialoverlay1 as co1new,
        a.commercialoverlay2 as co2new,
        a.specialdistrict1 as sd1new,
        a.specialdistrict2 as sd2new,
        a.specialdistrict3 as sd3new,
        a.limitedheightdistrict as lhdnew,
        a.zoningmapnumber as zmnnew,
        a.zoningmapcode as zmcnew,
        a.area,
        a.inzonechange,
        b.bbl as bblprev,
        b.zoningdistrict1 as zd1prev,
        b.zoningdistrict2 as zd2prev,
        b.zoningdistrict3 as zd3prev,
        b.zoningdistrict4 as zd4prev,
        b.commercialoverlay1 as co1prev,
        b.commercialoverlay2 as co2prev,
        b.specialdistrict1 as sd1prev,
        b.specialdistrict2 as sd2prev,
        b.specialdistrict3 as sd3prev,
        b.limitedheightdistrict as lhdprev,
        b.zoningmapnumber as zmnprev,
        b.zoningmapcode as zmcprev
    FROM dcp_zoningtaxlots."2021/04/01" a, dcp_zoningtaxlots."2021/03/01" b
    WHERE a.bbl::text = b.bbl::text
        AND (a.zoningdistrict1<>b.zoningdistrict1
        OR a.zoningdistrict2<>b.zoningdistrict2
        OR a.zoningdistrict3<>b.zoningdistrict3
        OR a.zoningdistrict4<>b.zoningdistrict4
        OR a.commercialoverlay1<>b.commercialoverlay1
        OR a.commercialoverlay2<>b.commercialoverlay2
        OR a.specialdistrict1<>b.specialdistrict1
        OR a.specialdistrict2<>b.specialdistrict2
        OR a.specialdistrict3<>b.specialdistrict3
        OR a.limitedheightdistrict<>b.limitedheightdistrict
        OR a.zoningmapnumber<>b.zoningmapnumber
        OR a.zoningmapcode<>b.zoningmapcode
        OR a.zoningdistrict1 IS NULL AND b.zoningdistrict1 IS NOT NULL
        OR a.zoningdistrict2 IS NULL AND b.zoningdistrict2 IS NOT NULL
        OR a.zoningdistrict3 IS NULL AND b.zoningdistrict3 IS NOT NULL
        OR a.zoningdistrict4 IS NULL AND b.zoningdistrict4 IS NOT NULL
        OR a.commercialoverlay1 IS NULL AND b.commercialoverlay1 IS NOT NULL
        OR a.commercialoverlay2 IS NULL AND b.commercialoverlay2 IS NOT NULL
        OR a.specialdistrict1 IS NULL AND b.specialdistrict1 IS NOT NULL
        OR a.specialdistrict2 IS NULL AND b.specialdistrict2 IS NOT NULL
        OR a.specialdistrict3 IS NULL AND b.specialdistrict3 IS NOT NULL
        OR a.zoningmapnumber IS NULL AND b.zoningmapnumber IS NOT NULL
        OR a.zoningmapcode IS NULL AND b.zoningmapcode IS NOT NULL
        OR b.zoningdistrict1 IS NULL AND a.zoningdistrict1 IS NOT NULL
        OR b.zoningdistrict2 IS NULL AND a.zoningdistrict2 IS NOT NULL
        OR b.zoningdistrict3 IS NULL AND a.zoningdistrict3 IS NOT NULL
        OR b.zoningdistrict4 IS NULL AND a.zoningdistrict4 IS NOT NULL
        OR b.commercialoverlay1 IS NULL AND a.commercialoverlay1 IS NOT NULL
        OR b.commercialoverlay2 IS NULL AND a.commercialoverlay2 IS NOT NULL
        OR b.specialdistrict1 IS NULL AND a.specialdistrict1 IS NOT NULL
        OR b.specialdistrict2 IS NULL AND a.specialdistrict2 IS NOT NULL
        OR b.specialdistrict3 IS NULL AND a.specialdistrict3 IS NOT NULL
        OR b.zoningmapnumber IS NULL AND a.zoningmapnumber IS NOT NULL
        OR b.zoningmapcode IS NULL AND a.zoningmapcode IS NOT NULL))
SELECT
sum(CASE WHEN a.zd1new <> a.zd1prev THEN 1 ELSE 0 END) zoningdistrict1,
sum(CASE WHEN a.zd2new <> a.zd2prev THEN 1 ELSE 0 END) zoningdistrict2,
    count(nullif(a.zd2new = a.zd2prev, true)) as zoningdistrict2,
    count(nullif(a.zd3new = a.zd3prev, true)) as zoningdistrict3,
    count(nullif(a.zd4new = a.zd4prev, true)) as zoningdistrict4,
    count(nullif(a.co1new = a.co1prev, true)) as commercialoverlay1,
    count(nullif(a.co2new = a.co2prev, true)) as commercialoverlay2,
    count(nullif(a.sd1new = a.sd1prev, true)) as specialdistrict1,
    count(nullif(a.sd2new = a.sd2prev, true)) as specialdistrict2,
    count(nullif(a.sd3new = a.sd3prev, true)) as specialdistrict3,
    count(nullif(a.lhdnew = a.lhdprev, true)) as limitedheightdistrict,
    count(nullif(a.zmnnew = a.zmnprev, true)) as zoningmapnumber,
    count(nullif(a.zmcnew = a.zmcprev, true)) as zoningmapcode
FROM bbl_diff a;

[AmandaDoyle]

@SPTKL "Total BBL Pairwise Value Mismatch by Version" on the QAQC page should simply show the count of records in the bbl_diff file. Confirmed this with Lynn