List:General Discussion« Previous MessageNext Message »
From:Scott Hamm Date:September 20 2006 6:52pm
Subject:Stored Procedure for list_zip_by_miles
View as plain text  
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 code`=@targetzip
        ) 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]

Thread
Stored Procedure for list_zip_by_milesScott Hamm20 Sep