SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 09:18 PM
| ||||
| ||||
 Stored Procedure for list_zip_by_miles 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 | +---------+----------------+----------+----------------+ */      |
|
02-27-2008, 09:19 PM
| ||||
| ||||
| Re: Stored Procedure for list_zip_by_miles Scott Hamm wrote: > 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 | > +---------+----------------+----------+----------------+ > */ > Scott, You're looking at a lot of processing here, especially if you have all 40K+ zipcodes in your database (even more if you have the zip+4). You will be figuring sine and cosines for every row in your database - and that will take a lot of time. I implemented something similar, but I used PHP (because SP's weren't available at the time). Basically I took the longitude and latitude of the target zip code and calculated the longitude X (20 in this case) miles east and west of the target, then latitude X miles north and south. This gave me a square. I then searched for any zipcodes with long/lat within this square, and calculated the distance from the target. Those < X miles from the target (i.e. within a circle bounded by the square) were the ones I wanted. The whole thing was only a few lines of PHP code and quite fast. Compared to the old way (like you're trying to do) which took several seconds to search the entire database. I could probably change it into a SP, but I never bothered. I just have it as a PHP function I include and it returns an array of the appropriate rows. The only thing I need to change is the table/column names. One of these days I'll get around to putting it into a class so I don't have to do anything to it. Just hasn't been worth the effort yet :-) BTW - please don't duplicate your post. This one was already on here. I suspect the reason you didn't get an answer is not many people are using SP's yet. I admit I don't much, although I did a lot of it several years ago in DB2. If you want to call attention to your post, just reply to it with something like "Bump" (to "bump" it up to people's attention) or "Does anyone have any ideas on this one?" Starting a new thread can fragment responses and cause confusion. Thanks. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
Linear Mode
