Database Reference
In-Depth Information
GEOMETRY_NAME=the_geom
TM_WORLD_BORDERS-0.3.shp
Note
Incaseyoualreadyimportedthehotspotsdatasetusingthe3857SRID,
youcanusethenewPostGIS2.0methodthatallowstheusertomodify
thegeometrytypecolumnofanexistingspatialtable.Youcanupdatethe
SRIDdefinitionforthehotspotstableinthiswaythankstothesupportof
typmod on geometry objects:
postgis_cookbook=# ALTER TABLE hotspots
ALTER COLUMN the_geom
SET DATA TYPE geometry(Point, 4326)
USING ST_Transform(the_geom, 4326);
How to do it...
The steps you need to follow to complete this recipe are as follows:
1. Check how many hotspots there are for each distinct country by using the
following query:
postgis_cookbook=> SELECT c.country_name,
MIN(c.iso2) as iso2, count(*) as hs_count
FROM chp01.hotspots as hs JOIN
chp01.countries as c ON
ST_Contains(c.the_geom, hs.the_geom)
GROUP BY c.country_name ORDER BY
c.country_name;
The output of the preceding command is as follows:
