I'm trying to create stored procedure, but after reading mysql's online
document, I was not able to comprehend its usage. Here is what I do, put in
target zip code and miles range, then find a list of zipcode, city, state
and miles from target zip code. How do I get around to it?
[code]
SET @targetzip='19943'; /*Establish the starting zip code.(Domain)*/
SET @rangemiles='20'; /*Specify the miles range from that starting
zip code.(Range)*/
SELECT
C.ZipCode,
Zi.City,
Zi.State,
Round(C.Miles) AS 'Mile(s)'
FROM
(
SELECT
Lat_A,
Long_A,
Lat_B,
Long_B,
ZipCode,
(degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) +
cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A -
long_B)))))) * 69.09 AS 'Miles'
FROM
(
SELECT
CAST(latitude AS decimal(8,6)) AS Lat_A,
CAST(longitude AS decimal(8,6)) AS Long_A
FROM
zips
WHERE
`zip [EMAIL PROTECTED]
) AS A,
(
SELECT
CAST(latitude AS decimal(8,6)) AS Lat_B,
CAST(longitude AS decimal(8,6)) AS Long_B,
`zip code` AS ZipCode
FROM
zips
) AS B
WHERE
(degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) +
cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A -
long_B)))))) * 69.09 <= @rangemiles
) AS C,
zips Zi
WHERE
Zi.`zip code`=C.ZipCode
ORDER BY
Round(C.Miles);
/*
RESULT
+---------+----------------+----------+----------------+
| ZipCode | City | State | Round(C.Miles) |
+---------+----------------+----------+----------------+
| 19943 | Felton | Delaware | 0 |
| 19979 | Viola | Delaware | 3 |
| 19980 | Woodside | Delaware | 4 |
| 19934 | Camden Wyoming | Delaware | 6 |
| 19962 | Magnolia | Delaware | 6 |
| 19946 | Frederica | Delaware | 7 |
| 19952 | Harrington | Delaware | 7 |
| 19954 | Houston | Delaware | 8 |
| 19964 | Marydel | Delaware | 10 |
| 19942 | Farmington | Delaware | 10 |
| 19963 | Milford | Delaware | 11 |
| 19901 | Dover | Delaware | 11 |
| 19902 | Dover Afb | Delaware | 11 |
| 19904 | Dover | Delaware | 11 |
| 19953 | Hartly | Delaware | 11 |
| 21636 | Goldsboro | Maryland | 11 |
| 21640 | Henderson | Maryland | 12 |
| 21649 | Marydel | Maryland | 12 |
| 21639 | Greensboro | Maryland | 12 |
| 19950 | Greenwood | Delaware | 13 |
| 19955 | Kenton | Delaware | 15 |
| 19960 | Lincoln | Delaware | 15 |
| 21644 | Ingleside | Maryland | 16 |
| 21660 | Ridgely | Maryland | 16 |
| 21629 | Denton | Maryland | 16 |
| 21607 | Barclay | Maryland | 17 |
| 19941 | Ellendale | Delaware | 17 |
| 19938 | Clayton | Delaware | 18 |
| 19933 | Bridgeville | Delaware | 19 |
| 21668 | Sudlersville | Maryland | 19 |
| 21641 | Hillsboro | Maryland | 20 |
| 19977 | Smyrna | Delaware | 20 |
+---------+----------------+----------+----------------+
*/
[/code]
