In this recipe, you will see for yourself the power of spatial SQL by solving a series of typical problems using spatial joins:
- First, query PostGIS to get the number of registered earthquakes in 2012 by state:
postgis_cookbook=# SELECT s.state, COUNT(*) AS hq_count
FROM chp03.states AS s
JOIN chp03.earthquakes AS e
ON ST_Intersects(s.the_geom, e.the_geom)
GROUP BY s.state
ORDER BY hq_count DESC;
(33 rows)
- Now, to make it just a bit more complex, query PostGIS to get the number of earthquakes, grouped per magnitude, that are no further than 200 km from the cities in the USA that have more than 1 million inhabitants; execute ...
