5.0.1 Preparation

Open pgAdmin in the browser and log in. Open the Query Tool (Right click trainingdatabase -> Query Tool).

5.0.2 Metadata tables

All tables containing spatial data are registered in metadata tables:

5.1 Exercise 4.1: Metadata of geometries

Inspect the geometry_columns table. What information do the different columns contain?

SELECT *
FROM
geometry_columns;

5.2 Exercise 4.2: Representing geometries

Let’s inspect the format in which spatial data is stored in PostGIS. Execute the following SQL query:

SELECT
name_en, continent, wkb_geometry
FROM
ne.countries_world
WHERE
name_en = 'Ireland';

The result tells us that the contents of the column ‘wkb_geometry’ is in a machine-readable binary format.

You can display the coordinates in a more readable format with the following query:

SELECT
name_en, continent, ST_AsText(wkb_geometry)
FROM
ne.countries_world
WHERE
name_en = 'Ireland';

5.3 Exercise 4.3: Functions

Try out the following functions with Ireland’s geometry:

• ST_Boundary

SELECT
...
FROM
...
WHERE
...;

SELECT
name_en, continent, ST_Boundary() -- fill in the geometry column
FROM
schema.table
WHERE
...; -- select based on the country name

SELECT
name_en, continent, ST_Boundary(wkb_geometry)
FROM
ne.countries_world
WHERE
name_en = 'Ireland';

• ST_Centroid

SELECT
...
FROM
...
WHERE
...;

SELECT
name_en, continent, ST_Centroid() -- fill in the geometry column
FROM
schema.table
WHERE
...; -- select based on the country name

SELECT
name_en, continent, ST_Centroid(wkb_geometry)
FROM
ne.countries_world
WHERE
name_en = 'Ireland';

• ST_Envelope

SELECT
...
FROM
...
WHERE
...;

SELECT
name_en, continent, ST_Envelope() -- fill in the geometry column
FROM
schema.table
WHERE
...; -- select based on the country name

SELECT
name_en, continent, ST_Envelope(wkb_geometry)
FROM
ne.countries_world
WHERE
name_en = 'Ireland';

Note how the geometry type differs based on the function.

5.4 Exercise 4.4: Spatial indexes

Inspect the indexes of the ne.countries.

• Which indexes have been created for the table?

Indexes can be inspected both with pgAdmin or QGIS’s DB Manager and also with an SQL query.

Let’s create a spatial index for the railroads_eu table:

CREATE INDEX railroads_eu_wkb_geometry
ON ne.railroads_eu
USING GIST(wkb_geometry);

You can find indexes in pgAdmin by selecting Schemas > Tables > Indexes.

In psql you can inspect indexes with the following commands:

\dt+ ne.railroads_eu
\di+ ne.railroads_eu

What is the size of the railroads_eu table and of its index?