PostgreSQL PostGIS Extension

Introduction

In this lab, you will learn how to install and use the PostGIS extension in a PostgreSQL database. PostGIS enables PostgreSQL to store and query spatial data, allowing you to perform spatial queries and analysis.

Install the PostGIS Extension

In this step, you will install the PostGIS extension in a PostgreSQL database. PostGIS adds support for geographic objects to PostgreSQL.

First, connect to the PostgreSQL server as the postgres user using the psql command:

sudo -u postgres psql

This will open the PostgreSQL interactive terminal. You'll see a prompt similar to:

psql (14.7 (Debian 14.7-1.pgdg110+1))
Type "help" for help.

postgres=#

Next, create a database named spatial_db:

CREATE DATABASE spatial_db;

You should see the output:

CREATE DATABASE

Now, connect to the spatial_db database:

\c spatial_db

The prompt will change to:

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

Install the PostGIS extension:

CREATE EXTENSION postgis;

This adds the PostGIS functionality to the spatial_db database. The output should be:

CREATE EXTENSION

Verify the installation by checking the PostGIS version:

SELECT PostGIS_full_version();

The output will show the full version information of the PostGIS extension.

Finally, exit the psql shell:

\q

Create a Spatial Table and Insert Data

In this step, you will create a table to store spatial data and insert some sample data.

First, connect to the spatial_db database using the psql command:

sudo -u postgres psql -d spatial_db

Create a table named cities with columns for id, name, and location. The location column will store the spatial data as a geometry type.

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

This command creates a table named cities with an auto-incrementing id, a name for the city, and a location column to store the city's geographic coordinates as a Point geometry using the SRID 4326 (WGS 84).

You should see the output:

CREATE TABLE

Now, insert data for three cities: New York, London, and Tokyo.

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

This inserts three rows into the cities table, using the ST_GeomFromText function to create Point geometries from Well-Known Text (WKT) strings representing the coordinates of each city.

You should see the output:

INSERT 0 3

To verify the data was inserted correctly, query the table:

SELECT id, name, ST_AsText(location) FROM cities;

The output should look like this:

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

Finally, exit the psql shell:

\q

Perform Spatial Queries

In this step, you will perform some basic spatial queries using the PostGIS extension.

First, connect to the spatial_db database using the psql command:

sudo -u postgres psql -d spatial_db

Calculate the distance between New York and London in meters:

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

This query calculates the distance between the locations of New York and London. The ::geography cast is used to perform the calculation on the Earth's surface, returning the distance in meters. You should see an output similar to:

   st_distance
-----------------
 5570299.7888681
(1 row)

Create a buffer of 200 kilometers (200000 meters) around London and find all cities that intersect with it:

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

This query first creates a buffer around London's location using ST_Buffer. Then, it selects the names of all cities whose locations intersect with the buffer using ST_Intersects. The output should be:

  name
--------
 London
(1 row)

Finally, exit the psql shell:

\q

Remove the PostGIS Extension and Database

In this step, you will remove the PostGIS extension and the spatial_db database.

First, connect to the spatial_db database using the psql command:

sudo -u postgres psql -d spatial_db

Remove the PostGIS extension:

DROP EXTENSION postgis;

This command removes the PostGIS functionality from the spatial_db database. You should see the output:

DROP EXTENSION

Now, drop the cities table:

DROP TABLE cities;

You should see the output:

DROP TABLE

Exit the psql shell:

\q

Finally, drop the spatial_db database:

sudo -u postgres psql -c "DROP DATABASE spatial_db;"

Summary

In this lab, you have learned how to install and use the PostGIS extension in a PostgreSQL database. You created a database, installed the PostGIS extension, created a spatial table, inserted data, performed spatial queries, and finally removed the extension and database. This provides a foundation for working with spatial data in PostgreSQL.