asked on

Calculations in a query

I need to write an SQL query that will calculate the distance between to sets of latitude and longitudinal points, and the sorts then by distance.

The point of this is to search for businesses by proximity to a customer's zip code. I have a list of the lat and long values of each zip code the US, and plan to use a LEFT JOIN to join that to the list of companies so that a user can search just by using their zip code.

Here is the equation I will be using:

d = arc(sin(p1.lat)*sin(p2.lat) + cos(p1.lat)* cos(p2.lat)* cos(p2.lon-p1.lon)) * R; where R = 6371, and represents the radius of the earth in kilometers.

Guy Hengel [angelIII / a3]

as you can see, the functions COS and SIN are present, not sure which one you mean by arc()...
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

so what is the problem actually?

Raynard7

Angel is correct about how to use cos and sin,

http://ben.milleare.com/2006/09/03/calculating-distance-with-latitude-and-longitude/

gives the query that I use to calculate distance using mysql queries

DrDamnit

ASKER

arc is arc cossin.

Raynard7

arc correspontds with: acos()

you can also have arc sine which is: asine()

ASKER CERTIFIED SOLUTION

Isisagate

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrDamnit

ASKER

Isisagate:

Can you alter that query to reflect the following data:

The list of businesses in teh databases is in biztable
The master list of zipcodes is in zipcity
The user will supply the zipcode as a single zipcode.

I would like the results ordred by the resulting distance from the zip code entered, and sorted in ASC order.

DrDamnit

ASKER

OK, I got the query altered, just one quick question: does this give the distance in miles?

Raynard7

yes