How to do it...

In this recipe, you will see for yourself the power of spatial SQL by solving a series of typical problems using spatial joins:

  1. 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)
  1. 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 ...

Get PostGIS Cookbook - Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.