The steps you need to follow to complete this recipe are as follows:

1. Analyze the structure of the Global_24h.csv file (in Windows, open the CSV file with an editor such as Notepad):

      $ cd ~/postgis_cookbook/data/chp01/
      $ head -n 5 Global_24h.csv

The output of the preceding command is as follows:

2. Create a GDAL virtual data source composed of just one layer derived from the Global_24h.csv file. To do so, create a text file named global_24h.vrt in the same directory where the CSV file is and edit it as follows:

       <OGRVRTDataSource> 
         <OGRVRTLayer name="Global_24h"> 
         <SrcDataSource>Global_24h.csv</SrcDataSource> 
         <GeometryType>wkbPoint</GeometryType> 
         <LayerSRS>EPSG:4326</LayerSRS> 
           <GeometryField encoding="PointFromColumns"
            x="longitude" y="latitude"/> 
         </OGRVRTLayer> 
       </OGRVRTDataSource>

3. With the ogrinfo command, check if the virtual layer is correctly recognized by GDAL. For example, analyze the schema of the layer and the first of its features (fid=1):

      $ ogrinfo global_24h.vrt Global_24h -fid 1 

The output of the preceding command is as follows:

You can also try to open the virtual layer with a desktop GIS supporting a GDAL/OGR virtual driver such as Quantum GIS (QGIS). In the following screenshot, the Global_24h layer is displayed together with the shapefile of the countries that you can find in the dataset directory of the book:

4. Now, export the virtual layer as a new table in PostGIS using the ogr2ogr GDAL/OGR command (in order for this command to become available, you need to add the GDAL installation folder to the PATH variable of your OS). You need to use the -f option to specify the output format, the -t_srs option to project the points to the EPSG:3857 spatial reference, the -where option to load only the records from the MODIS Terra satellite type, and the -lco layer creation option to provide the schema where you want to store the table:

      $ ogr2ogr -f PostgreSQL -t_srs EPSG:3857
      PG:"dbname='postgis_cookbook' user='me' password='mypassword'"
      -lco SCHEMA=chp01 global_24h.vrt -where "satellite='T'" 
      -lco GEOMETRY_NAME=the_geom

5. Check how the ogr2ogr command created the table, as shown in the following command:

      $ pg_dump -t chp01.global_24h --schema-only -U me postgis_cookbook 
 
      CREATE TABLE global_24h ( 
        ogc_fid integer NOT NULL, 
        latitude character varying, 
        longitude character varying, 
        brightness character varying, 
        scan character varying, 
        track character varying, 
        acq_date character varying, 
        acq_time character varying, 
        satellite character varying, 
        confidence character varying, 
        version character varying, 
        bright_t31 character varying, 
        frp character varying, 
        the_geom public.geometry(Point,3857) 
      );

6. Now, check the record that should appear in the geometry_columns metadata view:

      postgis_cookbook=# SELECT f_geometry_column, coord_dimension,
      srid, type FROM geometry_columns 
      WHERE f_table_name = 'global_24h';

The output of the preceding command is as follows:

7. Check how many records have been imported in the table:

      postgis_cookbook=# SELECT count(*) FROM chp01.global_24h; 

The output of the preceding command is as follows:

8. Note how the coordinates have been projected from EPSG:4326 to EPSG:3857:

      postgis_cookbook=# SELECT ST_AsEWKT(the_geom)
      FROM chp01.global_24h LIMIT 1;

The output of the preceding command is as follows: