Correct me if I'm wrong.

There are three approaches to get the nearest homes, users have created in my website:

  1. To create a table with two columns(latitude, longitude) that both of them are float and say:

    $latitude = 50; $longitude = 60;

    SELECT * FROM my_table WHERE (latitude <= $latitude+10 AND latitude >= $latitude-10) AND (longitude <= $longitude+10 AND longitude >= $longitude-10)

that 10 here means 1km for example.

In this approach we can also use harvesine formula.

  1. To merge those columns(latitude, longitude) to one column named point as POINT type and again search each row one by one.

  2. To categorize multiple points(the coordinates of homes users have created) as a category for one section of a country i.e. city and if a query comes with $latitude and $longitude to see the nearest homes, I will check in which category they are stored IN ORDER NOT TO search all rows but search only the section this query(coordinate) belongs to.

As I guess approach number 1 is slow because of the conditions for each row of table and again slow if I use harvesine formula.

If I use ST_Distance it seems again it's slow because again it just has lots of calculations.

But if I use approach number 3 it seems it is faster to check each section for an specific point user is than check all rows. I know how to set point for each home however I don't know how to create multiple home positions as a section maybe in another table.

BTW in new versions of MySQL and MariaDB Spatial Indexes are supported in InnoDB.

My questions:

  1. Is approach number 1 really slow or other ST_* functions are the same as this approach to check all rows with those formulas mentioned there one by one? Which one is faster?

  2. Does approach number 2 do something other than simple conditions to make it faster? I mean does it make any changes when using type of POINT instead of float and using ST_* functions instead of doing it myself? I want to know whether the algorithm is different.

  3. If approach number 3 is the fastest in these three approaches, how can I categorize points in order not to search all rows in a table?

  4. How can I use Spatial Indexes to make it as fast as possible?

  5. If any other approaches exist and I didn't mention, could you please tell me how can I get the nearest homes just by having coordinates in MySQL/MariaDB in PHP/Laravel?

  • Welcome to GIS SE! As a new user be sure to take the Tour where you will see that there should be only one question asked per question under our focussed Q&A format.
    – PolyGeo
    Jul 19 '18 at 22:35