MySQL Search An Item and all objects in distance

Question

I have a query that searches for the all rows in some distance according to geo coordinates:

SELECT LOCATIONS.*, 
       ( ( ACOS(
                  SIN(" . $location->Lat . " * PI() / 180)
                  * SIN(LAT * PI() / 180) 
                  +
                  COS(" . $location->Lat . " * PI() / 180)
                  * COS(LAT * PI() / 180) 
                  * COS( ( " . $location->Lng . " - LNG ) * PI() / 180)
               )
               * 180 / PI()
         )
         * 60 * 1.1515
       ) AS DISTANCE 
FROM   LOCATIONS 
HAVING DISTANCE <= 100 
ORDER  BY DISTANCE ASC

I'm wondering if it's possible to search for the first record of the table and then treat it as a base - use its lat and lng coordinates?

I was trying achieving this with subquery - but with no luck.

Can anybody give me a suggestion?

http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula — Elzo Valugi, Feb 01 '12 at 16:12
you want to calculate distances for all entries relative to a specific entry? — Elzo Valugi, Feb 01 '12 at 16:14

score 1 · Accepted Answer · answered Feb 01 '12 at 16:10

1

Put the "base" record by itself into a temporary table using CREATE TEMPORARY TABLE AS SELECT ... LIMIT 1. Then join against the temporary table from the query above.

See http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html

answered Feb 01 '12 at 16:10

schtever

3,210
16
25

Thank you for the answer. I didn't even thought about temporary table - that makes sense. – DaveW Feb 01 '12 at 16:26

MySQL Search An Item and all objects in distance

1 Answers1