7 ms·

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles https://www.mapbox.com/help/an-open-platform/#mbtiles

notacoward 9y ago
FYI, in sqlite is still on disk.
- elchief 9y ago
  It doesn't have to be. There's an in-memory option
  - Myrth 9y ago
    I wonder if ram fs would perform exact the same as sqlite in memory.
    - DaiPlusPlus 9y ago
      The article speculates that the overhead of calling `fopen` for each file and subsequently reading it is more expensive than reading from an already opened database file. But as for how expensive `fopen` is really depends many factors.
    - kccqzy 9y ago
      I would believe ramfs would be slower, as there will be additional system calls needed.
  - notacoward 9y ago
    Filesystems can be built in memory (ramdisks) too, but comparing sqlite in memory to files on disk would be pretty silly. For any serious and fair comparison, in sqlite still means on disk.
- Chris2048 9y ago
  Do you mean "stored on a traditional file-system"?
- microcolonel 9y ago
  For what it's worth, I think Mapbox's vector tiles can be stored in around 48GiB of RAM, which is perfectly within reason for a server. It's also the sort of data which is simple to shard. They can have multiple geolocated databases for a local area, and maybe fewer for international queries. This can be done pretty simply at the load balancer level.
asmosoinio 9y ago
Interesting and very relevant to the discussion! Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?
- i_feel_great 9y ago
  There is some mention of that here: https://www.sqlite.org/rtree.html https://www.sqlite.org/rtree.html
- mynewtb 9y ago
  GeoPackage is a great geodata format on sqlite. Also check out spatialite.
- shawn-butler 9y ago
  Try SpatialLite[0] if you need advanced geometry capabilities. Has R-Tree spatial indexes if you use SQLite 3.6+, and rudimentary MBR index for prior versions. Has rudimentary support for curves as well. [0]: https://www.gaia-gis.it/fossil/libspatialite/index https://www.gaia-gis.it/fossil/libspatialite/index
  - justinclift 9y ago
    As a data point, one of our users figured out how to load the SpatiaLite extension for DB Browser for SQLite (on Windows), then wrote up step by step instructions: https://github.com/sqlitebrowser/sqlitebrowser/wiki/SpatiaLite-on-Windows https://github.com/sqlitebrowser/sqlitebrowser/wiki/SpatiaLi... (people had been asking previously, but we had no idea. ^\o/^)
cat199 9y ago
Definitely an interesting use case - Part of the concerns outlined in that article and of more importance for this company are ease of distribution/compatibility for customers of mapsets and mobile devices, rather than raw read/write performance, however. Though the storage size claims might be relevant, and definitely a clear example that sqlite can be very workable and performant in the context of blob storage.
incanus77 9y ago
Originator of MBTiles format here. The initial intentions were moving 100,000s of raster map tiles between computer and mobile device, either over USB or network. Main benefit was avoiding per-file transaction overhead, as well as checksumming potential. Side benefits included a small space savings over filesystem because of block overhead, plus later iterations allowed for de-duplication of e.g. all-blue water map tiles. These days, we still use the format for transport between local machine and the Mapbox backend, despite having moved to "vector tiles", which essentially are protocol buffer-based encoded geometries directly. Original post: https://developmentseed.org/blog/2010/oct/08/portable-map-tiles-format-released/ https://developmentseed.org/blog/2010/oct/08/portable-map-ti...
- incanus77 9y ago
  Also, to clarify a bit on why spatial capabilities weren't part of the MBTiles spec: when rendering maps, you assemble tiles for a given zoom scale in the vertical and horizontal directions, like a checkerboard. The unique tuple defining a particular square on the map is z/x/y — zoom level, horizontal, vertical. So stuffing these into SQLite and uniquely keying across z/x/y there makes for fast retrieval at render time.
- justinclift 9y ago
  As a data point, many of the links on that page to the mapbox.com website are 404. :(

notacoward 9y ago

FYI, in sqlite is still on disk.

elchief 9y ago
It doesn't have to be. There's an in-memory option
- Myrth 9y ago
  I wonder if ram fs would perform exact the same as sqlite in memory.
  - DaiPlusPlus 9y ago
    The article speculates that the overhead of calling `fopen` for each file and subsequently reading it is more expensive than reading from an already opened database file. But as for how expensive `fopen` is really depends many factors.
  - kccqzy 9y ago
    I would believe ramfs would be slower, as there will be additional system calls needed.
- notacoward 9y ago
  Filesystems can be built in memory (ramdisks) too, but comparing sqlite in memory to files on disk would be pretty silly. For any serious and fair comparison, in sqlite still means on disk.
Chris2048 9y ago
Do you mean "stored on a traditional file-system"?
microcolonel 9y ago
For what it's worth, I think Mapbox's vector tiles can be stored in around 48GiB of RAM, which is perfectly within reason for a server. It's also the sort of data which is simple to shard. They can have multiple geolocated databases for a local area, and maybe fewer for international queries. This can be done pretty simply at the load balancer level.

asmosoinio 9y ago

Interesting and very relevant to the discussion! Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?

i_feel_great 9y ago
There is some mention of that here: https://www.sqlite.org/rtree.html https://www.sqlite.org/rtree.html
mynewtb 9y ago
GeoPackage is a great geodata format on sqlite. Also check out spatialite.
shawn-butler 9y ago
Try SpatialLite[0] if you need advanced geometry capabilities. Has R-Tree spatial indexes if you use SQLite 3.6+, and rudimentary MBR index for prior versions. Has rudimentary support for curves as well. [0]: https://www.gaia-gis.it/fossil/libspatialite/index https://www.gaia-gis.it/fossil/libspatialite/index
- justinclift 9y ago
  As a data point, one of our users figured out how to load the SpatiaLite extension for DB Browser for SQLite (on Windows), then wrote up step by step instructions: https://github.com/sqlitebrowser/sqlitebrowser/wiki/SpatiaLite-on-Windows https://github.com/sqlitebrowser/sqlitebrowser/wiki/SpatiaLi... (people had been asking previously, but we had no idea. ^\o/^)

cat199 9y ago

Definitely an interesting use case - Part of the concerns outlined in that article and of more importance for this company are ease of distribution/compatibility for customers of mapsets and mobile devices, rather than raw read/write performance, however. Though the storage size claims might be relevant, and definitely a clear example that sqlite can be very workable and performant in the context of blob storage.

incanus77 9y ago

Originator of MBTiles format here. The initial intentions were moving 100,000s of raster map tiles between computer and mobile device, either over USB or network. Main benefit was avoiding per-file transaction overhead, as well as checksumming potential. Side benefits included a small space savings over filesystem because of block overhead, plus later iterations allowed for de-duplication of e.g. all-blue water map tiles. These days, we still use the format for transport between local machine and the Mapbox backend, despite having moved to "vector tiles", which essentially are protocol buffer-based encoded geometries directly. Original post: https://developmentseed.org/blog/2010/oct/08/portable-map-tiles-format-released/ https://developmentseed.org/blog/2010/oct/08/portable-map-ti...

incanus77 9y ago
Also, to clarify a bit on why spatial capabilities weren't part of the MBTiles spec: when rendering maps, you assemble tiles for a given zoom scale in the vertical and horizontal directions, like a checkerboard. The unique tuple defining a particular square on the map is z/x/y — zoom level, horizontal, vertical. So stuffing these into SQLite and uniquely keying across z/x/y there makes for fast retrieval at render time.
justinclift 9y ago
As a data point, many of the links on that page to the mapbox.com website are 404. :(