Solved

Calculations in a query

Posted on 2006-11-12
8
289 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…

726 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