FleetAdmiralJakob 🗕 🗗 🗙•10mo ago

Find most similar value in Convex

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?

14 Replies

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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

Michal Srb•10mo ago

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).

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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

Pastebin

export const findNearestCityByCoord = mutation({ args: { coord: v....

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

PlanetScale

YouTube

Faster geospatial queries in MySQL

Searching a table in MySQL based on latitude and longitude columns. —————————————————— 📚 Learn more about PlanetScale • Website: https://planetscale.com/ • Docs: https://docs.planetscale.com/ —————————————————— 00:00 A point of interest 00:21 Haversine formula 01:00 ST_DISTANCE_SPHERE distance calculation 03:25 Filtering by ST_DISTANCE_SPHERE...

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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 btw. my table looks like that:

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"]),
});

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

Michal Srb•10mo ago

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.

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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();

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();

Michal Srb•10mo ago

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

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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).

Michal Srb•10mo ago

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.

FleetAdmiralJakob 🗕 🗗 🗙OP•10mo ago

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

Michal Srb•10mo ago

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.

FleetAdmiralJakob 🗕 🗗 🗙OP•9mo ago

Hi. Any updates on the geohash article?

Coffee11•7mo ago

Any updates?

Find most similar value in Convex

Did you find this page helpful?