Create Proximity Search Function

sotoJ24 commented 4 days ago

Implement a database function to retrieve apartments within a specified radius of a given location, leveraging the coordinates field in the apartments table.

Problem

We need:

To enable proximity-based searches for apartments.
To use geospatial queries for efficient location-based filtering.
To allow filtering based on availability and radius.

Solution

1. Add Proximity Search Function

Create a migration for the function:

hasura migrate create create_proximity_search_function

`up.sql`

-- Create function to find apartments within a radius  
CREATE OR REPLACE FUNCTION nearby_apartments(location GEOGRAPHY, radius FLOAT)  
RETURNS TABLE(  
    id UUID,  
    name TEXT,  
    description TEXT,  
    price DECIMAL,  
    warranty_deposit DECIMAL,  
    coordinates GEOGRAPHY,  
    address JSONB,  
    is_available BOOLEAN,  
    available_from TIMESTAMP WITH TIME ZONE,  
    available_until TIMESTAMP WITH TIME ZONE  
)  
AS $$  
BEGIN  
    RETURN QUERY  
    SELECT  
        id,  
        name,  
        description,  
        price,  
        warranty_deposit,  
        coordinates,  
        address,  
        is_available,  
        available_from,  
        available_until  
    FROM apartments  
    WHERE  
        is_available = TRUE AND  
        ST_DWithin(coordinates, location, radius);  
END;  
$$ LANGUAGE plpgsql;  

-- Grant execute permission to Hasura role  
GRANT EXECUTE ON FUNCTION nearby_apartments(location GEOGRAPHY, radius FLOAT) TO PUBLIC;

`down.sql`

-- Drop proximity search function  
DROP FUNCTION IF EXISTS nearby_apartments(location GEOGRAPHY, radius FLOAT);

2. Test the Function

Example Query

-- Find apartments within 5 km of a point  
SELECT * FROM nearby_apartments(  
    ST_SetSRID(ST_MakePoint(-80.191788, 25.761681)::GEOGRAPHY, 4326),  
    5000  
);

Prerequisites

[ ] apartments table created with coordinates field (GEOGRAPHY type).
[ ] PostGIS extension installed in the database.

Success Criteria

[ ] Function nearby_apartments created successfully.
[ ] Function returns correct results for test cases.
[ ] Filtering by availability and radius works as expected.
[ ] Integration tested via GraphQL API.

Verification Steps

Apply Migration:
```
hasura migrate apply  
```
Test Queries:
- Use SQL to verify the function's output with various test cases.
- Confirm it excludes unavailable apartments.
GraphQL Integration:
- Expose the function in Hasura using a custom query.

Example Custom Query in Hasura:

type: query  
name: nearby_apartments  
definition:  
  arguments:  
    location: { type: geography! }  
    radius: { type: float! }  
  output_type:  
    type: array  
    of:  
      type: apartments  
  handler:  
    function: nearby_apartments

Test GraphQL Query:

query NearbyApartments($location: geography!, $radius: float!) {  
nearby_apartments(location: $location, radius: $radius) {  
id  
name  
price  
address  
is_available  
}  
}

Additional Context

This setup will:

Enable location-based searches for apartments.
Enhance user experience by allowing tenants to find nearby apartments efficiently.
Ensure optimized database queries for scalability.

Related Issues

25 Create Apartments Table

od-hunter commented 4 days ago

Hi @sotoJ24 , can I work in this please?

emarc99 commented 4 days ago

I have experience deploying and interacting with subgraph. Let me do this.

rvalenciano commented 3 days ago

Hey, FYI, in order to work on this one, we need to finish this first.

od-hunter commented 3 days ago

Hi @sotoJ24 , please why are you closing the issue you assigned to me when I’ve not done it? I thought I was to wait for #17 first?

sotoJ24 commented 3 days ago

Dear @od-hunter, that issue depends on another one, at the moment it is not possible to work on it, but we can assign this https://github.com/safetrustcr/Backend/issues/27 one instead, apply now and I will assign it

Regards

od-hunter commented 3 days ago

Dear @od-hunter, that issue depends on another one, at the moment it is not possible to work on it, but we can assign this #27 one instead, apply now and I will assign it

Regards

@sotoJ24 , thanks for the clarity ser, I’d wait till when I can start working on this issue already assigned to me, thank you🙏🏻

sotoJ24 commented 3 days ago

Dear @od-hunter, that issue depends on another one, at the moment it is not possible to work on it, but we can assign this #27 one instead, apply now and I will assign it Regards

@sotoJ24 , thanks for the clarity ser, I’d wait till when I can start working on this issue already assigned to me, thank you🙏🏻

Got it, You're welcome

safetrustcr / Backend