Solved

Calculations in a query

Posted on 2006-11-12
8
290 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:DrDamnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17926389
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?
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17926409
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
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 17926443
arc is arc cossin.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 35

Expert Comment

by:Raynard7
ID: 17926499
arc correspontds with: acos()

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

Accepted Solution

by:
Isisagate earned 500 total points
ID: 17931190
here is the query I use..... Maybe it will help you out....


SELECT
b.zip, b.state,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2)
)))) AS distance
FROM zips a, zips b, zips c
WHERE
a.zip = '[zip]' AND
a.zip = c.zip AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2)
)))) <= [miles]
GROUP BY distance
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 17983694
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.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 17983834
OK, I got the query altered, just one quick question: does this give the distance in miles?
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17983852
yes
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question