As far as design patterns, the Yelp question is pretty standard stuff.
For a more complex answer, you will probably need the geospatial distance. Here is a fascinating powerpoint about that topic (and here is a pdf version of that as well). However, the math involved is quite ugly.
From their slide:
set @orig_lat=122.4058; set @orig_lon=37.7907;
set @dist=10;
SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2), 2) + COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) * POWER(SIN((@orig_lon – dest.lon) * pi()/180 / 2), 2) )) as distance
FROM hotels dest
having distance < @dist
ORDER BY distance limit 10
There's a longer, more in-depth answer about geospatial distance on Stack Overflow.
But you still want to limit the results by latitude and longitude.
Ultimately, I would avoid the POINT datatype and go with latitude/longitude. There's currently no way to determine the distance between two POINTs, so you're going to have to store latitude/longitude for that calculation anyways.
One last link: you may also want to check out this SO thread regarding speeding up the queries using spatial indexes.
Running a DML statement inside a loop is never a good idea. You are multiplying the amount of work to be done. Relational databases are best when operating on sets, when you do a loop you are operating on a single row at a time.
You can achieve the same by doing the update in a single statement:
UPDATE list_of_location
SET location = ST_MakePoint(longitude,latitude)::geography;
I'm not 100% about the syntax for calculating the actual point, as I don't use geometric stuff, but I guess you'll get the idea.
Best Answer
MySQL 8.0+
MySQL supports
Point(x,y)
which is a GIS function that constructs a point. With MySQL 8.0 and newer, you can further assign an SRID to that point withST_SRID(srid)
This is a relatively new feature implemented in MySQL 8.0 with #WL8543. MariaDB does not support it.
In PostGIS, you would use,
In MariaDB, your only option is,