CEQR Air Quality: Atypical Roadway

[AmandaDoyle]

What is an Atypical Roadway? A roadway that is either elevated or depressed compared to the surrounding environment. Not on the same level. If site is 200 feet from an Atypical Roadway then this needs to be considered in the analysis.

• [x] Determine best source for extracting Atypical Roadways (LION, Planimetrics, or...?)
• [x] Publish subset of data
• [ ] Create line dataset (convert to roadbed?)

[SPTKL]

https://github.com/CityOfNewYork/nyc-planimetrics/blob/master/Capture_Rules.md Seems like this is something we can potentially extract from planimetrics we can also use the elevation points together with feature code to determine elevated/depressed roadways? https://data.cityofnewyork.us/Transportation/Elevation-points/szwg-xci6/data

[SPTKL]

cscl has roadway type: and we can pull it from Open data https://github.com/CityOfNewYork/nyc-geo-metadata/blob/master/Metadata/Metadata_StreetCenterline.md

[SPTKL]

By definition of atypical roadways: https://www1.nyc.gov/assets/oec/technical-manual/17_Air_Quality_2014.pdf page 19

Projects that would result in placement of operable windows (i.e., windows that may be opened and closed by the tenant), balconies, air intakes, or intake vents generally within 200 feet of an atypical (e.g., not at-grade) source of vehicular pollutants, such as a highway or bridge with a total of more than two lanes.

[SPTKL]

cscl has grade level for street centerlines (for each stretch, the grade level of 'from' and 'to')

[SPTKL]

WITH lion as (
select distinct physicalid::text, nullif(number_total_lanes, '  ')::numeric as number_total_lanes
    from dcp_lion.latest
), cscl_filtered as (
    select *
    from cscl.latest 
    where FRM_LVL_CO!= '13' or TO_LVL_CO!= '13'
), atypical as (
    SELECT full_stree, physicalid, st_width, 
        r_zip, l_zip, borocode, FRM_LVL_CO, TO_LVL_CO,
        number_total_lanes, wkb_geometry 
FROM cscl_filtered left join lion USING (physicalid))
SELECT full_stree, max(number_total_lanes) as number_of_lanes, st_union(wkb_geometry) 
FROM atypical group by full_stree;

or

WITH lion as (
select distinct physicalid::text, nullif(number_total_lanes, '  ')::numeric as number_total_lanes
    from dcp_lion.latest
), cscl_filtered as (
    select *
    from cscl.latest 
    where FRM_LVL_CO!= '13' or TO_LVL_CO!= '13'
)SELECT full_stree, physicalid, st_width, 
        r_zip, l_zip, borocode, FRM_LVL_CO, TO_LVL_CO,
        number_total_lanes, wkb_geometry 
FROM cscl_filtered left join lion USING (physicalid);

note that the first code block has an aggregation by street name, therefore, some of the more granular information such as grade level, zipcode street widths are lost. We can find those info in the second code block.

ogr2ogr -f "ESRI Shapefile" <file_name>.csv PG:"<pg info here>" -sql "<sql statement here>" 

[SPTKL]

Note that we didn't filter by the number of lanes, some segments have number of lanes missing, this needs review https://gist.github.com/SPTKL/afdb70a12094aafbbb0b9ac7eff3811f

[baolingz]

• [x] Remove non-vehicular roads – pedestrian overpasses and bike paths priority
• [ ] Once EARD has verified that we’ve identified the right roads, create buffers

[baolingz]

        SELECT street AS streetname, segmentid, streetwidth_min,streetwidth_max, 
            lzip AS left_zipcode, rzip AS right_zipcode,
            LEFT(StreetCode, 1) AS borocode, nodelevelf, nodelevelt,
            featuretyp, trafdir, nullif(number_total_lanes, '  ')::NUMERIC AS number_total_lanes, 
            trim(bikelane) AS bikelane, wkb_geometry AS geom
        FROM {input_table}
        WHERE ((nodelevelf!= 'M' AND nodelevelf!= '*' AND nodelevelf!= '$')
        OR (nodelevelt!= 'M' AND nodelevelt!= '*' AND nodelevelt!= '$'))
        AND trafdir != 'P'
        AND (nullif(number_total_lanes, '  ')::NUMERIC != 1
        OR nullif(number_total_lanes, '  ')::NUMERIC IS NULL)
        AND trim(bikelane) != '1'
        AND trim(bikelane) != '2'
        AND trim(bikelane) != '4'
        AND trim(bikelane) != '9';