RoamAbout needs routes. Lots of them. Each route needs trail geometry (the actual path on a map), milestone positions (25%, 50%, 75%, 100%), and geotagged photography at each milestone. If building one route takes a week, the content library will be empty at launch and I'll be hand-tracing trails until I lose my mind.
So the first Pre-Production spike was: can I build a pipeline that curates a complete route in under a day?
The Pipeline
I settled on a 5-step process, tested against the Brooklyn Bridge Promenade as a case study:
Step 1: Trail geometry from OpenStreetMap. The Overpass API returns trail segments as ordered coordinate arrays. For the Brooklyn Bridge, that's 18 segments that need to be stitched into a single continuous path.
Step 2: Segment stitching. Here's where it got interesting. OSM segments aren't pre-ordered. They're just individual pieces of a trail with no guarantee about which connects to which. I wrote a greedy nearest-neighbor algorithm that matches endpoints with ~5 meter tolerance. Start with any segment, find the closest unconnected endpoint, stitch, repeat. The Brooklyn Bridge's 18 segments collapsed into a clean 49-point path.
Step 3: Milestone positions. Given a continuous path, calculating milestone positions at 25/50/75/100% is straightforward math. PostGIS has ST_LineInterpolatePoint() which does exactly this. Feed it a LineString and a fraction, get back exact coordinates.
Step 4: Photo discovery. Wikimedia Commons has a geosearch API that returns CC-BY licensed photos near any coordinate. For each milestone position, I search a radius and get 5+ geotagged photos to choose from. The manual work is selecting the best photo, not finding them.
Step 5: Store everything in Supabase. Route geometry, milestone coordinates, photo URLs, and attribution metadata all go into PostgreSQL with PostGIS extensions.
The Results
Brooklyn Bridge Promenade (1.1 miles): ~30 minutes from zero to fully curated route with geometry, milestones, and photography.
At 15 launch routes, that's 2-3 days of total curation work. Not weeks. Not months. Days.
The Surprise: Long Trails
For shorter trails (bridges, park loops, urban walks), the segment-stitching approach works perfectly. But what about the Appalachian Trail? That's 2,190 miles of segments.
Turns out OSM has a concept called "relations" that group trail segments in order. For major long-distance trails, I can query the relation directly and get pre-ordered segments. No stitching needed. The algorithm gracefully handles both cases: short trails get stitched, long trails use relations.
User Position on Route
The other piece I validated: can I show a user's position on the trail in real-time?
PostGIS makes this almost trivially easy. Given a user's total distance traveled and the route's total distance, I compute a progress fraction. ST_LineInterpolatePoint(route_geometry, progress_fraction) returns the exact map coordinate where the user's pin should be.
-- Where is the user on this trail?
SELECT ST_LineInterpolatePoint(
route.geometry,
user_route.distance_traveled / route.total_distance
) AS user_position
FROM routes route
JOIN user_routes user_route ON user_route.route_id = route.id
WHERE user_route.user_id = $1;During a live journey (the user is actively walking with the app open), the pin animates smoothly between positions as step count updates flow in from the motion coprocessor. During passive mode (app closed, HealthKit syncing in the background), the pin jumps to the new position next time they open the app.
What This Validated
The route pipeline spike answered the critical question: content creation is fast enough to launch with a meaningful library. It also proved the PostGIS approach works for both route storage and real-time position calculation.
With both Pre-Production spikes validated (this one and the live journey mode spike), I had full confidence to move into Production. The two hardest unknowns were resolved before writing a single line of app code.