Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calculations in a query

Posted on 2006-11-12
8
Medium Priority
?
295 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
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

916 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