Query improvement for assigning zoning districts

create function array_unique_stable(p_input anyarray)
  returns anyarray immutable strict parallel safe 
  language sql
as 
$$
select array_agg(t order by x)
from (
  select distinct on (t) t,x
  from unnest(p_input) with ordinality as p(t,x)
  order by t,x
) t2;
$$
;

WITH 
assign_zonedist as (
    SELECT 
        bbl,
        array_remove(array_unique_stable(ARRAY[
        (coalesce(
            array_agg(zonedist) FILTER(WHERE ROUND(perbblgeom::numeric,2) >= 10 AND row_number=1),
            array_agg(zonedist) FILTER(WHERE row_number=1)
        ))[1],
        (array_agg(zonedist) FILTER(WHERE ROUND(perbblgeom::numeric,2) >= 10 AND row_number=2))[1],
        (array_agg(zonedist) FILTER(WHERE ROUND(perbblgeom::numeric,2) >= 10 AND row_number=3))[1],
        (array_agg(zonedist) FILTER(WHERE ROUND(perbblgeom::numeric,2) >= 10 AND row_number=4))[1]
        ]), null) as zonedists
    FROM (
        SELECT 
            bbl, perbblgeom, row_number,
            (CASE WHEN zonedist in ('BALL FIELD', 'PLAYGROUND', 'PUBLIC PLACE') THEN 'PARK' ELSE zonedist END) as zonedist
        FROM lotzoneperorder
    ) a
    GROUP BY bbl
)
SELECT 
    bbl, 
    zonedists[1] as zonedist1,
    zonedists[2] as zonedist2,
    zonedists[3] as zonedist3,
    zonedists[4] as zonedist4
INTO test
FROM (
    SELECT bbl, (CASE WHEN zonedists[1] = 'PARK' AND zonedists[2] IS NULL then ARRAY['PARK'] ELSE zonedists END) as zonedists
    FROM assign_zonedist
) a;

SELECT 
    test.bbl,
    test.zonedist1, 
    dcp_zoning_taxlot.zoningdistrict1,
    test.zonedist2, 
    dcp_zoning_taxlot.zoningdistrict2,
    test.zonedist3, 
    dcp_zoning_taxlot.zoningdistrict3,
    test.zonedist4, 
    dcp_zoning_taxlot.zoningdistrict4
FROM test 
LEFT JOIN dcp_zoning_taxlot on test.bbl = dcp_zoning_taxlot.bbl
WHERE test.zonedist1 != dcp_zoning_taxlot.zoningdistrict1
OR  COALESCE(test.zonedist2, 'NULL') !=  COALESCE(dcp_zoning_taxlot.zoningdistrict2, 'NULL')
OR  COALESCE(test.zonedist3, 'NULL') !=  COALESCE(dcp_zoning_taxlot.zoningdistrict3, 'NULL')
OR  COALESCE(test.zonedist4, 'NULL') !=  COALESCE(dcp_zoning_taxlot.zoningdistrict4, 'NULL')
NYCPlanning / db-zoningtaxlots

Query improvement for assigning zoning districts #117
