1

What's Needed?

I want to get all the restaurants that are near the user's current location (10KM in range).

Description

I have a users table user(Id, Name, Address), here the address is a lat, long string. e.g. 20,21.

2nd table is restaurant(Id, Name, Address), and the format for address is the same as above.

On the front-end I get the current location of the user, and send to the API, and in the API I have a function calling below query from MySQL:

const getAllRestaurants = async (currentAddress: string): Promise<Array<Restaurant>> => {
  // query restaurants (using typeorm which will generate something like this)
  const rests = "SELECT * FROM restaurant LIMIT 0, 10";

  const restsToReturn = [];

  // got the restaurants, now filter on based of address
  for (const rest of rests) {
    // call "isRestaurantInRange" which will return true if the restaurant is user's range
    if (isRestaurantInRange(rest.Address, currentAddress)) {
      restsToReturn.push(rest);
    }
  }

  return restsToReturn;
}

Drawbacks:

  • It's SLOW.
  • The pagination doesn't work as expected, because if 2 out of 10 are in the user's range then we have to manually load up the next data and check the same condition until it reaches 10.
  • It's a DIRTY way to approach it.

What I am expecting:

  • Can isRestaurantInRange saved in database (MySQL)? So that when I request the function runs on the database level and return the expected results, without manually filtering it? I am not a MySQL expert, but stored procedure will NOT work as far as I know. Although, there's a caveat with this approach. i.e. I can't call Google Maps Distance API to have exact distance between two points, so need a solution which have google API working.

  • If you know a TypeORM based solution, it would also be acceptable.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
U-Dev
  • 1,296
  • 5
  • 19
  • You should filter results in mysql and not your client, also dont select all at once. – Code Spirit Jun 05 '21 at 12:21
  • That's the question, HOW TO FILTER ON MYSQL? Like can you run the filter function which I am running in MySQL? – U-Dev Jun 05 '21 at 12:22
  • 1
    https://stackoverflow.com/questions/42799118/mysql-find-points-within-radius-from-database . You can also use mysqls `CREATE FUNCTION` to create callable functions in your database. – Code Spirit Jun 05 '21 at 12:24
  • 1
    But what if I want to call the google's API to have a exact distance between two points? – U-Dev Jun 05 '21 at 12:43
  • Does this answer your question? [Find nearest latitude/longitude with an SQL query](https://stackoverflow.com/questions/2234204/find-nearest-latitude-longitude-with-an-sql-query) – Geoffrey Jun 05 '21 at 13:11
  • NO! Same question above: "What about if I need to use Google API"? – U-Dev Jun 05 '21 at 13:27
  • Well, if it was me I'd start with the sql – Strawberry Jun 05 '21 at 13:31

2 Answers2

2

Well, it was suggested to stick with MySQL implementation, which of course doesn't give accurate result. Anyways, I am using following query to get it:

SELECT ST_Distance(ST_GeomFromText('POINT(31.4630941 74.3215482)', 4326 ),
                   ST_GeomFromText('POINT(31.4780632 74.3125699)', 4326 ),
                  'kilometre') AS distance;

Used this link to verify that the MySQL function gives correct STRAIGHT distance between two points.

This is a great article about spatial data in MySQL. Quoting:

These are two of the most used SRSs. SRID 4326 is GPS coordinates.

lemon
  • 14,875
  • 6
  • 18
  • 38
U-Dev
  • 1,296
  • 5
  • 19
1

If you use a SPATIAL index with InnoDB, you need MySQL 5.7.6 (or newer) to also get SP_Distance_Sphere().

Do store the actual POINTs in the table; that is, don't compute them on the fly since that is likely to prevent the use of the index.

For other tips, see http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

There may be a disadvantage in splitting into 2 tables. Please provide SHOW CREATE TABLE.

Rick James
  • 135,179
  • 13
  • 127
  • 222