Hoomzz: Architecting Dubai’s
Next-Gen Property Marketplace

// PostGIS Geospatial Polygon Radius Search for Dubai Communities
// File: src/services/geospatial/PostGISDubaiSearch.ts

import { Pool } from 'pg';

export async function searchPropertiesInDubaiCommunity(
  pool: Pool,
  communityGeoJsonPolygon: string,
  minPriceAED: number,
  maxPriceAED: number,
  bedrooms: number
) {
  const query = `
    SELECT 
      id, title, price_aed, bedrooms, bathrooms,
      dld_title_deed_verified, rera_trakheesi_permit,
      ST_AsGeoJSON(location_point) as location,
      ST_Distance(location_point, ST_Centroid(ST_GeomFromGeoJSON($1))) as distance_from_center
    FROM dubai_property_listings
    WHERE 
      ST_Contains(ST_GeomFromGeoJSON($1), location_point)
      AND price_aed BETWEEN $2 AND $3
      AND bedrooms >= $4
      AND rera_permit_status = 'ACTIVE'
    ORDER BY rera_verified_at DESC, price_aed ASC
    LIMIT 100;
  `;

  const values = [communityGeoJsonPolygon, minPriceAED, maxPriceAED, bedrooms];
  const { rows } = await pool.query(query, values);
  return rows;
}