SQL & Spatial Queries

SQL (Structured Query Language) allows you to interact directly with your PostGIS database. With just a few simple commands, you can filter data, calculate values, and run basic spatial checks. This page introduces the essential SQL and spatial functions you’ll use most often in pgAdmin.

You can download the full dataset used on this page by running the SQL script included here:

• Manual_Data_PostGIS.zip

This dataset adds:

• places (10 Western Cape towns/cities with point geometry)
• place_types (City, Town, Village, Settlement)
• admin_boundaries (3 Western Cape municipal polygons)
• All constraints, foreign keys, indexes, and geometry types

Step 1: Open the Query Tool

You’ll run your SQL statements inside pgAdmin’s built-in editor.

1. In pgAdmin, expand kartoza_postgis → Schemas → public → Tables.

2. Select any table (e.g., places).

3. Click the Query Tool button in the top toolbar.

Image credit: pgAdmin

Step 2: Select Records

Begin by exploring your data using simple SELECT statements.

1. Show all rows

   SELECT * FROM places;
   

   
   

2. Show specific fields

   SELECT name, population, place_type_id
   FROM places;
   

   
   

3. Filter rows (WHERE)

   SELECT *
   FROM places
   WHERE population > 50000;
   

   
   

These simple SELECT queries let you preview and inspect the attribute data stored in your PostGIS tables.

Image credit: pgAdmin

Step 3: Sort and Limit Results

Sorting and limiting output helps you quickly find useful information.

1. Sort by population (descending)

   SELECT name, population
   FROM places
   ORDER BY population DESC;
   

   
   

2. Show only the first 5 rows

   SELECT *
   FROM places
   LIMIT 5;
   

   
   

Image credit: pgAdmin

Step 4: Basic Calculations

You can use SQL to calculate values on the fly.

1. Calculate population density

   SELECT
     name,
     population,
     area_km2,
     population / area_km2 AS population_density
   FROM places;
   

   
   

2. Count all records

   SELECT COUNT(*) FROM places;
   

   
   

Image credit: pgAdmin

Step 5: Basic Spatial Queries

PostGIS extends SQL with geometry functions to allow spatial filtering and analysis.

1. Find features within a bounding box

   SELECT *
   FROM places
   WHERE geom && ST_MakeEnvelope(18, -35, 20, -33, 4326);
   

   
   

2. Select features within a radius

   (Example: 50 km around a specific coordinate)

   SELECT *
   FROM places
   WHERE ST_DWithin(
     ST_Transform(geom, 3857),
     ST_Transform(ST_SetSRID(ST_Point(18.5, -34.0), 4326), 3857),
     50000
   );
   

   
   

3. Measure distance between two features

   SELECT
     a.name AS place_a,
     b.name AS place_b,
     ST_Distance(a.geom, b.geom) AS distance_m
   FROM places a, places b
   WHERE a.id = 1 AND b.id = 2;
   

   
   

Each spatial function returns a true/false result, distance, or filtered rows, perfect for building powerful GIS logic directly in the database.

Image credit: pgAdmin

Step 6: Update Data with SQL

SQL can also modify records. Make sure you understand what you’re updating before running these.

1. Update a field value

   UPDATE places
   SET population = 120000
   WHERE id = 3;
   

   
   

2. Assign a place type (City, Town, Village, etc.)

   UPDATE places
   SET place_type_id = 2
   WHERE name = 'Stellenbosch';
   

   
   

3. Delete a record

   DELETE FROM places
   WHERE id = 10;
   

   
   

Image credit: pgAdmin

Step 7: Run Spatial Summaries

PostGIS makes it easy to summarise spatial relationships.

1. Count how many features fall inside a polygon

   SELECT COUNT(*)
   FROM places
   WHERE ST_Within(
     geom,
     (SELECT geom 
     FROM admin_boundaries 
     WHERE name = 'Drakenstein')
   );
   

   
   

2. Calculate total area of polygons

   SELECT SUM(ST_Area(ST_Transform(geom, 3857))) AS total_area_m2
   FROM admin_boundaries;
   

   
   

Image credit: pgAdmin

Your database is now ready for real analysis. Even these basic SQL and spatial functions give you powerful control over your data.