Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Maptime Seattle Postgis - Problem 4
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment