I have a table where i have added 2 nearby locations which i calculated from google map.

Now my query is to get that nearby location with this query

SELECT ppl.Title,

     FLOOR((6971 * 2 * ASIN(SQRT(POWER(SIN((30.027055299999997 - abs(ppl.Latitude)) *
     pi()/180 / 2), 2)   COS(37 * pi()/180 ) * COS(abs(ppl.Latitude) * pi()/180) *
     POWER(SIN((-95.4558895 - ppl.Longitude) * pi()/180 / 2), 2) )))*1000)
     as distance 
     
     from `mobile_ppl` as ppl  where  ppl.Longitude=-95.4558895 AND ppl.Latitude=30.027055299999997 AND ppl.user_id!=4 GROUP BY ppl.user_id HAVING distance <500 

second code

SELECT 
    ppl.Title,
    ST_DISTANCE_SPHERE(POINT(- 95.4558101, 30.026540100000002),
            POINT(ppl.Longitude, ppl.Latitude)) / 1000 AS distance
FROM
    `mobile_ppl` AS ppl
WHERE
    ppl.Longitude = - 95.4558895
        AND ppl.Latitude = 30.027055299999997
        AND ppl.user_id != 4
GROUP BY ppl.user_id
HAVING distance < 500

These are the coordinates

Location 1

Latitude: 30.027055299999997

Longitude: -95.4558895

Location 2

Latitude: 30.026540100000002

Longitude:-95.4558101

After trying for several hours i still can't figure out why this is not working. Please help me out.

Check Fiddle : Fiddle SQL

CodePudding user response：

Using the modern version of distance, you get an result.

But i don't know why you are grouping it

so with your data

Your problem is basically only the decimal column as it cuts out all digits after the 9 th and so the comparison will fail always,

You have two possibilies

Save the data as varchar or double like in the example. or use spatial datatype

where xyou save the point

 CREATE TABLE IF NOT EXISTS `mobile_ppl` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_Id` varchar(8) NOT NULL,
   `Title` varchar(28) NOT NULL,
   `latitude` varchar(24) NOT NULL,
   `longitude` varchar(24) NOT NULL,
   PRIMARY KEY (`id`)
 ) AUTO_INCREMENT=1;

INSERT INTO mobile_ppl (user_Id,Title,longitude,latitude) VALUES ('10','Temp Location 1', '-95.4558895','30.027055299999997');
INSERT INTO mobile_ppl (user_Id,Title,longitude,latitude) VALUES ('4','Temp Location 2' ,'-95.4558101','30.026540100000002');
INSERT INTO mobile_ppl (user_Id,Title,longitude,latitude) VALUES ('3','Temp Location 3' ,'57.10100','-2.11060');
INSERT INTO mobile_ppl (user_Id,Title,longitude,latitude) VALUES ('7','Temp Location 4' ,'57.10801','-2.23776');

SELECT 
    ppl.Title,
    ST_DISTANCE_SPHERE(POINT(- 95.4558101, 30.026540100000002),
            POINT(ppl.Longitude, ppl.Latitude)) / 1000 AS distance
FROM
    `mobile_ppl` AS ppl
#WHERE
#    ppl.Longitude = - 95.4558895
#        AND 
#ppl.Latitude = 30.027055299999997
#        AND ppl.user_id != 4

#HAVING distance < 500

Title           |            distance
:-------------- | ------------------:
Temp Location 1 | 0.05779522176337853
Temp Location 2 |                   0
Temp Location 3 |  15771.686509015213
Temp Location 4 |  15782.978003441733

SELECT 
    ppl.Title,
    ST_DISTANCE_SPHERE(POINT(- 95.4558101, 30.026540100000002),
            POINT(ppl.Longitude, ppl.Latitude)) / 1000 AS distance
FROM
    `mobile_ppl` AS ppl
WHERE
    ppl.Longitude = - 95.4558895
        AND 
ppl.Latitude = 30.027055299999997
        AND ppl.user_id != 4

HAVING distance < 500

Title           |            distance
:-------------- | ------------------:
Temp Location 1 | 0.05779522176337853

db<>fiddle here

CREATE TABLE mobile_ppl (user_id int,title varchar(100) , Latitude varchar(100), Longitude varchar(100) )

INSERT INTO mobile_ppl VALUES (
2,'Location 1',

'30.027055299999997',

 '-95.4558895'),

(2, 'Location 2',

'30.026540100000002',

'-95.4558101')

SELECT 
    ppl.Title,
    ST_DISTANCE_SPHERE(POINT(- 95.4558101, 30.026540100000002),
            POINT(ppl.Longitude, ppl.Latitude)) / 1000 AS distance
FROM
    `mobile_ppl` AS ppl
WHERE
    ppl.Longitude = - 95.4558895
        AND ppl.Latitude = 30.027055299999997
        AND ppl.user_id != 4

HAVING distance < 500

Title      |            distance
:--------- | ------------------:
Location 1 | 0.05779522176337853

db<>fiddle here