Replacing Geospatial Database Reads with Static JSON Tiles and Cloudflare

Every OdoKeep user looking for nearby fuel prices was asking a different question against the same data.

The location changed. The radius changed. The selected fuel changed. The underlying station list was shared by everyone and rebuilt on a daily schedule.

That was enough to reconsider where the query happened.

OdoKeep is a React Native vehicle logbook. Its fuel-price feature consumes national feeds from Portugal, Spain, France, Italy, and Austria. The tile module documents a working set of roughly 46,000 stations; the count changes with the feeds. Originally, the app asked the backend for nearby stations and country-level brand counts.

The current price-serving path reads JSON files from Cloudflare R2 through a public custom domain. Supabase still handles the private garage data. The publishing job also reads source attribution metadata and reports its health. What went away was the need for the database to answer each phone's fuel-price lookup.

I could have published one country file and let the app filter it. That would be easy to build, but a nearby lookup should not require downloading a country's entire station list. The shared dataset needed a partition that matched the way people use the screen.

One grid module for the publisher and the phone

The partition is a half-degree grid. lib/fuel-prices/tiles.ts contains the grid arithmetic used by both the publisher and the client:

const TILE_DEGREES = 0.5;

function tileKey(lat: number, lon: number): string {
  return `${Math.floor(lat / TILE_DEGREES)}_${Math.floor(lon / TILE_DEGREES)}`;
}

Lisbon at 38.72, -9.14 lands in 77_-19. The negative coordinate makes Math.floor significant: truncation would put points west of Greenwich in the wrong cell.

The published layout is small enough to describe without an API specification:

fuel/
  index.json
  sources.json
  brands/
    PT.json
    ES.json
    ...
  tiles/
    77_-19.json
    77_-18.json
    ...

A tile contains station facts: coordinates, identity, address information, prices, opening hours when supplied, and source update times. It does not contain distance from the user. Distance belongs to a lookup, not to a station.

Fetch a bounding box, then filter by distance

For a search, the client first computes the cells covering the circle's bounding box. The approximate latitude span comes from the radius divided by kilometres per degree. The longitude span is widened by the cosine of latitude, because longitude degrees become narrower farther north.

Fetching a bounding box is intentionally a little imprecise. A corner tile can be requested even if none of its stations fall inside the circle. After loading, the app uses haversine distance to discard stations outside the requested radius and sort the rest. It keeps the same Earth radius as the query this replaced.

The file count is where the grid size earns its place. A half-degree cell is roughly 56 km tall, with a narrower width at the latitudes served. The default 10 km radius means a 20 km diameter. The tests sample the documented coverage region and assert at most four cells for that default and at most nine for the 30 km radius ceiling.

The diameter is easy to forget when estimating this. A statement such as "a 30 km search fits in a 56 km cell" sounds reasonable until you draw the circle.

A coarser grid would reduce the maximum number of requests but enlarge the files needed for the common search. The chosen grid spends more requests on a large radius so that a small radius can use smaller pieces.

The index makes the empty parts cheap. It lists only cells with stations and carries the grid size and build timestamp. A coastal search can eliminate known empty cells before requesting them. The client rejects an index built on a different grid rather than using keys with the wrong meaning.

Brand counts are also built once per country. Changing the brand filter no longer requires a country-wide aggregation in the database.

Once those files are loaded, changing fuel, brand, or radius can reuse the held stations. The network no longer has to participate in every adjustment to the view.

Empty and failed are different outcomes

The actual tile cache trusts entries for six hours. It holds parsed data in memory, persists copies for later launches, and keeps one in-flight promise per key. Two consumers asking for a tile simultaneously share the request. When a usable old copy exists and the network fails, that copy can still serve the lookup.

The most important cache change was giving tile retrieval three outcomes:

type TileOutcome =
  | { status: "ok"; stations: TileStation[] }
  | { status: "empty" }
  | { status: "failed" };

An empty cell and a cell I could not fetch are different evidence.

Suppose a lookup crosses two populated cells. One returns normally and the other returns a 503 with no usable cached copy. Combining the first cell's stations into a result would silently shrink the searched area. That shorter list could then be sorted and cached as if it answered the whole question.

stationsAround returns failure when any required tile fails. The higher layer can fall back to its previous search cache or explain that it could not retrieve the data. It does not turn a missing piece of the map into a newly complete result.

The current handling of a 404 is more limited: it is treated as an empty outcome and remembered for five minutes. That short lifetime matters during publication, when a cell can be temporarily unavailable. The index avoids many such requests, but a 404 still does not give the same certainty as a versioned, internally consistent dataset.

Publish complete datasets, tiles before index

Publication has a corresponding completeness rule. The TypeScript builder requires every configured country and refuses to publish when any adapter fails. Otherwise, an upstream outage in one country would become a new station set in which that country had disappeared. Keeping the previous full build is preferable to publishing that omission as fresh data.

The scheduled GitHub Action runs the builder with Bun. The sync path uses built-in modules and project source files, so this job does not install the mobile app's dependency tree. Its output is a directory; uploading it is a separate workflow step.

The JSON files are gzipped before upload and served with the appropriate content encoding. gzip -n leaves the timestamp out of the compressed header, so identical input does not acquire different bytes merely because it was compressed on another day. The objects carry a one-hour public cache directive.

Tiles are uploaded before the index. Publishing the index first would advertise new keys that a failed upload might never deliver.

This ordering does not make the deployment atomic. Files are replaced in place, and a client can see a mixture of builds. Pruning can also remove an old tile while a client still holds an earlier index. For current fuel-price browsing, the implementation accepts that limited consistency and uses short-lived missing-tile handling. A dataset requiring a consistent snapshot would need generation-specific paths and a pointer switched only after all files were available.

Where this architecture fits

R2 is useful here because its published pricing has no internet egress bandwidth charge. Storage and operations are still part of the R2 pricing model, and cache configuration still matters. A public custom domain can use Cloudflare caching, as described in the public bucket documentation. I do not have a before-and-after bill to attach to this article; the concrete change is that price lookups no longer consume database reads or database egress.

Daily publication also does not make every station's price current to the minute. The source update time is retained separately, and attribution travels with the dataset. The national feeds have different update behaviour and reuse conditions. Serving a file rather than a row does not change either.

This approach fits because the dataset is public, shared, geographically bounded, and updated much less often than it is read. I would make a different choice for private data, per-user authorisation, or live prices whose freshness depended on each transaction.

For this feature, the phone already knew the location, radius, and filters. Giving it a reusable local slice let it finish the query without repeatedly asking the server to rediscover the same stations.

You can see the resulting fuel-price browser in OdoKeep, alongside the vehicle logbook it supports. The iOS app is available if you want to try it around your own route.