4 Replies - 260 Views - Last Post: 20 May 2014 - 08:04 AM Rate Topic: -----

#1 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Radius Search based on ZipCode

Posted 19 May 2014 - 01:18 PM

Hey Guys,

I'm sure this comes up all the time and I've done some searching on it but there are so many methods and theories out there that I'm a little confused at this point.

I have a database of all(most) of the zip codes, latitudes, longitudes and pertinent information. I'm trying to query this database and return the zip code results within a radius of the input zip. I have the information of my input zip and I'm trying to run it but I keep returning an empty result set. This is the formula that I found online that other people have had success with but I can't seem to get it working. Does anyone have any suggestions? Am I way off base here?

As always, thanks in advance!!

Formula 1 - No errors, empty result set
SELECT distinct(zip)
FROM Build00GeoData 
WHERE 
    (
        POWER(latitude - 41.77, 2) 
        + 
        POWER(longitude - 71.35, 2) 
    ) 
    < POWER(10,2)



Formula 2 - No erros, empty result set
/*I'm using php here but for MySQL purposes, I did remove the variables and PHP syntax then replaced with hard numbers*/

SELECT distinct(zip) FROM Build00GeoData  WHERE (3958*3.1415926*sqrt((Latitude-'.$lat.')*(Latitude-'.$lat.') + cos(Latitude/57.29578)*cos('.$lat.'/57.29578)*(Longitude-'.$lon.')*(Longitude-'.$lon.'))/180) <= '.$radius.


This post has been edited by guyfromri: 19 May 2014 - 01:19 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Radius Search based on ZipCode

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9223
  • View blog
  • Posts: 34,630
  • Joined: 12-June 08

Re: Radius Search based on ZipCode

Posted 19 May 2014 - 01:31 PM

Have you thought about using something like this:
https://gist.github....lhejazi/6261123

for getting spacial radii?
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5840
  • View blog
  • Posts: 12,694
  • Joined: 16-October 07

Re: Radius Search based on ZipCode

Posted 20 May 2014 - 03:43 AM

Ask your database to tell you what it actually evaluates that as... it might provide insight.

e.g.
SELECT distinct zip,
   POWER(latitude - 41.77, 2) + POWER(longitude - 71.35, 2)  as d2
FROM Build00GeoData 


Was This Post Helpful? 0
  • +
  • -

#4 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: Radius Search based on ZipCode

Posted 20 May 2014 - 07:56 AM

View Postmodi123_1, on 19 May 2014 - 04:31 PM, said:

Have you thought about using something like this:
https://gist.github....lhejazi/6261123

for getting spacial radii?

Thank you! This seems very complicated though and I'm not sure I would be able to apply my php correctly if I did get this method to work. Is this something you have used in the past? I could very well be over-complicating it but it looks like it might be a little more robust than I actually need for this project.

View Postbaavgai, on 20 May 2014 - 06:43 AM, said:

Ask your database to tell you what it actually evaluates that as... it might provide insight.

e.g.
SELECT distinct zip,
   POWER(latitude - 41.77, 2) + POWER(longitude - 71.35, 2)  as d2
FROM Build00GeoData 


Thanks very much! I hadn't thought to actually view the conditions I was using and see what they were. As suspected, they're a random number that represents the square root of my longitude+latitude but based on my lack of experience with maps these types of calculations, I'm not sure how I could use these values to calculate distance.

Thanks both! These are great starting points for me. :)
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9223
  • View blog
  • Posts: 34,630
  • Joined: 12-June 08

Re: Radius Search based on ZipCode

Posted 20 May 2014 - 08:04 AM

Yes, I use that formula when looking for a collection of items in a given X mile radius from a specific lat/lon.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1