[BACKEND] Geocode event locations — 9 of 11 scrapers write no coordinates, so most events can never appear on the map

[linear]

Explore's map shows only a fraction of the events in the feed. The cause is upstream of the map: most events have no coordinates at all, so there is nothing to pin.

Current state

explore.tsx builds its pins from locatedEvents, which filters the feed down to events with a non-null latitude and longitude. Anything without coordinates is silently dropped from map view — it still appears in the list, which is why the two views disagree.

events.latitude / events.longitude (schema.sql:139-140) are only ever written from what a scraper supplies, and almost no scraper supplies them:

Coordinates	Scrapers
**None — hardcoded **latitude: null	cns, cockrell, finearts, lawSchool, liberalArts, mccombs, moody, texasGlobal, texasToday
Real coordinates	hornslink, pharmacy

Nine of eleven. Those nine parse a human-readable location into location_full and stop there — nothing ever turns "110 Inner Campus Drive, Austin TX" into a point.

In production this is 19 events with coordinates out of 277.

There is no geocoding anywhere in server/src today.

Worth being explicit, since it has already caused one round of confusion: this is not the same bug as #398. That fixed events sharing an identical coordinate stacking into one pin. This is events having no coordinate.

What already exists on the client — read this first

Kamsi built a client-side resolver three weeks ago on frontend/event-detail-map-modal (commits ac9ab7a, ec0768d). It was never PR'd and had drifted 35 commits behind; it is now rebased onto current main and typechecks clean. It is complementary to this ticket, not a replacement, and one piece of it is a dependency.

What it ships:

• modules/expo-local-search — a native iOS module wrapping Apple's MKLocalSearch, the POI engine behind the Maps app search bar. It resolves building and landmark names ("Texas Union Ballroom") that a postal geocoder cannot, and takes UT's centre as a search region so campus buildings outrank same-named places elsewhere.
• POST /events/:id/coordinates — a backfill endpoint. The first iOS client that resolves an event's label writes the result back. Fills only when both columns are currently NULL, so a real coordinate is never overwritten and concurrent resolvers converge on the first write.
• Coordinate resolution in the create-event submit path, so student-posted events store a pin instead of waiting for a viewer.

What it does not cover, and why this ticket still stands:

1. **iOS only. **searchPlace returns null on Android and web, so those users fall back to a plain geocode path that fails on room-and-building strings.
2. Lazy, one event at a time. A pin is filled only when someone opens that specific event on an iPhone. The map is empty on first load and stays mostly empty; 277 events do not populate by browsing.
3. Nothing touches the scrape path. Every new scrape run re-adds uncoordinated rows.

Build steps

1. Add a geocoding module — server/src/lib/geocode.ts, one provider behind one function: geocodeLocation(env, locationFull): Promise<{ latitude, longitude } | null>. Return null on a miss rather than throwing; ingest must not fail because an address didn't resolve.
2. Cache by normalised location string. Campus events repeat the same handful of buildings, so the same string should be looked up once ever, not once per event per scrape. A geocoded_locations table keyed on the normalised string (lowercased, whitespace-collapsed) with latitude, longitude, resolved_at, and a negative-result marker so failures aren't retried on every run.
3. **Call it from ingest, not from each scraper. **ingestEvents in server/src/events/ingest.ts already batches classification for the whole run; geocoding belongs beside it for the same reason. Only for rows arriving without coordinates — a scraper that provides its own always wins.
4. **Skip online events. **events.venue_type exists as of [BACKEND] Structured venue type — separate in-person vs online from the free-text location #381. venue_type = 'online' has no physical location and must not be geocoded or pinned.
5. Backfill. A one-off route or script over existing rows where latitude IS NULL AND venue_type = 'in_person', reusing the same cache.
6. Rate limits. A scrape ingests ~100 events; every provider caps requests per second. Throttle, and let a batch finish with some events ungeocoded rather than failing the run.
7. **Reuse the existing write path. **POST /events/:id/coordinates already encodes the fill-only-when-NULL rule. Server-side geocoding should share that guard rather than write a second one with different semantics — otherwise a scrape and a client resolver can disagree about who wins.

Notes

• A wrong pin is worse than no pin. If the provider returns nothing, or something outside a sanity box around Austin, leave the coordinates NULL. The event still shows in list view. A pin on the wrong building is a bug users report; a missing pin is a gap they don't notice.
• Provider is an open decision. Google Geocoding, Mapbox and Nominatim differ on price, licensing and whether results may be stored — Nominatim's terms are the restrictive one and matter here because step 2 stores results. Needs a call before implementation, and an API key in the Worker's secrets either way.
• Most of these locations are UT buildings and room numbers ("GDC ATRIUM", "BLT 2.503", "CPE Lawn") rather than street addresses. A generic geocoder will do poorly on those. MKLocalSearch handling exactly this case is evidence for the lookup-table-first approach — a small campus building table may get further on the common cases than any provider call, with the provider as fallback.
• Ordering: the client resolver can ship independently and start filling pins immediately. It does not block this work, and this work does not block it — they write to the same columns under the same NULL guard.
• Frontend needs no change: locatedEvents picks up whatever has coordinates, so pins appear as rows get filled in.

Source

Kamsi, in Slack: "since you would be working on a fix from text to POI, could you also handle the same thing for explore page — only some events show up from what Bodan did."

[linear]

LOOP-275