mysql zipcodes

[Guest]

How can i select rows from the database that have certian zip codes but group them and ordey them by zip codes but i want to be able to say 32824 then 34731 then 32827 I dont want them in ascending or decending order how can i acheieve this?

[JBS103]

Well you need to help us out and justify and explain what exactly the order is. From there we can start giving you some ideas.

[Guest]

An example order is 32824, 34731, 32827 but they will vary.

[AndyB]

Unless you can define rules about the order in which you want the information displayed, it's virtually impossible for anyone to suggest a solution.  There's no obvious rationale to the order you give as an example.

[printf]

You can't! Because there is no relationship to natural order by what you wanting to do!

 

// no relationship

 

32824,32827 = Orlando

34731 = Fruitland Park

 

printf

[bluebyyou]

Maybe have some other column in your table associated with each zip. 

  ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

  |    'zip'    |  'order'  |

  |  32824  |    a      |

  |  34731  |    b      |

  |  32827  |    c      |

  ''''''''''''''''''''''''''''''''''''''''''''''''

 

 

[Guest]

I am trying to get zip codes and distaces from one zip out of a database the pull out results for those zips but i want the results in the order of distance away how can i do that?

[bluebyyou]

do you already have code that gives you the results that arent in order?

[Guest]

no i dont i just have the code to get the zip codes in a array. The array is not in order.

[JBS103]

Well you should focus on writing a function that is going to find the distances between your zip codes first. Then you will have to sort the distances and display the zip code that is associated with each distance respectively.

[Guest]

I have the code to find the distances in between but I want to show the results on one page for all zips.  If I were to say  SELECT * FROM list WHERE ZIP=32824 OR ZIP=34731 OR ZIP=32827  Would it show them in order of 32824, 34731, 32827?

[Guest]

I have the code to find the distances in between but I want to show the results on one page for all zips.  If I were to say  SELECT * FROM list WHERE ZIP=32824 OR ZIP=34731 OR ZIP=32827  Would it show them in order of 32824, 34731, 32827?

[bluebyyou]

If we can see the code you have so far, maybe we could make a suggestion on how to get what you want?

[Guest]

$zips = $z->get_zips_in_range('32824', 50);

if (empty($zips)) echo 'Error: '.$z->last_error;
else {
   foreach ($zips as $key => $value) {
      echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>32824</b>.<br>";
   }   
   echo "<br><i>get_zips_in_range() executed in <b>".$z->last_time."</b> seconds.</i><br>";
}

 

instead of printing them all i want to search the database for locations in those zip codes but have it in distance order.

[printf]

First get the center zip code latitude and longitude and city and state, I'll use zip code (32824)

 

SELECT city, state, zipcode, front_door_lat, front_door_long FROM zip_code_table WHERE zipcode = '32824';

 

 

That would return...

 

$row['city'] => Orlando
$row['state'] => FL
$row['zipcode'] => 32824
$row['front_door_lat'] => +28.410872
$row['front_door_long'] => -81.358980

 

Now base your radius, on the center zip code

 

<?php

// the radius in miles

$miles = 10;

// the center zip information

$center_lon = $row['front_door_long'];

$center_lat = $row['front_door_lat'];

// don't include the center zip code

$dont_include = $row['zipcode'];

// the degrees

$degrees = ( $miles / 69.172 );

// we add 1, because 10 mile radius is anything
// below 11 miles, not 10 miles. if we didn't
// do this we would only return up to 9.99 miles

$radius = ( $miles + 1 );

// max results to return, paging!

$limit = 10;

// the query

$sql = "SELECT *, (degrees(acos(" . sin ( deg2rad ( $center_lat ) ) . " * sin(radians(front_door_lat)) + " . cos ( deg2rad ( $center_lat ) ) . " * cos(radians(front_door_lat)) * cos(radians(" . $center_lon . " - front_door_long)))) * 69.172) AS miles FROM  zip_code_table WHERE front_door_long >= " . ( $center_lon - $degrees ) . " AND front_door_lat >= " . ( $center_lat - $degrees ) . " AND front_door_long <= " . ( $center_lon + $degrees ) . " AND front_door_lat <= " . ( $center_lat + $degrees ) . " AND (((degrees(acos(" . sin ( deg2rad ( $center_lat ) ) . " * sin(radians(front_door_lat)) + " . cos ( deg2rad ( $center_lat ) ) . " * cos(radians(front_door_lat)) * cos(radians(" . $center_lon . " - front_door_long)))) * 69.172) / 6. + (degrees(acos(" . sin ( deg2rad ( $center_lat ) ) . " * sin(radians(front_door_lat)) + " . cos ( deg2rad ( $center_lat ) ) . " * cos(radians(front_door_lat)) * cos(radians(" . $center_lon . " - front_door_long)))) * 69.172)) < " . $radius . " AND zipcode != '" . $dont_include . "' ORDER BY miles ASC LIMIT " . $limit;

?>

 

The query would return all the table data + one column called miles, which you use to display the miles in a format you want, like...

 

echo sprintf ( '%0.2f', $miles );

 

Where as... (within the SQL query(s))

 

front_door_lat = would be changed to the name of the column in your zipcode table that represents the latitude

front_door_long = would be changed to the name of the column in your zipcode table that represents the longitude

zipcode = would be changed to the name of the column that holds the zipcode

zip_code_table = would be changed to the name of your zipcode table name!

 

 

This does the calculation in the query, so you don't have to use wasteful PHP code to figure the real miles.

 

 

You can try example of how it works here...

 

http://zip.ya-right.com/distance/radius.php

 

 

printf

[Guest]

SELECT city, state, zipcode, front_door_lat, front_door_long FROM zip_code_table WHERE zipcode = '32824';


$row['city'] => Orlando
$row['state'] => FL
$row['zipcode'] => 32824
$row['front_door_lat'] => +28.410872
$row['front_door_long'] => -81.358980


/ the radius in miles

$miles = 10;

// the center zip information

$center_lon = $row['front_door_long'];

$center_lat = $row['front_door_lat'];

// don't include the center zip code

$dont_include = $row['zipcode'];

// the degrees

$degrees = ( $miles / 69.172 );

// we add 1, because 10 mile radius is anything
// below 11 miles, not 10 miles. if we didn't
// do this we would only return up to 9.99 miles

$radius = ( $miles + 1 );

// max results to return, paging!

$limit = 10;

// the query

$sql = "SELECT DISTINCT *, (degrees(acos(" . sin ( deg2rad ( $center_lat ) ) . " * sin(radians(front_door_lat)) + " . cos ( deg2rad ( $center_lat ) ) . " * cos(radians(front_door_lat)) * cos(radians(" . $center_lon . " - front_door_long)))) * 69.172) AS miles FROM  zip_code_table WHERE front_door_long >= " . ( $center_lon - $degrees ) . " AND front_door_lat >= " . ( $center_lat - $degrees ) . " AND front_door_long <= " . ( $center_lon + $degrees ) . " AND front_door_lat <= " . ( $center_lat + $degrees ) . " AND (((degrees(acos(" . sin ( deg2rad ( $center_lat ) ) . " * sin(radians(front_door_lat)) + " . cos ( deg2rad ( $center_lat ) ) . " * cos(radians(front_door_lat)) * cos(radians(" . $center_lon . " - front_door_long)))) * 69.172) / 6. + (degrees(acos(" . sin ( deg2rad ( $center_lat ) ) . " * sin(radians(front_door_lat)) + " . cos ( deg2rad ( $center_lat ) ) . " * cos(radians(front_door_lat)) * cos(radians(" . $center_lon . " - front_door_long)))) * 69.172)) < " . $radius . " AND zipcode != '" . $dont_include . "' ORDER BY miles ASC";


$query = "SELECT * FROM Houses WHERE Zip IN ($query)";

 

I want to pull out the houses information for all the houses in those zip codes I was trying to like this but thats not working at all. Like if we start from 32824 I want it to get all the houses for 32824 then houses for the closest zip code.

[printf]

If you want, make dump of your database (including the houses data and the zip code data) and PM me a link to download it, and I will make you an example based on your data. Because as it is, I have no way of knowing what you exactly doing with the code I gave you. That's the best way I can help you solve you problem.

 

printf

[camdagr81]

My best guess is to start with a point of origin. Say New York City and then each zipcode from there out to Los Angelos has a distance from the orgin point associated to it. Then create a calculation that will give you the relation of a specific place based on the city you're trying to find out where some other city is farthest from.

For example: if we make New York City our origin point and set it's value at 0 and want to know how far from Columbus, OH is from Atlanta, GA you would do the following equation (Pythagorean Theorem)

 

a = NYC to COL ^ 2 (squared)
b = ATL to COL ^ 2 
c = NYC to ATL ^ 2

solving for b in the equation...
a + b = c
b = c - a

then take the square root of b

* Note this only works when you plot the 3 points as a right triangle