Does SQLite have GIS capabilities (like PostGIS provides for Postgres), or is this storing the data as traditional database primitives?

SQLite can be built with the R*tree module, which supports efficiently looking up all bounding boxes that contain a point: https://www.sqlite.org/rtree.html

PostGIS similarly provides an R*Tree index mode, as well as a heap of functions for doing GIS calculations directly. To do that in SQLite, you'd implement and inject custom functions as appropriate.

Check out Spatialite. https://www.gaia-gis.it/fossil/libspatialite/index

DuckDB has put some effort into GIS, so if SQLite does not immediately fit the bill, I would go there.

https://motherduck.com/blog/getting-started-gis-duckdb/