CODEWITHSUNDEEP
phpmysqlsql
Henrik
Henrik

Reputation: 21

Searching for records in database within a certain distance calculated by coordinates

I'm having trouble with this query:

SELECT Store_name, GPS_latitude, GPS_longitude, SQRT(
POW(69.1 * (GPS_latitude - $longitude), 2) +
POW(69.1 * ($latitude - GPS_longitude) * COS(GPS_latitude / 57.3), 2)) AS distance
FROM table HAVING distance < 25 ORDER BY distance

The user of the website should be able to input coordinates (latitude and longitude). From there on I want the query above to list the stores in the table ordered by how close they are to the user given $latitude and $longitude.

I do get results, but only if i edit the query from 'distance < 25' to 'distance < 5000' (or other large number), although the results does not seem to be in order based on distance from the user given coordinates.

Is it the query which calculates this the wrong way? I am quite stuck and i can't seem to find any solutions.

Upvotes: 2

Views: 67

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

Try this:

select * from (
    SELECT Store_name, GPS_latitude, GPS_longitude,
    SQRT(
        POW(69.1 * (GPS_latitude - $longitude), 2) +
        POW(69.1 * ($latitude - GPS_longitude) * COS(GPS_latitude / 57.3), 2)
    ) AS distance
    FROM table
) as vtab
WHERE distance < 25
ORDER BY distance;

PS but i would strongly recommend you to take a look at the "MySQL-Specific Spatial Relation Functions"

Upvotes: 1

Related Questions