No geography for non-DOB clusters

[AmandaDoyle]

SL reported that about 70% non-DOB clusters do not have a geometry.
Examples of records that don't have a geometry 2021M0107 2021Q0106 2018Q0484 P2016X0409

[AmandaDoyle]

Probably won't fix everything but ON a.dcp_ulurpnumber = b.ulurpno here should be changed to upper(b.dcp_ulurpnumber) = upper(a.ulurpno). As is no records are returned. If changed to force to upper there are 32 matches.

[AmandaDoyle]

From the list SL provided 2021Q0106 and P2016X0409 have a geometry in dcp_application and geom is not assigned with upper fix above In dcp_application 11532 of 34561 (33% do not have a geometry) And all 4 records referenced above have a geometry in dcp_knownprojects

[AmandaDoyle]

Is it an issue with NULLIF statements? The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1. Why would we want to return a NULL geometry? Should the logic be updated to:

WITH
-- Assigning Geometry Using BBL
geom_pluto as (
    SELECT
        a.record_id,
        ST_Union(b.wkb_geometry) as geom
    FROM(
        SELECT 
            a.record_id,
            b.dcp_bblnumber as bbl
        from _dcp_application a
        LEFT JOIN dcp_projectbbls b
        ON a.record_id = trim(split_part(b.dcp_name, '-', 1))
        WHERE b.statuscode != '2'
    ) a LEFT JOIN dcp_mappluto_wi b
    ON a.bbl::numeric = b.bbl::numeric
    GROUP BY a.record_id
),
-- Assigning Geometry Using Previous version of KPDB
geom_kpdb as (
    SELECT 
        a.record_id,
        **case 
                 when b.geometry::geometry is NULL 
        then a.geom
                 else b.geometry::geometry
                end as geom**
    FROM geom_pluto a
    LEFT JOIN dcp_knownprojects b
    ON a.record_id = b.record_id
),
-- Assigning Geometry Using Zoning Map Amendments
geom_ulurp as (
SELECT 
    a.record_id,
        **case
        when st_union(b.wkb_geometry) is  NULL
    then a.geom
        else null
        end as geom**
FROM(
    select 
        a.record_id,
        a.geom,
        b.dcp_ulurpnumber
    FROM (
        select
            a.record_id,
            a.geom,
            b.dcp_projectid
        from geom_kpdb a
        LEFT JOIN dcp_projects b
        on a.record_id = b.dcp_name
    ) a LEFT JOIN dcp_projectactions b
    ON a.dcp_projectid = b._dcp_project_value
) a LEFT JOIN dcp_zoningmapamendments b
ON a.dcp_ulurpnumber = b.ulurpno
GROUP BY a.record_id, a.geom
)
-- Main table with the geometry lookup
SELECT a.*, b.geom
INTO dcp_application
FROM _dcp_application a
LEFT JOIN geom_ulurp b 
ON a.record_id = b.record_id;

This logic changes the number of records with a NULL geometry to 10686 in dcp_application, and all 4 sample records have a geometry.

[AmandaDoyle]

@mbh329 I'm satisfied with the above logic if you want to test on a feature branch.

You'll need to remove the ** I tried to make the parts that I changed bold, but it didn't work