Hello I am trying to return the closest locations in radius of 100 meters. The input in the formula should return the closest locations near 100 meters but instead returns the same distance.
CREATE TABLE locations (lat decimal, lng decimal);
INSERT INTO locations (lat, lng) VALUES (47.0642512, 15.4696701),(47.0642630, 15.4696744) ;
WITH dt AS
(SELECT (
((acos(sin((47.0722487 * pi() / 180)) * sin((lat * pi() / 180)) + cos((47.0722487 * pi() / 180)) * cos((lat * pi() / 180)) * cos(((15.4477908 - lng) * pi() / 180)))) * 180 / pi()) * 60 * 1.1515 * 1.609344
) AS distance
FROM locations)
SELECT * FROM dt WHERE distance <= 100;
https://www.db-fiddle.com/f/3SpLAoG55wrEdnCisvYbrK/3
Is where I run it
Could someone help me out ?
Use st_distance_sphere that is quite simple,
the other dinstance are to great for fit your limit for 100 meters.
Schema (MySQL v8.0)
CREATE TABLE locations (lat double precision, lng double precision);
INSERT INTO locations (lat, lng) VALUES (49.227747, -122.994726)
,(47.0642512, 15.4696701)
,(47.0642630, 15.4696744);
Query #1
SELECT
*
FROM
locations
WHERE st_distance_sphere(POINT(-122.995842,49.227439 ), POINT( lng ,lat )) <= 100;
| lat | lng |
| --------- | ----------- |
| 49.227747 | -122.994726 |
Query #2
SELECT
st_distance_sphere(POINT(-122.995842,49.227439 ), POINT( lng ,lat ))
FROM
locations ;
| st_distance_sphere(POINT(-122.995842,49.227439 ), POINT( lng ,lat )) |
| --------------------------------------------------------------------- |
| 87.97942831882659 |
| 8584909.968344273 |
| 8584908.937326042
|