Database Reference
In-Depth Information
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);
How it works...
SpatialjoinsareoneofthekeyfeaturesthatunleashthespatialpowerofPostGIS.
Foraregularjoin,itispossibletorelateentitiesfromtwodistincttablesusingacom-
monfield.Foraspatialjoin,itispossibletorelatefeaturesfromtwodistinctspatial
tablesusinganyspatialrelationshipfunction,suchas
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 containing 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 from 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
magnitude.
The third query is similar to the second one, except it doesn't group per city and
magnitude. The distance is computed using the
ST_Distance
function. Note that
asfeaturecoordinatesarestoredinWGS84,youneedtocastthegeometriccolumn
toaspheroidandusethespheroidtogetthedistanceinmeters.Alternatively,you
