from Hacker News

The SQLite R*Tree Module

by mpsq on 3/10/21, 9:39 PM with 15 comments

• by bob1029 on 3/10/21, 10:16 PM

  I have started to play around with building a software rasterizer from scratch, and I am wondering if this might be something that I could potentially leverage to cheat at some of the math problems. The document mentions CAD, but I am not sure if the performance is such that real-time (i.e. 30+fps) queries would be feasible over meaningful datasets.

  Does anyone have any experience directly using SQL for this sort of thing? I feel like there is something here with being able to define a 3d scene in terms of normalized SQL tables, and then running queries over them in order to produce intermediate/final output.

• by yowlingcat on 3/10/21, 10:11 PM

  Having used this in production before, it's an absolutely fantastic tool for specific use cases. My use case was indexing fairly static medium cardinality geo data for lookup within a microservice that we hit from our primary monolith. You get fast spatial indexes and essentially zero additional operational overhead.

• by centimeter on 3/10/21, 10:11 PM

  I love SQLite's software model (embeddable library rather than a separate binary), but I often sorely miss the rich data model provided by postgres. I miss things like strong type checking, special types to represent times/coordinates/etc., and advanced index types like GiST indices.

• by yread on 3/10/21, 10:37 PM

  Note that even though it's a module lot of distributions of Sqlite come with it enabled. You can check it by running

  PRAGMA compile_options;

  and looking for ENABLE_RTREE

• by jokoon on 3/11/21, 4:17 AM

  Mmmmh does spatialite use it? I guess yes. Spatialite is great even though the queries are a little tedious.