Set up apartments table with PostGIS data types

[rvalenciano]

Set up apartments table with PostGIS data types

Create the apartments table with proper PostGIS data types to support geographical features for apartment locations, searching, and filtering.

Problem

We need to store apartment locations and enable spatial queries for:

• Location-based search
• Distance calculations
• Area/neighborhood filtering
• Map-based visualizations

Solution

Create Hasura migration for the apartments table with PostGIS data types.

Migration Files

Create new migration:

hasura migrate create create_apartments_table

up.sql:

-- Create apartments table
CREATE TABLE apartments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    owner_id TEXT REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    warranty_deposit DECIMAL(10,2) NOT NULL CHECK (warranty_deposit > 0),
    coordinates POINT NOT NULL,
    location_area GEOMETRY(POLYGON, 4326),
    address JSONB NOT NULL,
    is_available BOOLEAN DEFAULT true,
    available_from TIMESTAMP WITH TIME ZONE NOT NULL,
    available_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT valid_date_range 
        CHECK (available_until IS NULL OR available_from < available_until)
);

-- Create spatial index
CREATE INDEX idx_apartments_coordinates 
    ON apartments USING GIST (coordinates);
CREATE INDEX idx_apartments_location_area 
    ON apartments USING GIST (location_area);

-- Create regular indexes
CREATE INDEX idx_apartments_owner 
    ON apartments(owner_id);
CREATE INDEX idx_apartments_availability 
    ON apartments(is_available, available_from, available_until);
CREATE INDEX idx_apartments_price 
    ON apartments(price);

-- Create update trigger for updated_at
CREATE TRIGGER update_apartments_updated_at
    BEFORE UPDATE ON apartments
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Create helper function for nearby apartments
CREATE OR REPLACE FUNCTION find_nearby_apartments(
    search_location POINT,
    radius_meters FLOAT,
    min_price DECIMAL DEFAULT NULL,
    max_price DECIMAL DEFAULT NULL
)
RETURNS TABLE (
    id UUID,
    distance FLOAT,
    name TEXT,
    price DECIMAL,
    coordinates POINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.id,
        ST_Distance(
            a.coordinates::geometry, 
            search_location::geometry
        ) as distance,
        a.name,
        a.price,
        a.coordinates
    FROM apartments a
    WHERE 
        a.is_available = true
        AND a.deleted_at IS NULL
        AND ST_DWithin(
            a.coordinates::geometry,
            search_location::geometry,
            radius_meters
        )
        AND (min_price IS NULL OR a.price >= min_price)
        AND (max_price IS NULL OR a.price <= max_price)
    ORDER BY distance;
END;
$$ LANGUAGE plpgsql STABLE;

down.sql:

-- Drop function
DROP FUNCTION IF EXISTS find_nearby_apartments;

-- Drop trigger
DROP TRIGGER IF EXISTS update_apartments_updated_at ON apartments;

-- Drop indexes
DROP INDEX IF EXISTS idx_apartments_coordinates;
DROP INDEX IF EXISTS idx_apartments_location_area;
DROP INDEX IF EXISTS idx_apartments_owner;
DROP INDEX IF EXISTS idx_apartments_availability;
DROP INDEX IF EXISTS idx_apartments_price;

-- Drop table
DROP TABLE IF EXISTS apartments;

Data Types Explanation

1. coordinates POINT:

   • Stores exact apartment location
   • Used for distance calculations and proximity searches

2. location_area GEOMETRY(POLYGON, 4326):

   • Can store complex shapes for areas/neighborhoods
   • SRID 4326 corresponds to WGS84 (standard GPS coordinates)

GraphQL Usage Examples

Query Nearby Apartments

query NearbyApartments(
  $coordinates: point!
  $radius: float!
  $minPrice: numeric
  $maxPrice: numeric
) {
  find_nearby_apartments(
    args: {
      search_location: $coordinates
      radius_meters: $radius
      min_price: $minPrice
      max_price: $maxPrice
    }
  ) {
    id
    name
    distance
    price
    coordinates
  }
}

Verification Steps

1. Apply migration:

   hasura migrate apply

2. Test spatial queries:

   
   -- Test point creation
   SELECT ST_MakePoint(-73.935242, 40.730610);

-- Test proximity search SELECT * FROM find_nearby_apartments( ST_MakePoint(-73.935242, 40.730610), 1000, 1000, 2000 );

## Prerequisites
- [x] PostGIS extension enabled
- [ ] Hasura CLI installed
- [ ] Database connection configured

## Success Criteria
- [ ] Table created with correct data types
- [ ] Spatial indexes created and functioning
- [ ] Helper function working correctly
- [ ] Sample queries execute successfully
- [ ] Migration can be rolled back cleanly

## Additional Context
This schema supports:
- Location-based apartment search
- Price filtering
- Availability filtering
- Soft deletion
- Automatic timestamp management
- Area-based queries

[Stephy-Crown]

Can I contribute to this one?

[Unickhub]

Let me try this one!

[ShantelPeters]

I’d like to work on this.

[derianrddev]

Hi, my name is Derian Rodriguez. I'm a passionate software engineer with more than a year of experience, actively contributing to open source projects through OnlyDust. My experience includes full-stack development with focus on backend, frontend, and web3 development using Starknet and Cairo. In addition, I am part of the Dojo Coding community. I would follow all guidelines and instructions to achieve exactly what you want. In university, I had the opportunity to work with PostGIS for geospatial data management. I would love to contribute and support the development of this project.

[RJMadrigal]

Hi team!

I would like to get this opportunity. I am a systems engineer from Costa Rica, with experience in C#, .NET, React and I am currently learning Cairo and Rust, which makes this project a great opportunity to apply and strengthen my knowledge.

It's my first ODhack and I'm looking for my first contribution, so I'm excited to contribute and learn in this collaborative environment.

[mariocodecr]

Hii! My name is Mario Araya, I’m a Software Developer with 2+ years of experience. I have worked with backend technologies such as Cairo, Java, and C#, as well as frontend development using React, NextJS, and JavaScript/TypeScript. I’ve made contributions to open-source projects, completed a Starknet Bootcamp, exercises on NodeGuardians, finished Starklings, and participated in multiple hackathons. I’m also a member of the Dojo Coding community here in Costa Rica.

Create a migration for the apartments table with PostGIS data types to support location-based searches, distance calculations, and area/neighborhood filtering. The table includes fields for apartment details , coordinates , and location area. Implement spatial indexes for coordinates and location area, a function to find nearby apartments, and a trigger to update the updated_at column. Apply the migration, test spatial queries, and ensure the migration can be rolled back.

[CtpN3m01]

Hi! My name is Saymon Porras, and I’m a Software Developer and an active member of the Dojo Coding community. I am enthusiastic about tackling projects that involve spatial data and database migrations, and this issue provides an excellent opportunity to learn and contribute meaningfully.

I am eager to set up the apartments table using PostGIS data types, ensuring support for location-based features like proximity searches, area filtering, and map-based visualizations. I will carefully implement the migration to include the requested functionalities, such as spatial indexes, triggers for the updated_at column, and the helper function for finding nearby apartments.

I will follow the provided guidelines, test spatial queries thoroughly, and ensure the migration is robust and reversible. My focus will be on delivering a high-quality solution while maintaining clean and efficient code. I am excited to contribute to this project and deepen my expertise in this area.

[coxmars]

Hi @rvalenciano, I'm a software developer with 2+ years of experience and I have worked with backend technologies such as Cairo, Java, C# and frontend with React, NextJS, JS/TS. Also, I'm a member of the Dojo Coding community here in CR and I have made contributions in Cairo VM Go, Cairo VM TS, Cairo Lints etc. Likewise, I’m a Cairo developer working in ByteBuildersLabs creating on-chain games using the Dojo framework.

This is my approach:

• First, I will read the contributors guideline if exist, and also I will setup all I need to start.
• I will create the Hasura migration for the apartments table, using PostGIS data types and all the instructions mentioned before.
• Then, I’m going to ensure the table is optimized using the indexes for better performance in search etc.
• Finally, we can validate if my implementation is the expected result, or if I need to improve it.

ET: 3-4 days

[evgongora]

Hello! My name is Erick, I'm part of the Dojo Coding community and would love to contribute to this project. I'm a systems engineer student focusing on full stack web development and would like to tackle this by following the provided instructions.

Let me know if I can hop on this :)

[salazarsebas]

My background

As a member of the Dojo Coding community, I have experience in web2 and traditional technologies, as for web3 I have only been working for a few months and I have managed to make a few contributions, however, it is not an impediment for me to do my best in this issue, I will dedicate the necessary effort to contribute to the OSS.

How I plan on tackling this issue

To solve this issue, I will perform the following steps:

• Understand how PostGIS data works.
• Create the table of apartments.
• Store the locations.
• To be able to make search queries, distance calculations, filtering and visualization.
• Make the migration to Hasura. Use the context and suggestions to come up with the best solution to the issue.

[rvalenciano]

@evgongora feel free to work on this :)