How to do it...

Carry out the following steps to create and organize a database:

1. Create a database named chapter10 by executing the following command:

      CREATE DATABASE chapter10;

2. Create a schema named postgis in the chapter10 database, where we will install PostGIS. Execute the following command:

      CREATE SCHEMA postgis;

3. Install PostGIS in the postgis schema of the chapter10 database:
   1. If you are running PostgreSQL 9.1 or a newer version, use the CREATE EXTENSION statement:

               CREATE EXTENSION postgis WITH SCHEMA postgis;

The WITH SCHEMA clause of the CREATE EXTENSION statement instructs PostgreSQL to install PostGIS and its objects in the postgis schema.

4. Check whether or not the PostGIS installation has succeeded by running the following commands:

      > psql -U me -d chapter10 
      > chapter10=# SET search_path = public, postgis;

Verify the list of relations in the schema, which should include all the ones created by the extension:

If you are using pgAdmin or a similar database system, you can also check on the graphical interface whether the schemas, views, and table were created successfully.

The SET statement instructs PostgreSQL to consider the public and postgis schemas when processing any SQL statements from our client connection. Without the SET statement, the \d command will not return any relation from the postgis schema.

5. To prevent the need to manually use the SET statement every time a client connects to the chapter10 database, alter the database by executing the following command:

      ALTER DATABASE chapter10 SET search_path = public, postgis;

All future connections and queries to chapter10 will result in PostgreSQL automatically using both public and postgis schemas.

Note: It may be the case that, for Windows users, this option may not work well; in version 9.6.7 it worked but not in version 9.6.3. If it does not work, you may need to clearly define the search_path on every command. Both versions are provided.

6. Load the PRISM rasters and San Francisco boundaries geometry, which we used in Chapter 5, Working with Raster Data, by executing the following command:

      > raster2pgsql -s 4322 -t 100x100 -I -F -C -Y 
      C:\postgis_cookbook\data\chap5
      \PRISM\    PRISM_tmin_provisional_4kmM2_201703_asc.asc 
      prism | psql -d chapter10 -U me

Then, define the search path:

      > raster2pgsql -s 4322 -t 100x100 -I -F -C -Y 
      C\:postgis_cookbook\data\chap5
      \PRISM\PRISM_tmin_provisional_4kmM2_201703_asc.asc 
      prism | psql "dbname=chapter10 options=--search_path=postgis" me 

7. As we did in Chapter 5, Working with Raster Data, we will postprocess the raster filenames to a date column by executing the following command:

      ALTER TABLE postgis.prism ADD COLUMN month_year DATE;
      UPDATE postgis.prism SET month_year = ( 
        SUBSTRING(split_part(filename, '_', 5), 0, 5) || '-' || 
        SUBSTRING(split_part(filename, '_', 5), 5, 4) || '-01' 
      ) :: DATE;

8. Then, we load the San Francisco boundaries by executing the following command:

      > shp2pgsql -s 3310 -I
      C\:postgis_cookbook\data\chap5\SFPoly\sfpoly.shp sfpoly |
      psql -d chapter10 -U me

Then, define the search path:

      > shp2pgsql -s 3310 -I 
      C\:postgis_cookbook\data\chap5\SFPoly\sfpoly.shp 
      sfpoly | psql "dbname=chapter10 options=--search_path=postgis" me

9. Copy this chapter's dataset to its own directory by executing the following commands:

      > mkdir C:\postgis_cookbook\data\chap10
      > cp -r /path/to/book_dataset/chap10 
      C\:postgis_cookbook\data\chap10

We will use the shapefiles for California schools and police stations provided by the USEIT program at the University of Southern California. Import the shapefiles by executing the following commands; use the spatial index flag -I only for the police stations shapefile:

      > shp2pgsql -s 4269 -I 
      C\:postgis_cookbook\data\chap10\CAEmergencyFacilities\CA_police.shp 
      capolice | psql -d chapter10 -U me

Then, define the search path:

      > shp2pgsql -s 4269 C\:postgis_cookbook\data\chap10
      \CAEmergencyFacilities\CA_schools.shp 
      caschools | psql -d chapter10 -U me

Then, define the search path:

      shp2pgsql -s 4269 -I C\:postgis_cookbook\data\chap10
      \CAEmergencyFacilities\CA_schools.shp 
      caschools | psql "dbname=chapter10 options=--search_path=postgis" 
      me shp2pgsql -s 4269 -I 
      C\:postgis_cookbook\data\chap10\CAEmergencyFacilities\CA_police.shp 
      capolice | psql "dbname=chapter10 options=--search_path=postgis" me