0

I have an app/site and need to perform a search for all users located within a user specified distance of their zip code. I have two tables, one with user data and a second with zip code information. I'm having difficulties figure out the query to find users that are within the distance. I don't understand geospatial query needed to make it work.

User Table Structure

username        zip 
=====================   
tester55        60608   
Tester1         00544   
iosuser1        73120   
Tester3         01002

Zipcodes Table Structure

zip     latitude     longitude
------------------------------
00501   40.8154      -73.0451
00544   40.8154      -73.0451
01001   42.0702      -72.6227
01002   42.3671      -72.4646

I want to use the zip code of a user to find other users within a certain radius. Here are the queries I have so far, I'm trying to figure out how to make it one query.

First Query

    Select users.zip, users.username, zipcodes.zip as user zip, 
zipcodes.longitude as userlong, zipcodes.latitude as userlat
from users, zipcodes where users.zip = zipcodes.zip AND username = 'tester55

Second Query

SELECT zipcodes.zip, zipcodes.city, zipcodes.state, zipcodes.longitude, zipcodes.latitude, ( 3959 * acos( cos( radians(userlat) ) * cos( radians( zipcodes.latitude ) ) 
    * cos( radians( zipcodes.longitude ) - radians(userlong) ) + sin( radians(userlat) ) * sin(radians(zipcodes.latitude)) ) ) AS distance 
FROM zipcodes
HAVING distance < 50
ORDER BY distance

Any help is greatly appreciated.

techgirl
  • 293
  • 1
  • 3
  • 18
  • 1
    Doing it this way is bound to be super messy. Using a database with good GIS support is better, or at the absolute least, use [MySQL Spatial Extensions](https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html). – tadman Sep 03 '17 at 23:29
  • This is not a duplicate. @Shadow can you remove the duplicate label so I can get responses. – techgirl Sep 04 '17 at 13:42
  • I obviously think it is a duplicate, otherwise I would not have chosen to close it down as such. If you do not think it is a duplicate, then pls elaborate why not. Your question is about joining the same tables multiple times in a single query. This is exactly what is described in the duplicate topic. – Shadow Sep 04 '17 at 13:49
  • I've edited the question to show that the focus is on not on joining but the syntax required to find users within a radius. That's the part I'm in need of help with and have been searching for days. If any question is a duplicate it is [link]https://stackoverflow.com/questions/3983325/calculate-distance-between-zip-codes-and-users?rq=1 . I've reviewed that question and still didn't get the answer needed. – techgirl Sep 04 '17 at 13:56

0 Answers0