2

I have a table with zipcode(int) and Location(point). I'm looking for a MySql query or function. Here is an example of the date. I'd like to return 100 miles.

37922|POINT(35.85802 -84.11938)

Is there an easy query to achieve this?

Okay so I have this 

select x(Location), Y(Location) FROM zipcodes

This will give me my two points, but how do i figure out whats within a distance of x/y?

Simon East
  • 55,742
  • 17
  • 139
  • 133
  • 1
    What is wrong with using a function? – alex Jun 15 '11 at 00:16
  • Unless you can show me a function that uses Point instead of decimal to defer the lat/lon I really don't want to. –  Jun 15 '11 at 00:17

1 Answers1

1

The query to do this is not too hard, but is slow. You would want to use the Haversine formula.

http://en.wikipedia.org/wiki/Haversine_formula

Converting that to SQL should not be too difficult, but calculating the distance for every record in a table gets costly as the data set increases.

The work can be significantly reduced by using a geohash function to limit the locus of candidate records. If accuracy is important, the Haversine formula can be applied to the records inside a geohash region.

If the mysql people never completed their GIS and Spatial extension, consider using ElasticSearch or MongoDB.

There is a pretty complete discussion here: Formulas to Calculate Geo Proximity

Community
  • 1
  • 1
Chris
  • 1,713
  • 2
  • 12
  • 16
  • thank you, but all of that is greek to me, i would really need to see an example on this one. –  Jun 15 '11 at 00:44
  • Google 'Haversine mysql'. Here is one: http://www.davidus.sk/web/main/index/article_id/8 – Chris Jun 15 '11 at 00:54