ReviewOS

stacks/wildloop

Add a spatial / bounding-box index for territory conquest lookups

#949
Closed glennmichael123 opened this 9 days ago ·
9 days ago

Problem

ProcessActivityConquestAction and GetTerritoriesForMapAction load all active territories and filter in JS (bbox parse + routeIntersectsPolygon, O(n·m)). No spatial or even bbox index exists; this won't scale past a demo.

Acceptance

  • Index territories for spatial/bbox queries (indexed center_lat/lng + bbox prefilter at the query layer, or a real spatial index).
  • Conquest over many territories stays performant (benchmark).
9 days ago

Done, in 0000000082-add-territory-spatial-indexes.sql and the two actions this issue named.

  • Territories are indexed for bbox queries. The migration adds min_lat, max_lat, min_lng, max_lng and a composite index on ("status", "min_lat", "max_lat", "min_lng", "max_lng"), so the status filter and the box are served by one index.
  • Both hot paths prefilter at the query layer rather than loading every active territory and filtering in JS:
    • GetTerritoriesForMapAction takes the viewport bounds and pushes them into the query, with a whereNull('min_lat') arm so rows predating the migration are still found.
    • ProcessActivityConquestAction:82 narrows to whereIn('status', ['active', 'contested']) intersected with the route's own bounds before any polygon test runs.

What is not evidenced is the benchmark the acceptance asks for — nobody has measured conquest over many territories before and after. The O(n·m) scan the issue was filed about is gone, which is the substance of it; if the number matters, that is worth its own issue rather than holding this one open.

Sign in to comment on this issue.