Setting up PostGIS on GCP

Working towards online geospatial solutions

OK, so here we are, in the midst of COVID-19. What a great time to start a new project and learn a new skill. I’m focusing on developing a bit of my golang chops and the Google Cloud Platform knowledge to see if we can use these as our primary backends for teaching and research needs at work. My boss just gutted my server room and has maneuvered the only person who can actually do geospatial backend work to not work in my unit any longer so I’ve got to get creative.

This is an ongoing process, check out all the stuff related to this topic posted here under the GCP category.

Setting up gcloud

First we need to download the Google Cloud Platform SDK. It can be found here. Download it and install it locally. This will provide you with the gcloud command line interface.

Here is the one I installed

rodney@mini ~ % gcloud version
Google Cloud SDK 285.0.1
bq 2.0.55
core 2020.03.17
gsutil 4.48
Google Cloud SDK 285.0.1

Starting a Project

For this, you need to log into the Google Cloud Console and start a new project. You will also be required to enter a credit card so that any charges you incur are taken care of. For developers and all other people, Google gives $300 in monthly credit, which I have found to be more than enough for me to do everything I need to do to play around. I have yet to be hit with any charges (and you can put on hard limits to be safe).

Once you have set up a project you can install a database.

Installing PostgreSQL as CloudSQL

There are serveal ways to install databases, you could spin up a Virtual Machine (VM) running some flavor of linux (I like Ubuntu myself) or you can do this serverless (which is what I’m going to do here). To run serverless, you do not need to worry about the underlying infrastructure of what is going on, just trust that Google can make a copy of PostgreSQL available to me.

To get an instance going, select SQL from the hamburger menu on the left and follow the documentation on spinning up an instance. I’m using PostgreSQL because we can install PostGIS onto it, both MySQL and some Microsoft offering is also available if that is how you swing (no idea if the M$ can have geospatial data extensions).

Notes:

  • Give the instance a reasonable name, you’ll be using it over and over.
  • Make a good password and save it.

Interacting with Postgresql Via Console

You can turn on the instance and log directly into it using the Google console. By default, the password you made was for the user postgres and you can connect to it as:

gcloud sql connect theNameYouGaveYourPostgresInstance --user=postgres

And you should be connected within psql, just like you are when you interact with it locally. To find out more about the options, see the help at:

gcloud sql connect --help

Installing PostGIS

While we are here, we can install PostGIS. While you are logged into the instance (SQL -> click on instance name), make a database (menu on the left) that you will be using to store geospatial content.

Next, connect to the database as:

gcloud sql connect theNameYouGaveYourPostgresInstance -d YourNewDatabaseName
Connecting to database with SQL user [postgres].Password for user postgres:
Whitelisting your IP for incoming connection for 5 minutes...done.
psql (9.6.17, server 11.6)
WARNING: psql major version 9.6, server major version 11.
Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>

Now, let’s install PostGIS on this database as well a topography1

arapat=> CREATE EXTENSION postgis;

and the topology extension

arapat=> CREATE EXTENSION postgis_topology;

will now be loaded for this specific database.

Now, we can make a table that has specific columns of data representing geospatial data. Here I’ll add a table from my own research program. Here are data representing 29 sampled populations of Araptus attenuatus, the Sonoran Desert Bark Beetle where we recorded the number of male and female individuals at each locale (with latitude and longitude) as well as an estimate of the habitat suitability (from a MaxENT analysis).

Here is the SQL required to make this table

CREATE TABLE sex_bias (
    id SERIAL PRIMARY KEY,
    site character(6) NOT NULL, 
    num_males integer NOT NULL,
    num_females integer NOT NULL,
    suitability float NOT NULL,
    latitude float NOT NULL,
    longitude float NOT NULL
);

This makes a table with a primary key (id) and information right from the underlying CSV file.

Now, we can add some data to the table.

 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '12', 24, 21, 0.351905, 27.18232, -112.6655);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '153', 35, 41, 0.732487, 24.13389, -110.4624);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '157', 26, 30, 0.881029, 24.0195, -110.096);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '159', 22, 15, 0.187965, 27.52944, -113.3161);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '160', 48, 36, 0.365191, 27.40498, -112.5296);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '161', 64, 63, 0.279105, 27.0367, -112.986);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '162', 57, 41, 0.6136198, 27.2028, -112.408);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '163', 21, 21, 0.432873, 24.2115, -110.951);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '166', 19, 26, 0.267303, 25.91409, -112.0806);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '168', 28, 25, 0.496465, 25.55757, -111.2156);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '171', 38, 39, 0.461357, 28.22308, -113.1826);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '173', 19, 32, 0.524267, 28.40846, -112.8698);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '175', 13, 8, 0.42144, 28.72796, -113.4897);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '177', 49, 50, 0.26179, 28.66056, -113.9914);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '32', 40, 27, 0.0562845, 26.63783, -109.327);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '48', 18, 27, 0.619519, 24.21441, -110.2725);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '51', 9, 11, 0.412512, 25.34819, -111.6006);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '58', 11, 9, 0.901878, 26.0155, -111.3547);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '64', 16, 19, 0.287623, 25.60521, -111.3264);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '73', 11, 5, 0.145523, 24.00789, -109.8507);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '75', 16, 18, 0.8656099, 24.58843, -110.746);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '77', 19, 16, 0.422609, 24.87611, -110.6917);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '84', 13, 17, 0.395347, 28.96651, -113.6679);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '88', 23, 18, 0.218594, 29.32541, -114.2935);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '89', 18, 19, 0.3975, 28.03661, -113.3999);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '9', 11, 10, 0.5641302, 29.01457, -113.9449);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( '93', 25, 21, 0.162724, 26.94589, -112.0461);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( 'Aqu', 12, 9, 0.7217, 23.2855, -110.1043);
 INSERT INTO sex_bias (site, num_males, num_females, suitability, latitude, longitude) VALUES( 'Const', 18, 11, 0.174438, 25.0247, -111.675);

This adds the 29 locales of raw data into the table.

Making GEOMETRY Columns

If we look at the table in PostgreSQL, we see that

\d sex_bias
          Table "public.sex_bias"
   Column    |       Type       | Modifiers
-------------+------------------+-----------
 id          | integer          | not null
 site        | character(6)     |
 num_males   | integer          |
 num_females | integer          |
 suitability | double precision |
 latitude    | double precision |
 longitude   | double precision |
Indexes:
    "sex_bias_pkey" PRIMARY KEY, btree (id)

We need to take the longitude and latitude columns and turn them into actual GEOMETRY data columns so that we can perform geospatial operations on the data set. To do this, we need to alter the table by adding a GEOMETRY column and then populate it with the latitude and longitude columns.

ALTER TABLE sex_bias ADD COLUMN geom geometry(Point, 4326);
UPDATE sex_bias SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude), 4326);

And now we can drop the old latitude and longitude columns

ALTER TABLE sex_bias DROP COLUMN latitude;
ALTER TABLE sex_bias DROP COLUMN longitude;

which makes our table look like:

\d sex_bias
                                  Table "public.sex_bias"
   Column    |         Type         |                       Modifiers
-------------+----------------------+-------------------------------------------------------
 id          | integer              | not null default nextval('sex_bias_id_seq'::regclass)
 site        | character(6)         | not null
 num_males   | integer              | not null
 num_females | integer              | not null
 suitability | double precision     | not null
 geom        | geometry(Point,4326) |
Indexes:
    "sex_bias_pkey" PRIMARY KEY, btree (id)

And now we are GO!


See also