CEQR Air: corrections to make after QAQC

[AmandaDoyle]

dep_cats_permits

• [x] make values in geo_bbl NULL and not 0 ( or can we not do this because the field is a bigint?)

atypical_roadways

• [x] don't include records where FeatureTyp = 1

nysdot_functional_class

• [x] There is a segment showing up in Albany. Remove the segment from the dataset. It has urban_area_code 63217 and borough 'Queens,' which is probably causing the problem

QA/QC reports

• [x] Generate report that reports the geocoding / mapping success rate for each CEQR dataset
• [x] Output the records that are part of the target subset that did not geocode for each dataset, including grc code

[baolingz]

WITH qaqc AS(
SELECT 'dep_cats_permits' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM (SELECT *, NULL as geo_grc, NULL as geo_message FROM dep_cats_permits.latest UNION ALL SELECT * FROM dep_cats_permits.geo_rejects) AS a
UNION ALL
SELECT 'nysdec_state_facility_permits' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM (SELECT *, NULL as geo_grc, NULL as geo_message FROM nysdec_state_facility_permits.latest UNION ALL SELECT * FROM nysdec_state_facility_permits.geo_rejects) AS b
UNION ALL
SELECT 'nysdec_title_v_facility_permits' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM (SELECT *, NULL as geo_grc, NULL as geo_message FROM nysdec_title_v_facility_permits.latest UNION ALL SELECT * FROM nysdec_title_v_facility_permits.geo_rejects) AS b
UNION ALL
SELECT 'dot_traffic_cameras' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM dot_traffic_cameras.latest
UNION ALL
SELECT 'atypical_roadways' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM atypical_roadways.latest
UNION ALL
SELECT 'facilities_garages' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM facilities_garages.latest
UNION ALL
SELECT 'nysdec_air_monitoring_stations' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdec_air_monitoring_stations.latest
UNION ALL
SELECT 'nysdot_functional_class' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_functional_class.latest
UNION ALL
SELECT 'nysdot_aadt' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_aadt.latest
UNION ALL
SELECT 'dcp_areas_of_concern' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_functional_class.latest
UNION ALL
SELECT 'tunnel_ventilation_towers' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_functional_class.latest
UNION ALL
SELECT 'nysdot_traffic_counts_2015' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_traffic_counts."2015"
UNION ALL
SELECT 'nysdot_traffic_counts_2016' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_traffic_counts."2016"
UNION ALL
SELECT 'nysdot_traffic_counts_2017' AS data, 100.0 * SUM(CASE WHEN geom IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS geom_pct
FROM nysdot_traffic_counts."2017"
)
SELECT * FROM qaqc
ORDER BY geom_pct ASC;

^ query to calculate the geocoding / mapping success rate

[baolingz]

dep_cats_permits.geo_rejects and nysdec_facility_permits.geo_rejects were created, which include records got rejected by Geosupport.

[baolingz]

@AmandaDoyle issues solved. Ready for a final check before sending them to labs.

[AmandaDoyle]

@baolingz this looks great as a next step can you

• [x] 1) make the updated data (along with two geo_rejects tables) available to @aferrar so that he can share it with Lynn and once it's in Esri we'll review it and share it with Cary
• [x] 2) instruct @pichot and Labs on how to access the data
• [ ] 3) @pichot and Labs - if you come across and issues or inconsistencies in field names, please open issues

Question: Why don't we output a geo rejects table for nysdot_aadt?

[baolingz]

We didn't output a geo_rejects table for nysdot_aadt because we didn't geocode it but used the geometry from the source data. Besides, it doesn't have enough information for us to geocode on.

[AmandaDoyle]

@AmandaDoyle got it - makes sense. Thanks!