Retrieve a FeatureCollection of affected counties joined with wind speed data for Hurricane Sandy

intersection-query.sql

SELECT ROW_TO_JSON(fc) AS json_out FROM (
    SELECT 'FeatureCollection' AS type, 
           ARRAY_TO_JSON(ARRAY_AGG(f)) AS features
    FROM (
        SELECT 'Feature' AS type,
               ST_AsGeoJSON(c.geom)::json AS geometry,
               ROW_TO_JSON((
                SELECT l FROM (
                    SELECT r.radii,
                           r.synoptime,
                           r.basin,
                           r.stormnum,
                           r.ne,
                           r.se,
                           r.sw,
                           r.nw,
                           r.gid AS id,
                           c.geoid AS fips
                ) AS l)) AS properties
        FROM tl_2019_us_county AS c, al182012_radii AS r
        WHERE ST_Intersects(c.geom, ST_MakeValid(r.geom))
    ) f
) fc