Search code examples
mysqldistancelatitude-longitude

returning the closest locations from a database with multiple locations in database


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 ?


Solution

  • 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  
    
                                                   |
    

    View on DB Fiddle