Set up PostGIS extension

[rvalenciano]

Set up PostGIS extension

Enable PostGIS extension in our PostgreSQL database through Hasura migrations to support geographical features in our rental platform.

Problem

We need geographical queries support for:

• Apartment locations storage
• Proximity searches
• Area-based filtering

Solution

Create Hasura migrations to enable and verify PostGIS extension installation.

Migration Files

Create new migration:

hasura migrate create enable_postgis

up.sql:

-- Verify superuser privileges
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_roles
        WHERE rolname = current_user
        AND rolsuper = true
    ) THEN
        RAISE EXCEPTION 'Superuser privileges required to enable PostGIS';
    END IF;
END
$$;

-- Enable PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify installation
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_extension
        WHERE extname = 'postgis'
    ) THEN
        RAISE EXCEPTION 'PostGIS extension failed to install';
    END IF;
END
$$;

down.sql:

DROP EXTENSION IF EXISTS postgis CASCADE;

Verification Steps

1. Apply migration:

   hasura migrate apply

2. Verify installation:

   SELECT PostGIS_Version();

Prerequisites

• PostgreSQL with PostGIS support
• Database user with SUPERUSER privileges
• Hasura CLI installed

Success Criteria

• [ ] PostGIS extension is enabled
• [ ] Spatial functions are available
• [ ] Migration can be rolled back cleanly
• [ ] Point and geometry data types are supported

Additional Context

This is required for implementing apartment location features and proximity search functionality.

[rvalenciano]

Approach to solve it:

1. Change postgres image in docker-compose in favour of a postgis based one.
2. Add the migration suggested and verify it is working