If you didn't follow all the other recipes, be sure to import the hotspots (Global_24h.csv) and the countries dataset (countries.shp) in PostGIS. The following is how to do it with ogr2ogr (you should import both the datasets in their original SRID, 4326, to make spatial operations faster):
- Import in PostGIS the Global_24h.csv file, using the global_24.vrt virtual driver you created in a previous recipe:
$ ogr2ogr -f PostgreSQL PG:"dbname='postgis_cookbook'
user='me' password='mypassword'" -lco SCHEMA=chp01 global_24h.vrt
-lco OVERWRITE=YES -lco GEOMETRY_NAME=the_geom -nln hotspots
- Import the countries shapefile using ogr2ogr:
$ ogr2ogr -f PostgreSQL -sql "SELECT ISO2, NAME AS country_name
FROM wborders" -nlt MULTIPOLYGON PG:"dbname='postgis_cookbook'
user='me' password='mypassword'" -nln countries
-lco SCHEMA=chp01 -lco OVERWRITE=YES
-lco GEOMETRY_NAME=the_geom wborders.shp
If you already imported the hotspots dataset using the 3857 SRID, you can use the PostGIS 2.0 method that allows the user to modify the geometry type column of an existing spatial table. You can update the SRID definition for the hotspots table in this way thanks to the support of typmod on geometry objects:
postgis_cookbook=# ALTER TABLE chp01.hotspots
ALTER COLUMN the_geom
SET DATA TYPE geometry(Point, 4326)
USING ST_Transform(the_geom, 4326);
postgis_cookbook=# ALTER TABLE chp01.hotspots
ALTER COLUMN the_geom
SET DATA TYPE geometry(Point, 4326)
USING ST_Transform(the_geom, 4326);