GiST index for geometric and text in PostgreSQL - Mini Project: Build & Apply

Create and Use GiST Index for Geometric and Text Data in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a real estate company. The database stores information about properties, including their locations as geometric points and descriptions as text. To speed up searches by location and text similarity, you want to create GiST indexes.

🎯 Goal: Build a PostgreSQL table with geometric and text columns, create GiST indexes on these columns, and write queries that use these indexes to efficiently find properties near a location and with similar descriptions.

📋 What You'll Learn

Create a table called properties with columns id (integer primary key), location (point), and description (text).

Create a GiST index on the location column.

Create a GiST index on the description column using the pg_trgm extension.

Write a query to find properties within a certain distance from a given point using the GiST index on location.

Write a query to find properties with descriptions similar to a given keyword using the GiST index on description.

💡 Why This Matters

🌍 Real World

Real estate, mapping, and location-based services often need fast searches by location and text descriptions. GiST indexes help speed up these queries.

💼 Career

Database administrators and backend developers use GiST indexes to optimize spatial and text search queries in PostgreSQL.

1

Create the properties table with geometric and text columns

Create a table called properties with columns: id as an integer primary key, location as a point type, and description as text.

PostgreSQL

-- Create the properties table with id, location, and description columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types. Use SERIAL for auto-incrementing id.

2

Enable the pg_trgm extension and create GiST indexes

Enable the pg_trgm extension with CREATE EXTENSION. Then create a GiST index called idx_location_gist on the location column of properties. Also create a GiST index called idx_description_gist on the description column using the gist_trgm_ops operator class.

PostgreSQL

CREATE TABLE properties (
    id SERIAL PRIMARY KEY,
    location POINT,
    description TEXT
);
-- Enable pg_trgm extension and create GiST indexes on location and description
-- Your code here

Need a hint?

Use CREATE EXTENSION IF NOT EXISTS pg_trgm; to enable trigram support. Use CREATE INDEX ... USING GIST to create GiST indexes. For text, specify gist_trgm_ops operator class.

3

Write a query to find properties near a given point

Write a query to select all columns from properties where the location is within a distance of 5 units from the point (3, 4). Use the <@ operator with a circle to leverage the GiST index on location.

PostgreSQL

CREATE TABLE properties (
    id SERIAL PRIMARY KEY,
    location POINT,
    description TEXT
);

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_location_gist ON properties USING GIST (location);

CREATE INDEX idx_description_gist ON properties USING GIST (description gist_trgm_ops);

-- Write a query to find properties within distance 5 from point (3,4)
-- Your code here

Need a hint?

Use the <@ operator with a circle to find points within distance. The syntax location <@ circle '((x,y),r)' finds points inside the circle.

4

Write a query to find properties with similar descriptions

Write a query to select all columns from properties where the description is similar to the word 'garden'. Use the % operator to leverage the GiST index on description.

PostgreSQL

CREATE TABLE properties (
    id SERIAL PRIMARY KEY,
    location POINT,
    description TEXT
);

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_location_gist ON properties USING GIST (location);

CREATE INDEX idx_description_gist ON properties USING GIST (description gist_trgm_ops);

SELECT * FROM properties
WHERE location <@ circle '((3,4),5)';

-- Write a query to find properties with descriptions similar to 'garden'
-- Your code here

Need a hint?

Use the % operator for trigram similarity search on text columns with GiST index.