Querying within longitude and latitude in MySQL
Solution 1
You should search for the Haversine formula, but a good start could be:
- Creating a Store Locator with PHP, MySQL & Google Maps - See Section 'Finding Locations with MySQL'
- Geo/Spatial Search with MySQL
Citing from the first url:
Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
SELECT
id,
( 3959
* acos( cos( radians(37) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-122) )
+ sin( radians(37) )
* sin( radians( lat ) )
)
)
AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
Solution 2
I would highly recommend using either SpatiaLite or PostGIS for this kind of operation. They have built in the kind of functions you are trying to hand code. They also have proper support for spatial data which doesn't really exist in MySQL.
Not exactly a solution in MySQL but a better solution if you want to keep doing spatial requests in the future.
Related videos on Youtube
Genadinik
Thank you to everyone who has helped me. I am extremely appreciative of the amazing people on StackOverflow who have helped me :) I currently run https://www.problemio.com under which I offer mobile apps, books, coaching, and online courses, and even a B2B course licensing business: https://www.problemio.com/udemy/white-labeling-or-buying-udemy-courses.html I also run a funny t-shirt store: https://www.waveifyoulike.com
Updated on July 09, 2022Comments
-
Genadinik almost 2 years
Before asking for specific code examples, I just wanted to ask whether it is possible to make a query something like this pseudo code:
select items from table where lat/lon = -within x miles of a certain lat/lon point-
Is that doable? Or do I have to jump through some hoops? Any good approaches that could be recommended would be great!
-
Genadinik over 13 yearsThank you for your suggestion. I just posted a new question here: stackoverflow.com/questions/4697624/…
-
David Yell about 10 yearsThis doesn't return any calculated distance for me, just
NULL
the whole time. -
Maykonn about 8 yearsNot returning results on exact point. These results returning distance as NULL that not works on having clause.
-
JarsOfJam-Scheduler about 3 years37 = lng and -12=lat, as proposed by conventional essays?:)
-
JarsOfJam-Scheduler about 3 years@DavidYell didn't understand why it returns
NULL
in your case. I've used it three years ago and today, and it actually perfetly works. -
JarsOfJam-Scheduler about 3 years@Maykonn didn't understand why it returns
NULL
in your case. I've used it three years ago and today, and it actually perfetly works.