chore(persistence): Add PostGIS for spatial queries at scale

[arunderwood]

Summary

When spot volume exceeds 10K records, add GEOGRAPHY columns and spatial indexes for efficient radius queries. Migration path from in-code Haversine to PostGIS ST_DWithin.

Context

Currently using in-code Haversine calculations. PostGIS provides database-level spatial indexing for better performance at scale.

---

Dependencies

Requires: #267 (Spots Phase 1) complete - gridsquares stored in spots table

Trigger Conditions

Condition	Action
Spot volume > 100K/day	Consider PostGIS
"Near me" feature requested	Implement PostGIS
In-code spatial queries < 100ms	Defer PostGIS

Migration Path

Step 1: Add nullable GEOGRAPHY columns

ALTER TABLE spots ADD COLUMN spotter_location GEOGRAPHY(POINT);
ALTER TABLE spots ADD COLUMN spotted_location GEOGRAPHY(POINT);

Step 2: Backfill from gridsquares

UPDATE spots SET
    spotter_location = ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography
WHERE spotter_location IS NULL;

Step 3: Add spatial index

CREATE INDEX idx_spots_spotter_geo ON spots USING GIST(spotter_location);

Step 4: Replace queries

// Before (in-code)
spots.stream().filter(s -> haversine(s, userLocation) < 500).toList();

// After (PostGIS)
@Query("SELECT * FROM spots WHERE ST_DWithin(spotter_location, :loc, 500000)")
List<SpotEntity> findNearby(@Param("loc") Point location);

Performance Comparison

Records	In-Code Haversine	PostGIS ST_DWithin
10K	~10ms	~2ms
100K	~100ms	~5ms
1M	~1s	~10ms