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)

2. 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 the following code:

      postgis_cookbook=# SELECT c.name, e.magnitude, count(*) as hq_count 
      FROM chp03.cities AS c 
        JOIN chp03.earthquakes AS e 
        ON ST_DWithin(geography(c.the_geom), geography(e.the_geom), 200000) 
        WHERE c.pop_2000 > 1000000 
        GROUP BY c.name, e.magnitude 
        ORDER BY c.name, e.magnitude, hq_count; 

      (18 rows)

3. As a variant of the previous query, executing the following code gives you a complete list of earthquakes along with their distance from the city (in meters):

      postgis_cookbook=# SELECT c.name, e.magnitude,
        ST_Distance(geography(c.the_geom), geography(e.the_geom)) 
      AS distance FROM chp03.cities AS c 
        JOIN chp03.earthquakes AS e 
        ON ST_DWithin(geography(c.the_geom), geography(e.the_geom), 200000) 
        WHERE c.pop_2000 > 1000000 
        ORDER BY distance; 

      (488 rows) 

4. Now, ask PostGIS for the city count and the total population in each state by executing the following code:

      postgis_cookbook-# SELECT s.state, COUNT(*) 
      AS city_count, SUM(pop_2000) AS pop_2000 
      FROM chp03.states AS s 
        JOIN chp03.cities AS c 
        ON ST_Intersects(s.the_geom, c.the_geom) 
        WHERE c.pop_2000 > 0 -- NULL values is -9999 on this field!

        GROUP BY s.state 
        ORDER BY pop_2000 DESC; 

      (51 rows) 

5. As a final test, use a spatial join to update an existing table. You need to add the information in the state_fips field to the earthquake table from the states table. First, to host that kind of information, you need to create a column as shown in the following command:

      postgis_cookbook-# ALTER TABLE chp03.earthquakes 
      ADD COLUMN state_fips character varying(2); 

6. Then, you can update the new column using a spatial join, as follows:

      postgis_cookbook-# UPDATE chp03.earthquakes AS e 
        SET state_fips = s.state_fips 
        FROM chp03.states AS s 
        WHERE ST_Intersects(s.the_geom, e.the_geom);