check eq and lt for multiple fields

I have this index defined in my schema:

    .index("coord_idx", ["lon", "lat"]),

    .index("coord_idx", ["lon", "lat"]),

Now I want to check if something is in a bounding box by using this index:

const citiesInBoundingBox = await ctx.db
      .query("search")
      .withIndex("coord_idx", (q) =>
        q
          .gt("lon", args.coord.lon - 0.1)
          .lt("lon", args.coord.lon + 0.1)
          .gt("lat", args.coord.lat - 0.1)
          .lt("lat", args.coord.lat + 0.1),
      );

const citiesInBoundingBox = await ctx.db
      .query("search")
      .withIndex("coord_idx", (q) =>
        q
          .gt("lon", args.coord.lon - 0.1)
          .lt("lon", args.coord.lon + 0.1)
          .gt("lat", args.coord.lat - 0.1)
          .lt("lat", args.coord.lat + 0.1),
      );

Unfortunately, this is not possible because you can only chain one gt and one lt operator on this. DISCLAIMER, HAS NOTHING TODO WITH MY FEATURE REQUEST: (working on the geohashes (maybe with the article I will fully understand or know how to implement it) btw. but thought this would be a much quicker solution to get it running.

9 Replies

Michal Srb•11mo ago

Indeed! You want to use geohash and then filter in JS / without an index. The index can only give you a single "contiguous" range in the index tree. The filter you have would ask "give me a lon range, and then inside each of those 'trees' give a lat range".

FleetAdmiralJakob 🗕 🗗 🗙OP•11mo ago

Ok, so I have to create an index on the lon coord and then filter the rest without an index?

Michal Srb•11mo ago

Yeah, you can do that It's inefficient compared to geohashing or quadtrees but it's possible

FleetAdmiralJakob 🗕 🗗 🗙OP•11mo ago

yes, i'm working on it 😂, but maybe im just to stupid to know how to do it (especially with having in mind that I have to run my geohash search algorithm through convex)

erquhart•11mo ago

If your search table is large enough that js filtering has real observed performance penalties that are unacceptable, I wonder if forking your data into two tables could work. One for long, one for lat, and join them on a third table with the rest of the data for a given row. Haven't thought this all the way through but there may be a path there.

Michal Srb•11mo ago

Here's a graphical explanation of why the indexed query in your original question doesn't work

Michal Srb•11mo ago

@erquhart the join is worse. If the number of items is O(n^2), then using one index loads up O(n) documents and filters them in O(n). Whereas doing a join is 2 x O(n) documents loaded plus the join which we can say is O(n x log n).

erquhart•11mo ago

Yeah good point

ian•11mo ago

using h3 for a single string to query for is my general advice for these things. However, I've been curious to use the vector search for this - just make a 2-dimensional vector index and put lat/long in there and search [lat, long] to find the closest elements

check eq and lt for multiple fields

Did you find this page helpful?