elite311 Posted August 26, 2011 Share Posted August 26, 2011 So I'm trying to make a web app for work that allows our reps to enter the customers address then it search a database and return the closest location in the database to the address they entered. I'm pretty new to SQL so any help or tutorials would be great. With a lot of help from tutorials I have it working right now if you enter the 2 address's here's my code and you can see the working page here http://s262833979.onlinehome.us/Dan/servicecall.html <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"/> <meta name="robots" content="noindex,follow" /> <title>Book A Service Call</title> <script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAh2r2YFiBfUkYnPMfobDlIBSZzGBYWmrklI-wZRptp7_OVA51VBTka31gdS-Bl3rNQ2o2ceKs3gSO9g" type="text/javascript"></script> <!-- According to the Google Maps API Terms of Service you are required display a Google map when using the Google Maps API. see: http://code.google.com/apis/maps/terms.html --> <script type="text/javascript"> var geocoder, location1, location2, gDir; function initialize() { geocoder = new GClientGeocoder(); gDir = new GDirections(); GEvent.addListener(gDir, "load", function() { var drivingDistanceMiles = gDir.getDistance().meters / 1609.344; var drivingDistanceKilometers = gDir.getDistance().meters / 1000; document.getElementById('results').innerHTML = '<strong>Customers Location: </strong>' + location1.address + ' (' + location1.lat + ':' + location1.lon + ')<br /><strong>Closest Superior branch: </strong>' + location2.address + ' (' + location2.lat + ':' + location2.lon + ')<br /><strong>Distance to closest Superior branch: </strong>' + drivingDistanceKilometers + ' kilometers'; }); } function showLocation() { geocoder.getLocations(document.forms[0].address1.value, function (response) { if (!response || response.Status.code != 200) { alert("Sorry, we were unable to geocode the first address"); } else { location1 = {lat: response.Placemark[0].Point.coordinates[1], lon: response.Placemark[0].Point.coordinates[0], address: response.Placemark[0].address}; geocoder.getLocations(document.forms[0].address2.value, function (response) { if (!response || response.Status.code != 200) { alert("Sorry, we were unable to geocode the second address"); } else { location2 = {lat: response.Placemark[0].Point.coordinates[1], lon: response.Placemark[0].Point.coordinates[0], address: response.Placemark[0].address}; gDir.load('from: ' + location1.address + ' to: ' + location2.address); } }); } }); } </script> </head> <body onload="initialize()"> <form action="#" onsubmit="showLocation(); return false;"> <p> <input type="text" name="address1" value="Address 1" /> <input type="text" name="address2" value="Address 2" /> <input type="submit" value="Search" /> </p> </form> <p id="results"></p> </body> </html> I also set up an SQL database and here is what it looks like : Field Type Length/values id INT 3 store_name VARCHAR 20 street_number VARCHAR 60 city VARCHAR 60 province VARCHAR 10 postal_code VARCHAR 10 latitude FLOAT 10.6 longitude FLOAT 10.6 I have read a lot about the query and came up with this but I'm not sure how to incorporate it into my page, I'm also not sure if this is 100% correct $query = "SELECT `store_name`,`street_number`,`city`,`province`,`postal_code`,`latitude`,`longitude`, ( 3959 * acos( cos( radians($user_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($user_long) ) + sin( radians($user_lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM locations ORDER BY distance"; I was really hoping someone could help me figure this out or point me in the right direction. Thanks in advance Quote Link to comment Share on other sites More sharing options...
elite311 Posted August 27, 2011 Author Share Posted August 27, 2011 Anyone? I'm really stumped, I just want to have to enter the customers address and it find the closest branch in the date base and then it display how many kilometers away the branch is from the customer Quote Link to comment Share on other sites More sharing options...
fenway Posted August 28, 2011 Share Posted August 28, 2011 Don't use great circle distance -- it's just unnecessarily slow. Quote Link to comment Share on other sites More sharing options...
elite311 Posted August 31, 2011 Author Share Posted August 31, 2011 I've been trying to get this to work for a week now, I can't figure it out. Does anyone know of a good tutorial? I have read a lot of them on store locators but I just want to modify what I already have as I want to build on it after. I really need some help with this, if someone knows how I can change the code above so it will search the database for the closest location to the one I enter instead of me having to enter a 2nd location. Quote Link to comment Share on other sites More sharing options...
elite311 Posted August 31, 2011 Author Share Posted August 31, 2011 Ok so after a lot of reading I found a tutorial and have a working locator now. I'm defiantly starting to understand this a little better as I've been working through it but still have a ways to go. You can see it here http://s262833979.onlinehome.us/Dan/postcode/search.php The only hurdle now is it's using the Haversine formula to find the closest location, can someone help me figure out how I can make it show the distance as driving distance and it locate the closest location based on driving distance instead of the Haversine formula? I have attached the file Thanks [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted September 1, 2011 Share Posted September 1, 2011 If you have to attach a script, that means it's too big. It's too big because you're trying to post the entire thing. I already told you not to use great circle distances -- and you ignored me. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.