LIS-threshold-queries.md

LIS Issue 17

Narrow query

This query filters the lots included in the 2016/17 expansion. The results are not particularly interesting, since most lots in this collection have a proportion of 1.

 nine | eight | seven | six  | five | four | three | two  | one
------+-------+-------+------+------+------+-------+------+------
 3910 |  3912 |  3912 | 3912 | 3912 | 3912 |  3912 | 3912 | 3914

SELECT 
    COUNT(proportion) filter (WHERE proportion >= 0.9) as nine,
    COUNT(proportion) filter (WHERE proportion >= 0.8) as eight,
    COUNT(proportion) filter (WHERE proportion >= 0.7) as seven,
    COUNT(proportion) filter (WHERE proportion >= 0.6) as six,
    COUNT(proportion) filter (WHERE proportion >= 0.5) as five,
    COUNT(proportion) filter (WHERE proportion >= 0.4) as four,
    COUNT(proportion) filter (WHERE proportion >= 0.3) as three,
    COUNT(proportion) filter (WHERE proportion >= 0.2) as two,
    COUNT(proportion) filter (WHERE proportion >= 0.1) as one
FROM
    (SELECT st_area(st_intersection(st_buffer(zoning.geom, 0), geo.geom))/st_area(geo.geom) AS proportion
    FROM (
            SELECT pin
            FROM (SELECT pin FROM lis 
                    INNER JOIN ihs_audit 
                    ON REPLACE(pin, '-', '')="PIN_NBR" 
                    WHERE status = 'City Owned' 
                    AND "CITY_OWNED_IND" = 'likely_held' 
                    AND ("ACQ_JR_IND" IS NULL 
                    OR "ACQ_JR_IND" = 0) 
                    AND ("NOACQ_JR_IND" IS NULL 
                    OR "NOACQ_JR_IND" = 0) 

                    UNION 

                    SELECT TRIM(pin) FROM added_properties
                ) AS eligible 
            INNER JOIN parcel 
            ON REPLACE(pin, '-', '')=pin14 
            INNER JOIN zoning 
            ON ST_Intersects(parcel.geom, zoning.geom) 
            LEFT JOIN osm_buildings 
            ON ST_Intersects(parcel.geom, osm_buildings.wkb_geometry) 
            LEFT JOIN building_permits 
            ON ST_Intersects(parcel.geom, building_permits.wkb_geometry) 
            WHERE issue_date IS NULL 
            GROUP BY pin 
            HAVING bool_and(zone_type=4) 
            AND bool_and(osm_id is null) 

            UNION 

            SELECT TRIM(pin) 
            FROM manually_vetted 
            INNER JOIN lis 
            USING(pin) 
            INNER JOIN parcel 
            ON REPLACE(TRIM(pin), '-', '')=pin14 
            INNER JOIN zoning 
            ON ST_Intersects(parcel.geom, zoning.geom) 
            GROUP BY TRIM(pin) 
            HAVING bool_and(zone_type=4) 

            UNION 

            SELECT pin FROM hard_add
    ) AS land_use 

    INNER JOIN parcel AS geo 
    ON REPLACE(pin, '-', '')=pin14 
    INNER JOIN expansion_boundaries 
    ON ST_Intersects(geo.geom, expansion_boundaries.geom) 
    INNER JOIN zoning 
    ON ST_Intersects(geo.geom, zoning.geom) 
    LEFT JOIN excluded_properties 
    ON substring(land_use.pin, 1, 13)=substring(excluded_properties.pin, 1, 13) 
    LEFT JOIN sold_from_previous_round 
    ON replace(land_use.pin, '-', '') = sold_from_previous_round.pin14::text 
    WHERE excluded_properties.pin IS NULL 
    AND sold_from_previous_round.pin14 IS NULL) as lots_expansion;

Broader query

This query filters from the parcels in the IHS audit which qualify as eligible for the Large Lots program: PINs that are owned by the city, PINs that are "likely held" (status assigned from IHS), and PINs that do not have a resolution or judgment as part of its history. (N.B. The query takes over an hour to run.) These results suggest that an adjusted threshold only has meaningful results if it is below 0.1. That is, the area of some properties intersect with ten percent or less of residential zoning areas (type 4), but most intersect with ninty percent or more.

  1   | 0.9  | 0.8  | 0.7  | 0.6  | 0.5  | 0.4  | 0.3  | 0.2  | 0.1  |  0
------+------+------+------+------+------+------+------+------+------+------
 6087 | 6219 | 6228 | 6231 | 6235 | 6246 | 6258 | 6266 | 6272 | 6282 | 6621

SELECT 
    COUNT(proportion) filter (WHERE proportion >= 1) as "1",
    COUNT(proportion) filter (WHERE proportion >= 0.9) as "0.9",
    COUNT(proportion) filter (WHERE proportion >= 0.8) as "0.8",
    COUNT(proportion) filter (WHERE proportion >= 0.7) as "0.7",
    COUNT(proportion) filter (WHERE proportion >= 0.6) as "0.6",
    COUNT(proportion) filter (WHERE proportion >= 0.5) as "0.5",
    COUNT(proportion) filter (WHERE proportion >= 0.4) as "0.4",
    COUNT(proportion) filter (WHERE proportion >= 0.3) as "0.3",
    COUNT(proportion) filter (WHERE proportion >= 0.2) as "0.2",
    COUNT(proportion) filter (WHERE proportion >= 0.1) as "0.1",
    COUNT(proportion) filter (WHERE proportion >= 0) as "0"
FROM (
    SELECT st_area(st_intersection(st_buffer(zoning.geom, 0), parcel.geom))/st_area(parcel.geom) AS proportion
    FROM (
            SELECT pin FROM lis 
                    INNER JOIN ihs_audit 
                    ON REPLACE(pin, '-', '')="PIN_NBR" 
                    WHERE status = 'City Owned' 
                    AND "CITY_OWNED_IND" = 'likely_held' 
                    AND ("ACQ_JR_IND" IS NULL 
                    OR "ACQ_JR_IND" = 0) 
                    AND ("NOACQ_JR_IND" IS NULL 
                    OR "NOACQ_JR_IND" = 0)
        ) AS eligible 

        INNER JOIN parcel 
        ON REPLACE(pin, '-', '')=pin14 
        INNER JOIN zoning 
        ON st_intersects(st_buffer(zoning.geom, 0), parcel.geom) WHERE zone_type=4
    ) as lots_expansion;