Solved

Calculations in a query

Posted on 2006-11-12
8
287 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 142

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

773 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