The Problem

While building a real estate platform with location-based property notifications, I encountered a critical performance bottleneck. The initial implementation was simple but catastrophically inefficient:

// The naive approach - fetches EVERYTHING
const allProps = await db
  .select()
  .from(properties)
  .where(eq(properties.status, 'active'));

// Then filters 10,000+ properties in memory
const filtered = allProps.filter((p) => {
  const dist = haversine(userLat, userLon, p.latitude, p.longitude);
  return dist <= radius;
});

With 10,000+ active properties in the database, this meant:

• Loading every single property into memory
• Calculating haversine distance 10,000+ times per search
• Network overhead transferring massive datasets
• Response times of 2-5 seconds (unacceptable for modern web apps)

Understanding the Core Issues

Issue 1: Database vs Application Filtering

The fundamental mistake was doing spatial filtering in the application layer. Databases are optimized for filtering data, but we were bypassing that entirely.

Issue 2: The TypeScript Type System Challenge

Our database schema used PostgreSQL's NUMERIC type for coordinates and prices, which TypeScript represents as strings. This created a conflict with our comparison operators:

// This fails - comparing PgNumeric (string) with number
gte(properties.latitude, minLat)  // ❌ Type error!

// This works - converting to string
gte(properties.latitude, minLat.toString())  // ✅

Issue 3: Drizzle ORM Query Chaining

Unlike some ORMs, Drizzle doesn't support multiple .where() calls:

// This doesn't work
let query = db.select().from(properties);
query = query.where(eq(properties.status, 'active'));  // ❌
query = query.where(gte(properties.price, minPrice));  // ❌

You must combine all conditions into a single where() call using and().

The Solution: Two-Stage Filtering

The optimization strategy uses a two-stage approach:

Stage 1: Bounding Box Filter (Database Level)

Instead of fetching all properties, we calculate a rectangular "bounding box" around the user's location and filter at the database level:

// Calculate bounding box
// 1 degree latitude ≈ 111 km
const latDelta = radius / 111;

// Longitude varies by latitude
const lonDelta = radius / (111 * Math.cos(userLat * Math.PI / 180));

const minLat = userLat - latDelta;
const maxLat = userLat + latDelta;
const minLon = userLon - lonDelta;
const maxLon = userLon + lonDelta;

This bounding box represents a rectangular area that's guaranteed to contain all properties within our circular radius (plus some extras at the corners).

Stage 2: Precise Circle Filter (Application Level)

After fetching the reduced dataset, we apply the precise haversine formula to filter to an exact circular radius:

const filtered = candidateProps.filter((p) => {
  const lat = parseFloat(p.latitude);
  const lon = parseFloat(p.longitude);
  const dist = haversine(userLat, userLon, lat, lon);
  return dist <= radius;
});

The Complete Implementation

Here's the production-ready solution:

async findNearbyProperties(userId: number) {
  // Get user location and preferences
  const loc = await this.getUserLocation(userId);
  if (!loc) throw new Error('Location not set');
  
  const prefs = await this.getPreferences(userId);
  const radius = prefs?.radius ?? 5;
  const priceMin = prefs?.priceMin ?? 0;
  const priceMax = prefs?.priceMax ?? Number.MAX_SAFE_INTEGER;
  const types = prefs?.propertyTypes ?? [];
  const minBedrooms = prefs?.minBedrooms ?? 0;

  // Calculate bounding box
  const latDelta = radius / 111;
  const lonDelta = radius / (111 * Math.cos(loc.latitude * Math.PI / 180));
  
  const minLat = loc.latitude - latDelta;
  const maxLat = loc.latitude + latDelta;
  const minLon = loc.longitude - lonDelta;
  const maxLon = loc.longitude + lonDelta;

  // Build database conditions
  const conditions = [
    eq(properties.status, 'active'),
    ne(properties.visibilityStatus, 'archived'),
    isNotNull(properties.latitude),
    isNotNull(properties.longitude),
    // Price filtering with type conversion
    gte(properties.price, priceMin.toString()),
    lte(properties.price, priceMax.toString()),
    // Bounding box with type conversion
    gte(properties.latitude, minLat.toString()),
    lte(properties.latitude, maxLat.toString()),
    gte(properties.longitude, minLon.toString()),
    lte(properties.longitude, maxLon.toString())
  ];

  // Optional filters
  if (minBedrooms > 0) {
    conditions.push(gte(properties.bedrooms, minBedrooms));
  }
  if (types.length > 0) {
    conditions.push(inArray(properties.propertyType, types));
  }

  // Single database query
  const candidateProps = await db
    .select()
    .from(properties)
    .where(and(...conditions));

  // Precise distance filtering
  return candidateProps.filter((p) => {
    const lat = parseFloat(p.latitude || '0');
    const lon = parseFloat(p.longitude || '0');
    if (isNaN(lat) || isNaN(lon)) return false;
    
    const dist = this.haversine(loc.latitude, loc.longitude, lat, lon);
    return dist <= radius;
  });
}

private haversine(lat1: number, lon1: number, lat2: number, lon2: number) {
  const R = 6371; // Earth's radius in km
  const dLat = ((lat2 - lat1) * Math.PI) / 180;
  const dLon = ((lon1 - lon2) * Math.PI) / 180;
  const a =
    Math.sin(dLat / 2) * Math.sin(dLat / 2) +
    Math.cos((lat1 * Math.PI) / 180) *
    Math.cos((lat2 * Math.PI) / 180) *
    Math.sin(dLon / 2) * Math.sin(dLon / 2);
  const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
  return R * c;
}

Performance Results

Metric	Before	After	Improvement
Properties Fetched	10,000+	~50-200	50-200x
Network Transfer	~5MB	~100KB	50x
Response Time	2-5s	<200ms	10-25x
Memory Usage	~150MB	~5MB	30x

Key Takeaways

1. Filter at the Source

Always push filtering logic as close to the data source as possible. Database engines are optimized for this—use them.

2. Understand Your Type System

PostgreSQL's NUMERIC type maps to strings in TypeScript. Type conversions aren't just formalities—they're requirements.

3. Bounding Box + Precise Distance

For geospatial queries, use a two-stage approach:

• Stage 1: Fast rectangular bounding box (database)
• Stage 2: Precise circular distance (application)

4. ORM Constraints Matter

Different ORMs have different query building patterns. With Drizzle, combine all conditions into a single where(and(...conditions)) call.

5. Optimize for the Common Case

Most searches will have a small radius (5-10km). Optimizing for this case gives the best real-world performance.

Future Optimizations

For even better performance at scale, consider:

• PostGIS Extension: Native geospatial indexing and queries
• Spatial Indexes: R-tree or GiST indexes for coordinate columns
• Caching: Redis cache for popular search areas
• Database Partitioning: Partition by geographic regions
• Pre-computed Grids: Divide areas into grid cells for faster lookups

Conclusion

This optimization transformed a feature from "unusably slow" to "production-ready" with a few strategic changes. The lesson? Modern web apps demand sub-second responses, and achieving that often means rethinking your data access patterns from the ground up.

The real win isn't just the speed—it's building a foundation that scales. Whether you have 10,000 or 1,000,000 properties, this approach keeps searches fast and users happy.