Find most similar value in Convex

[small lake]

Hi, I have a convex table with cities and their coordinates. Now I want to query by coordinates what the nearest city would be. Is and if yes how is that possible in Convex?

Do I have to implement the haversine formula? 😅 And if yes: How can I do that with convex?

[torn vessel]

I would use geohashes and index ranges for lookup. We're working on an article on this topic (try searching our discord for geosearch as well).

[small lake]

ok, thank you! can you send me the article when it's done?

[small lake]

Hi, I wrote this code:

https://pastebin.com/RiSSDBQC

It's without some geohashes so I'm interested in seeing how much your approach will be faster but I think it for now I can use it.

heavily inspired by this video:
https://www.youtube.com/watch?v=QgnCB8X_sN4&t=736s

Another problem that I have is that not the whole landarea of the city is compared, but only one single point which could cause problems if you live near the border of a city

[small lake]

btw. my table looks like that: ```ts
export default defineSchema({
search: defineTable({
admin1: v.string(),
admin2: v.string(),
coord: v.object({ lat: v.float64(), lon: v.float64() }),
country: v.string(),
id: v.float64(),
name: v.string(),
})
.searchIndex("search_body", {
searchField: "name",
})
.index("by_city_id", ["id"])
.index("by_lon", ["coord.lon"]),
});

But with that table the last problem (with the landarea) is not fixable

[torn vessel]

If you're doing area lookup, not a point lookup, you want probably want a bounding box for the city (not just one coord), and then lookup based on that (which boils down to range queries and intersections). I'm not sure how this is usually done efficiently, but this is not specific to Convex, so you might be better off trying to get help from ChatGPT, StackOverflow, etc. If you figure it out do let us know how you approached it.

[small lake]

Isnt that a bounding box?

const cities = await ctx.db
      .query("search")
      .withIndex("by_lon", (q) =>
        q
          .gt("coord.lon", args.coord.lng - 0.1)
          .lt("coord.lon", args.coord.lng + 0.1),
      )
      .filter((q) =>
        q.and(
          q.gt(q.field("coord.lat"), args.coord.lat - 0.1),
          q.lt(q.field("coord.lat"), args.coord.lat + 0.1),
        ),
      )
      .collect();

[torn vessel]

I was imagining you want something like this, two overlapping rectangles

[small lake]

to solve the problem with the landarea of the city? yes, for that I would have to save the landarea for each city and then compare the point to that. but since I'm already at my spending limits I think I can't do that right now (and it is not so important anyways).

[torn vessel]

Using a geohash will significantly decrease your DB bandwidth usage btw.

Right now you're scanning a full slice of your world, whereas with a geohash you can scan only selected regions.

[small lake]

ok, but if I add a geohash I need to store that in my db too, since my DB Storage is full and my Bandwith seems fine for now I think I need to optimize for the DB Storage

[torn vessel]

The geohash should have minimal impact on your DB storage. It's a single short string (or a number) for each location.
Also the DB storage is "fixed" (bounded by how much you store), but the DB bandwidth consumed will continue growing as you exercise your app.

[small lake]

Hi. Any updates on the geohash article?