Multiple Tables
Moderator: General Moderators
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
Multiple Tables
Maybe someone can help. I know a bit about PHP and MySQL, but seem to have undertaken a project that might go beyond my knowledge. I currently have a few tables within a database that I need to cross reference against in order to get the result set that I need. Currently I have a zip code table so I can do a radius limit, a dealer lot table with all of the dealer's information and a vehicle table with all of the inventory for each of the dealers in the dealer table. Basically, what I need to be able to do is provide the script with a given zip code and radius. The script must then find the coordinates of that zipcode, do mathematical calculations to determine all of the zipcodes within the radius of the given zipcode, find all of the dealers within matching zipcodes and then display each of the vehicles that are on their lot. I also need the user to be able to sort the results by year, make, model, color, price and distance. Its your basic vehicle search as you would find on Automotive.Com or Cars.Com, but we need to build something from the ground up around a data feed we're receiving. I currently have everything written out in multiple SELECT requests, but after searching the entire US worth of zip codes, 8,000+ dealers and 600,000+ vehicles ... the search tends to take a bit longer than it should. Any help would be greatly appreciated!
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
OK as stated I use postgres and have done something similar in the past (but not using US data).
My method of solving this problem was a script which took lat/long and calculated the distance x km,y km from a known point, Great Circle and storing this as a 'point' type within the database table. This was only performed once.
I could then use the postgres radius and distance functions within postgres to get the radius/distance without having to calculate the great circle distance again. This sped the query up considerably. The actual joins between the tables as shown should be straight forward.
Remember distances on a map are not simply pythagarus (or however you spell it).
Don't know about mysql geographic functions...
Could you also provide an example of your current query...
My method of solving this problem was a script which took lat/long and calculated the distance x km,y km from a known point, Great Circle and storing this as a 'point' type within the database table. This was only performed once.
I could then use the postgres radius and distance functions within postgres to get the radius/distance without having to calculate the great circle distance again. This sped the query up considerably. The actual joins between the tables as shown should be straight forward.
Remember distances on a map are not simply pythagarus (or however you spell it).
Don't know about mysql geographic functions...
Could you also provide an example of your current query...
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
feyd | Please use
I can figure out the distance thing but never thought about putting it all into a DB and referencing it that way. I guess I am just trying to figure out how to make MySQL do all of the work so it saves some time in returning the results.
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
This is the old coding that I was using to figure out everything ...Code: Select all
## COORDINATES OF REQUESTED ZIP
$coordinates_query = "SELECT DISTINCT latitude, longitude FROM zipcodes WHERE zip_code = '". $searchzip ."'";
$coordinates_query = mysql_query($coordinates_query);
$coordinates = mysql_fetch_array($coordinates_query);
$latitude = $coordinates['latitude'];
$longitude = $coordinates['longitude'];
## GET PREMIUM DEALERS
$pdealer_query = "SELECT DISTINCT LOTID FROM premiumdealers WHERE STATUS IN (2, 3)";
$pdealer_query = mysql_query($pdealer_query);
while($pdealer = mysql_fetch_array($pdealer_query)) {
if($i!=0) {$pdealers .= ", ";}
$pdealers .= "'". $pdealer['LOTID'] ."'";
$i++;
}
unset ($i);
## GET DEALER LOT CODES FOR GIVEN AREA
## $radius_query = "SELECT DISTINCT zip_code, city, round(3958*3.1415926*sqrt((latitude-". $latitude .")*(latitude-". $latitude .") + cos(latitude/57.29578)*cos(". $latitude ."/57.29578)*(longitude-". $longitude .")*(longitude-". $longitude ."))/180) AS distance FROM zipcodes HAVING distance <= (". $searchradius ." +1) ORDER BY distance ASC;";
$radius_query = "SELECT DISTINCT zip_code, city, round(69.1 * sqrt(((latitude-". $latitude .") * (latitude-". $latitude .")) + .6 * ((longitude-". $longitude .") * (longitude-". $longitude .")))) AS distance FROM zipcodes HAVING distance <= (". $searchradius ." +1) ORDER BY distance ASC;";
$radius_query = mysql_query($radius_query);
while($radius = mysql_fetch_array($radius_query)) {
$dealer_query = "SELECT DISTINCT CIDLOTD FROM `lotdata-temp` WHERE CZIP = '". $radius['zip_code'] ."'";
$dealer_query = mysql_query($dealer_query);
while($dealer = mysql_fetch_array($dealer_query)) {
array_push($dealer_code, $dealer['CIDLOTD']);
array_push($dealer_miles, $radius['distance']);
if($i!=0) {$dealers .= ", ";}
$dealers .= "'". $dealer['CIDLOTD'] ."'";
$i++;
}
}
global $radius;
unset ($i);feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am