← back to Stayclaim
src/app/api/stars/nearby/route.ts
152 lines
/**
* /api/stars/nearby?lat=&lng=&radius= — server-side haversine query
*
* Returns Walk of Fame stars + historical residences + active restaurants
* within `radius` meters of the given lat/lng, sorted nearest first. Cap 60.
*
* Used by /stars/nearby client component (geolocation flow).
*
* Hardening notes:
* - All four numerics ($1=lat, $2=lng, $3=radius, $4=earth_m) are bound params,
* no SQL template-literal interpolation
* - lat/lng must fall inside the LA bounding box (32–35 N, 116–120 W) — every
* legitimate user is here, and a wider value would scan all 3 tables
* - res CTE deduped via DISTINCT ON to collapse legacy + new seed dupes
* - rst CTE filters closed_year IS NULL (active restaurants only)
*/
import { NextResponse } from 'next/server';
import { pool } from '@/lib/db';
import { rateLimit } from '@/lib/rate-limit';
export const runtime = 'nodejs';
export const dynamic = 'force-dynamic';
const EARTH_M = 6371000;
// LA-area bbox — anything outside is either an error or an attacker probing
const LAT_MIN = 32, LAT_MAX = 35;
const LNG_MIN = -120, LNG_MAX = -116;
export async function GET(req: Request) {
const rl = await rateLimit('stars');
if (!rl.ok) {
return NextResponse.json(
{ ok: false, error: 'rate_limited' },
{ status: 429, headers: { 'Retry-After': String(rl.retryAfter ?? 60) } },
);
}
const { searchParams } = new URL(req.url);
const lat = Number(searchParams.get('lat'));
const lng = Number(searchParams.get('lng'));
const rRaw = Number(searchParams.get('radius'));
const radius = Number.isFinite(rRaw) && rRaw > 0 ? Math.min(rRaw, 50000) : 1609;
if (!Number.isFinite(lat) || !Number.isFinite(lng)) {
return NextResponse.json({ ok: false, error: 'lat/lng required' }, { status: 400 });
}
if (lat < LAT_MIN || lat > LAT_MAX || lng < LNG_MIN || lng > LNG_MAX) {
return NextResponse.json(
{ ok: false, error: 'lat/lng outside LA service area' },
{ status: 400 }
);
}
// Haversine via a SQL CTE that unions all three data sources.
// EARTH_M passed as $4 — no SQL interpolation anywhere.
// Use a dedicated client so SET LOCAL statement_timeout sticks to this query only.
const client = await pool.connect();
try {
await client.query(`SET LOCAL statement_timeout = '3s'`);
const { rows } = await client.query<{
kind: 'walk' | 'residence' | 'restaurant';
name: string;
subtitle: string;
slug: string;
lat: number;
lng: number;
distance_m: number;
}>(`
WITH params AS (
SELECT $1::float AS lat, $2::float AS lng,
$3::float AS radius_m, $4::float AS earth_m
),
walk AS (
SELECT 'walk'::text AS kind,
w.honoree_name AS name,
COALESCE(w.category::text, '') || ' · ' || COALESCE(w.address, '') AS subtitle,
w.honoree_slug AS slug,
w.lat::float AS lat, w.lng::float AS lng,
p.earth_m * 2 * asin(sqrt(
sin(radians((w.lat - p.lat)/2))^2 +
cos(radians(p.lat)) * cos(radians(w.lat)) *
sin(radians((w.lng - p.lng)/2))^2
)) AS distance_m
FROM walk_of_fame w, params p
WHERE w.lat IS NOT NULL AND w.lng IS NOT NULL
AND w.lat BETWEEN p.lat - (p.radius_m / 111000.0) AND p.lat + (p.radius_m / 111000.0)
AND w.lng BETWEEN p.lng - (p.radius_m / 88000.0) AND p.lng + (p.radius_m / 88000.0)
),
res AS (
SELECT DISTINCT ON (epa.entity_id, epa.listing_id)
'residence'::text AS kind,
e.display_name AS name,
COALESCE(l.title, '') || ', ' || COALESCE(l.city, '') AS subtitle,
l.slug AS slug,
l.latitude::float AS lat, l.longitude::float AS lng,
p.earth_m * 2 * asin(sqrt(
sin(radians((l.latitude - p.lat)/2))^2 +
cos(radians(p.lat)) * cos(radians(l.latitude)) *
sin(radians((l.longitude - p.lng)/2))^2
)) AS distance_m
FROM entity_place_association epa
JOIN entity e ON e.id = epa.entity_id
JOIN listing l ON l.id = epa.listing_id, params p
WHERE epa.public_visible = true
AND l.is_public = true
AND l.latitude IS NOT NULL AND l.longitude IS NOT NULL
-- bbox pre-filter (radius_m / 111000 degrees) before haversine
AND l.latitude BETWEEN p.lat - (p.radius_m / 111000.0) AND p.lat + (p.radius_m / 111000.0)
AND l.longitude BETWEEN p.lng - (p.radius_m / 88000.0) AND p.lng + (p.radius_m / 88000.0)
ORDER BY epa.entity_id, epa.listing_id, epa.created_at DESC NULLS LAST
),
rst AS (
SELECT 'restaurant'::text AS kind,
r.name AS name,
COALESCE(r.cuisine, 'Restaurant') || ' · ' ||
COALESCE(sc.cnt::text, '0') || ' sightings' AS subtitle,
r.slug AS slug,
r.lat::float AS lat, r.lng::float AS lng,
p.earth_m * 2 * asin(sqrt(
sin(radians((r.lat - p.lat)/2))^2 +
cos(radians(p.lat)) * cos(radians(r.lat)) *
sin(radians((r.lng - p.lng)/2))^2
)) AS distance_m
FROM restaurant r
LEFT JOIN LATERAL (
SELECT COUNT(*) AS cnt FROM restaurant_sighting s
WHERE s.restaurant_id = r.id AND s.public_visible = true
) sc ON true
, params p
WHERE r.lat IS NOT NULL AND r.lng IS NOT NULL
AND r.closed_year IS NULL
AND r.lat BETWEEN p.lat - (p.radius_m / 111000.0) AND p.lat + (p.radius_m / 111000.0)
AND r.lng BETWEEN p.lng - (p.radius_m / 88000.0) AND p.lng + (p.radius_m / 88000.0)
)
SELECT * FROM walk WHERE distance_m <= (SELECT radius_m FROM params)
UNION ALL
SELECT * FROM res WHERE distance_m <= (SELECT radius_m FROM params)
UNION ALL
SELECT * FROM rst WHERE distance_m <= (SELECT radius_m FROM params)
ORDER BY distance_m ASC
LIMIT 60
`, [lat, lng, radius, EARTH_M]);
return NextResponse.json({ ok: true, stars: rows });
} catch (e) {
console.error('[api/stars/nearby] DB error:', e);
return NextResponse.json({ ok: false, error: 'query failed' }, { status: 500 });
} finally {
client.release();
}
}