Maptime Seattle Postgis - Problem 4

problem4.sql

WITH county_hurricanes AS (
	SELECT DISTINCT c.name AS county_name, s.name AS state_name, h.serial_num
	FROM geo.counties_cb c
	INNER JOIN geo.states_ne s ON c.statefp = right(s.fips, 2) --The FIPS codes in the state table have "US" infront of them, the right() function trims that
	INNER JOIN weather.hurricane_lines_noaa h ON ST_Intersects(c.geom, h.geom)
	WHERE s.admin = 'United States of America'
)

SELECT county_name, state_name, COUNT(*)
FROM county_hurricanes
GROUP BY county_name, state_name
ORDER BY COUNT(*) DESC