Closest distance

elite311 · July 28, 2011

Hello,

I am fairly new to the world of we programing and I'm hoping someone can help me or at least point me in the right direction. I am working on a project for my company's customer service team, basically I am using google maps to allow the CSR to enter to locations and get the distance between the locations. I'm trying to upgrade this system and automate it some. Right now the CSR must enter both locations, the customers location(city & province) and out location (city & province) what I'm trying to accomplish is only getting the CSR to enter the customers location and then have the system search a sql database of all our branches to find the closest one and then return the result with the driving distance.

Our company books service calls based on a zone system, example:

zone 1: 0 to 50kms = $129

zone 2: 50 to 100kms = $173.50

etc

So I want to automate the search system to find the closest branch and display the driving distance in kms as well as show the price of the service call based on how many kms away it is.

I have done a lot of reading of tutorials and have been trying to get this to work for a while but I'm having all kinds of trouble. My code right now is:

<!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>Address 1: </strong>' + location1.address + ' (' + location1.lat + ':' + location1.lon + ')<br /><strong>Address 2: </strong>' + location2.address + ' (' + location2.lat + ':' + location2.lon + ')<br /><strong>Driving Distance: </strong>' + drivingDistanceMiles + ' miles (or ' + 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 have created an SQL table (with some help from some tutorials) to enter all of the branch loactions and it looks like this:

  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL

The first part works fine and does what I want but I cant figure out how to get it to search an SQL database instead of having to enter the location manually. You can see my working code so far at http://s262833979.onlinehome.us/Dan/servicecall.html

Can anyone help me figure out how to do this? I am using PHP 5 and MySQL 5.0 and Google Maps API

Any help would be greatly appreciated as I'm completely stumped!

ebmigue · July 28, 2011

"Closest" branch to what?

Being "close to something" is a relative relation. Thus, I would assume that you have a "base" location (serving as the reference point.)

You can start by modeling all locations as three-attribute relation (table), say:

name_of_place string,
x_axis decimal,
y_axis decimal

The idea is to represent the possible as points in a Cartesian plane.

Finding the closest location from a given reference point is probably solve-able using techniques in geometry.

And application of geometric techniques can be achieved, IMO, in SQL.

You might also want to look at existing algorithms for "The Traveling Salesman Problem" and/or "Shortest path problem".

Hope it helps.

fenway · July 28, 2011

Assuming you have a reasonable number of branches, you can simply use the Pythagorean theorem to figure out the rough distance to each, and then take the shortest one.

Don't bother with great-circle distance -- it's way too-resource intensive, and no one can drive that path anyway.

Sign In

Closest distance

Recommended Posts

elite311

Link to comment

Share on other sites

ebmigue

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information