SQLite databases are single, cross-platform files. So are spatial data stored in SQLite databases. Up to this day, shapefiles are one of the most widely used GIS data interchange formats. I see SQLite (SpatiaLite) databases as better suited for this, because of single-file format and because metadata for shapefiles are stored in DBF files that often have problems with non-english encodings, have limited column names, and cannot be larger than 2Gb. SpatiaLite supports 64-bit IEEE Floating point type, has embedded Unicode support and integrated metadata (Projection, etc.)
Embedded and low-resource environments
SQLite packs tremendous power in small memory footprint, as it is carefully written in C, and SpatiLite is not far behind. A drone flying along a river or road, or returning on autopilot – if something of this kind needs basic geospatial abilities, it can be done.
Local SQL and GIS enabled application caches
In many GIS related applications, there is intensive data flow from the main server to the client, because geospatial data are bulky. To reduce these flows, a local cache can help, local database reads are usually faster. If such a cache has SQL and/or spatial abilities, we can simplify much of the code.
Database for a website
Citing the SQLite.org website about typical uses,
SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
Most probably, similar or slightly lower results can be achieved with SpatiaLite extensions. Who knows any real-world cases?
Much of the world has low bandwidth internet connections. In these cases, the solution could be to get the bulk of the spatial data from some offline media and then get only relatively thin updates. Especially, if you have some old computer with limited memory and processing power.
Education and learning
Ease of installation and low resource usage, combined with permissive licence and reusable experience make it suitable for classroom and self-education use. Once one learns to play with GIS in SpatiaLite, it is very easy to migrate to PostGIS.
When SpatiaLite is not the best tool
Whenever there are concurrent writes to the geospatial database, client/server applications and big data (SQLite file size limitation is about 140 terabytes).