Using the PostGIS Extension in PostgreSQL

Introduction

In this lab, you will learn how to use the PostGIS extension to add spatial capabilities to a PostgreSQL database. PostGIS transforms your database into a spatial database, allowing it to store and query data that is defined in a geometric space.

You will start by creating a new database and enabling the PostGIS extension. Then, you will create a table with a GEOMETRY column to store location data. Finally, you will insert data for several cities and perform spatial queries to calculate distances and find data within a specific area.

Create a Spatial Database and Enable PostGIS

In this step, you will create a new database and enable the PostGIS extension within it. It is a good practice to use a dedicated database for spatial data to keep it organized.

First, open a terminal and connect to the PostgreSQL server using the psql interactive terminal. You will connect as the default postgres superuser.

sudo -u postgres psql

You will see the psql prompt, which looks like postgres=#.

Next, create a new database named spatial_db. All SQL commands in psql must end with a semicolon (;).

CREATE DATABASE spatial_db;

You should see the following confirmation message:

CREATE DATABASE

Now, connect to your newly created database. The \c command is used to connect to a different database.

\c spatial_db

Your prompt will change to indicate that you are now connected to spatial_db:

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

With the database ready, enable the PostGIS extension. This command loads all the PostGIS spatial types, functions, and tables into your database.

CREATE EXTENSION postgis;

The output will confirm that the extension was created:

CREATE EXTENSION

To verify that PostGIS is installed correctly, you can check its version.

SELECT PostGIS_full_version();

This will display detailed version information for PostGIS and its related libraries, confirming a successful setup. You can now proceed to the next step. Do not exit the psql shell.

Create a Spatial Table and Insert Data

Now that you have a spatial database, you can create tables to store geographic data. In this step, you will create a cities table and insert the coordinates for a few major cities.

You should still be in the psql shell, connected to the spatial_db database.

First, create a table named cities. This table will have a standard id and name, along with a location column of type GEOMETRY.

Understanding GEOMETRY(Point, 4326)

• GEOMETRY: This is the fundamental PostGIS data type for storing spatial features.
• Point: This specifies that the geometry column will only store points. Other types include LINESTRING and POLYGON.
• 4326: This is the Spatial Reference System Identifier (SRID). SRID 4326 corresponds to WGS 84, the standard coordinate system used by GPS and Google Maps.

Execute the following command to create the table:

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

You will see the confirmation:

CREATE TABLE

Next, insert data into the cities table. You will use the ST_GeomFromText function to convert Well-Known Text (WKT) representations of points into geometry objects. The format is POINT(longitude latitude).

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));

The output indicates that three rows were inserted:

INSERT 0 3

To verify that the data was inserted correctly, you can query the table. Use the ST_AsText function to convert the geometry object back into a human-readable text format.

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

The output should display the data you just inserted:

 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)

You have successfully created a spatial table and populated it with data.

Perform Spatial Queries and Analysis

The true power of PostGIS lies in its ability to perform spatial queries. In this step, you will use PostGIS functions to calculate the distance between two points and to find which points fall within a certain area.

You should still be in the psql shell, connected to the spatial_db database.

First, let's calculate the distance between New York and London. For accurate distance calculations on the Earth's surface, it's best to cast the geometry type to the geography type. The geography type accounts for the Earth's curvature, and ST_Distance will return the result in meters.

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

The query will return the great-circle distance in meters. The result will be a large number, approximately 5.57 million meters.

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

Next, let's perform an analysis to find which of our cities are within 200 kilometers of London. To do this, you will first create a circular buffer around London's location and then check which cities' locations intersect with that buffer.

• ST_Buffer: Creates a geometry that represents all points whose distance from the source geometry is less than or equal to a specified distance. The distance is in meters when used with the geography type.
• ST_Intersects: Returns true if two geometries "spatially intersect" - if they have any space in common.

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 uses a Common Table Expression (CTE) to create a 200,000-meter (200 km) buffer around London. It then finds all cities that intersect with this buffer. As expected, only London itself is within this area.

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

You have now performed basic spatial analysis. You can exit the psql shell by typing \q.

\q

Clean Up the Database

It is good practice to clean up your environment after completing your work. In this step, you will drop the spatial_db database. Dropping a database removes the database itself, along with all objects it contains, such as tables and extensions.

You should be back at the regular terminal prompt (labex@vm:~$).

To drop a database, you must be connected to a different database. Connect to the default postgres database.

sudo -u postgres psql

Now, execute the DROP DATABASE command.

DROP DATABASE spatial_db;

You will see a confirmation message:

DROP DATABASE

You can verify that the database has been removed by listing all available databases with the \l command.

\l

You will see that spatial_db is no longer in the list.

Finally, exit the psql shell.

\q

Summary

In this lab, you learned how to use the PostGIS extension to add spatial data support to PostgreSQL. You successfully created a dedicated database, enabled the PostGIS extension, defined a table with a geometry column, inserted location data, and performed spatial queries to calculate distances and find intersections. These skills provide a solid foundation for building location-aware applications with PostgreSQL.