Spatial joins are one of the key features that unleash the spatial power of PostGIS. For a regular join, it is possible to relate entities from two distinct tables using a common field. For a spatial join, it is possible to relate features from two distinct spatial tables using any spatial relationship function, such as ST_Contains, ST_Covers, ST_Crosses, and ST_DWithin.

In the first query, we used the ST_Intersects function to join the earthquake points to their respective state. We grouped the query by the state column to obtain the number of earthquakes in the state.

In the second query, we used the ST_DWithin function to relate each city to the earthquake points within a 200 km distance of it. We filtered out the cities with a population of less than 1 million inhabitants and grouped them by city name and earthquake magnitude to get a report of the number of earthquakes per city and by magnitude.

The third query is similar to the second one, except it doesn't group per city and by magnitude. The distance is computed using the ST_Distance function. Note that as feature coordinates are stored in WGS 84, you need to cast the geometric column to a spheroid and use the spheroid to get the distance in meters. Alternatively, you could project the geometries to a planar system that is accurate for the area we are studying in this recipe (in this case, the ESPG:2163, US National Atlas Equal Area would be a good choice) using the ST_Transform function. However, in the case of large areas like the one we've dealt with in this recipe, casting to geography is generally the best option as it gives more accurate results.

The fourth query uses the ST_Intersects function. In this case, we grouped by the state column and used two aggregation SQL functions (SUM and COUNT) to get the desired results.

Finally, in the last query, you update a spatial table using the results of a spatial join. The concept behind this is like that of the previous query, except that it is in the context of an UPDATE SQL command.