testing schools 1/10 mile

PHOTO EMBED

Tue Feb 08 2022 23:29:25 GMT+0000 (UTC)

Saved by @qbui #sql

-- how many stations within 1/10 mile range of each zip code?
SELECT * 
FROM
 (SELECT
        vehicle,
        date1,
        code,
        metric,
        safe_cast(lon as FLOAT64) as lon,
        safe_cast(lat as FLOAT64) as lat,
        ST_GeogPoint(safe_cast(lon as FLOAT64), safe_cast(lat as FLOAT64)) AS point,
    FROM 
        `nytint-prd.gfx_nyc_fleet_logs.test_log_subset_3mo_geo`
    WHERE ABS(safe_cast(lat as FLOAT64)) < 90
    AND ABS(safe_cast(lon as FLOAT64)) < 180) as logs,
    `nytint-prd.gfx_nyc_fleet_logs.school_locations` as schools
WHERE ST_DWithin(
        ST_GeogPoint(safe_cast(schools.LONGITUDE as FLOAT64), safe_cast(schools.LATITUDE as FLOAT64)),
        logs.point,
        1609.34/10)
limit 100
content_copyCOPY