Mileage Calculation

[Mike Konoff]

Wondering if Alpha Five has any capability to calculate distance between two addresses, such as what occurs when you "get directions" from Google Maps. I know that Alpha Five can map an address. Would like to set a calculated field to determine the driving distance a location is from the home office. Displaying estimated travel time would be helpful too.

Thanks,
Mike Konoff

[Tom Cone Jr]

Re: Mileage Calculation

I think Google Maps simply sums the distances in each "leg" of the journey. How do you propose that Alpha will be able to intuit which of many different routes the user might take? Or, how "long" each leg might be?

[Mike Konoff]

Re: Mileage Calculation

Good question. No idea. Was being hopeful there might be some nifty built in function that allows Alpha Five to interface with Google Maps to determine a distance between two addresses.

[Mike Wilson]

Re: Mileage Calculation

Yes,
Peter Wayne has a script I believe in learn alpha that I use. I believe I have once posted it on the MB with an example I will look for it and post as soon as I find it or re-post.

[Mike Wilson]

Re: Mileage Calculation

Here you go -

Later - This is an "ass the crow flies" distance. As for travel time.... the parameters to have those known are a bit far from mere calculations. That would be something that depends on roads and routes. Look to Google for that.

'Date Created: 11-Jul-2003 11:40:56 PM
'Last Updated: 18-Jun-2013 11:27:49 AM
'Created By  : Peter Wayne
'Updated By  : j michael wilson

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'ref1="http://www.census.gov/cgi-bin/geo/gisfaq\?Q5.1"
'ref2="http://williams.best.vwh.net/avform.htm"
'ref3="http://www.census.gov/geo/www/tiger/zip1999.html"
'refjmw="http://itouchmap.com/latlong.html"
'refusps="http://webapps.usps.com/zip4/citytown.jsp"

'distance_get_from_lat_lon

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
	function gcirc as p (lat1 as n, lat2 as n, lon1 as n, lon2 as n)
	rlat1=dtor(lat1)
	rlat2=dtor(lat2)
	rlon1=dtor(lon1)
	rlon2=dtor(lon2)
	D=acos(sin(rlat1)*sin(rlat2)+ cos(rlat1)*cos(rlat2)*cos(rlon1-rlon2))
	gcirc.distance=3963*D
	
	if sin(rlon2-rlon1)>0 then
		gcirc.bearing=rtod(2*MATH_PI - acos((sin(rlat2) - sin(rlat1)*cos(D)) / (cos(rlat1)*sin(D))))
	else
		gcirc.bearing=rtod(acos ((sin(rlat2) - sin(rlat1)*cos(D)) / (cos(rlat1)*sin(D))))
	end if
	end function  
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@


'set up eval variables
	for i=1 to 2
		evaluate_template("dim lat"+i+" as n")
		evaluate_template("dim latmin"+i+" as n")
		evaluate_template("dim latsec"+i+" as n")
		evaluate_template("lon"+i+"=0")
		evaluate_template("lonmin"+i+"=0")
		evaluate_template("lonsec"+i+"=0")
		evaluate_template("dim lat"+i+"sign as c")
		evaluate_template("dim lon"+i+"sign as c")
		eval("lat"+i+"sign")="north"
		eval("lon"+i+"sign")="west"
	next
	dim ptr as p
	ptr.distance=0
	ptr.bearing=0

' set up dialog
dim vR as C
dim vaddr1 as C="1600 Pennsylvania Ave NW, Washington, D.C., DC 20500"
dim vaddr2 as C="350 5th Ave, New York, NY 10118"
dim dlat1 as C
dim dlat2 as C
dim dlon1 as C
dim dlon2 as C
dim zdistance as N

vR=ui_dlg_box("geocode",<<%dlg%
{lf};
{region}
must have comma between each part - street, city, state, zip ;
Addr 1: [.70vaddr1];
Addr 2: [.70vaddr2];
{lf};
{sp=16}<Get Distance!get>;
{lf};
ADDR1 - Latitude: [.20dlat1] {sp=3} Longitude: [.20dlon1];
ADDR2 - Latitude: [.20dlat2] {sp=3} Longitude: [.20dlon2];
{lf=.5}
{endregion};
{region}
{frame=1,4}
{background=yellow}{lf};
Distance - [.20zdistance]              ;
{lf};
{endregion};
{justify=left|{lf};
{line=1,0}
{lf};
<Close> <Web>
%dlg%,<<%code%

IF a_dlg_button="Get"
	hourglass_cursor(.t.)
	dlat1=geocode_address(vaddr1).lat
	dlon1=geocode_address(vaddr1).lon
	dlat2=geocode_address(vaddr2).lat
	dlon2=geocode_address(vaddr2).lon
	
	dim zlat1 as N
	dim zlat2 as N
	dim zlon1 as N
	dim vlon2 as N
	zlat1=val(dlat1)
	zlon1=val(dlon1)
	zlat2=val(dlat2)
	zlon2=val(dlon2)
	ptr=gcirc(zlat1,zlat2,zlon1,zlon2)
	zdistance=round(ptr.distance,2)
	ui_beep()
	hourglass_cursor(.f.)
	a_dlg_button=""
END IF

IF a_dlg_button="Web"
	dim vurl as C="http://www.worldatlas.com/aatlas/latitude_and_longitude_finder.htm"
	sys_open(vurl)
 a_dlg_button=""
END IF
%CODE%)

[christappan]

Re: Mileage Calculation

I'm feeding this two straight addresses like:

cAddressStart = "123 Main Street Anywhere TN 37918"
cAddressEnd = "456 Central Avenue Somewhere Else TN 37922"

As I understand it, it's free to use Google's api up to 1000 a day(?). This is just like typing those two addresses into Google maps' driving directions but with better feedback if it doesn't find a location. It's returning the number one option per Google--the top pick if you used their website.

FUNCTION geoDriving AS C (cAddressStart AS C, cAddressEnd AS C )

debug(1)

cPreppedAddressStart = stritran(stritran(stritran(cAddressStart," ","+"),"++","+"),"++","+")
cPreppedAddressEnd = stritran(stritran(stritran(cAddressEnd," ","+"),"++","+"),"++","+")

string = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=" + cPreppedAddressStart + "&destinations=" + cPreppedAddressEnd + "&mode=driving&language=en&sensor=false&units=imperial"

jsonReq = http_get(string)

jsonBody = json_parse(jsonReq.body)

return = ""

dim pm as P

if jsonReq.error_code = 0  'if Google does not return errors
	'Google returns formatted addresses used in the actual distance calculation
	'in the form "1600 Breda Drive, Knoxville, TN, 37918".  If the address is invalid,
	'Google uses as much of the address as is valid, such as "Tennesee, USA" or 
	'"Knoxville, TN, USA".  So if a Google-formatted address has fewer than 3 commas,
	'the address entered was not valid for driving distance calculations
	
	originAddrSections = occursi(",",jsonBody.origin_addresses[1])
	
	if originAddrSections < 3
		return = "|Invalid Origin"		
	end if
	
	destAddrSections = occursi(",",jsonBody.destination_addresses[1])

	if destAddrSections < 3
		return = return + "|Invalid Destination"		
	end if
	
	if like("*|*",return) = .f.
		return = convert_type(round_up(val(jsonBody.rows[1].elements[1].distance.text),0),"C")
		if like("*ft*",jsonBody.rows[1].elements[1].distance.text) 'if less than 1 mile Google returns distance in feet
			if like("*1ft*",jsonBody.rows[1].elements[1].distance.text)
				return = "0"
			else
				return = "1"
			end if
		end if
	end if
else
	return = "|Invalid Origin|Invalid Destination"
end if

geoDriving = return

END FUNCTION

[martinwcole]

Re: Mileage Calculation

Chris,
I get an error message in V10, but works like a champ in V11 - returned "29" when I put in two addresses. Bet this took a few to get out all the kinks!!!!!!!!!!!!!!

[christappan]

Re: Mileage Calculation

Glad it works. I'm using v11. Maybe the json_parse() didn't exist in v10? Anyway, this wasn't familiar territory for me but Google's documentation made the initial process fairly easy; it did take some effort to determine what Google did for error handling. But I've done the work in A5 so I'm happy to share, just as I've taken quite a bit of others' code and ideas from here.

[martinwcole]

Re: Mileage Calculation

Wonder why the units is 'imperial' - sounds ilke imperial would be in kilometers, but the 29 it returned for me was I think accurate in miles.

[christappan]

Re: Mileage Calculation

http://en.wikipedia.org/wiki/Imperial_units

[martinwcole]

Re: Mileage Calculation

?geoDriving("3706 novus grand prairie tx 75052","4331 credit union dr anchorage ak")

Here is a correction in the code for distances greater than 999 miles
(remspecial)
thanks to the effors of Tom McCann and myself

FUNCTION geoDriving AS C (cAddressStart AS C, cAddressEnd AS C )
cPreppedAddressStart = stritran(stritran(stritran(cAddressStart," ","+"),"++","+"),"++","+")
cPreppedAddressEnd = stritran(stritran(stritran(cAddressEnd," ","+"),"++","+"),"++","+")
'msgbox("","" + cPreppedAddressStart)
'msgbox("","" + cPreppedAddressEnd)
string = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=" + cPreppedAddressStart + "&destinations=" + cPreppedAddressEnd + "&mode=driving&language=en&sensor=false&units=imperial"
jsonReq = http_get(string)
jsonBody = json_parse(jsonReq.body)
return = ""
dim pm as P
if jsonReq.error_code = 0  'if Google does not return errors
	'Google returns formatted addresses used in the actual distance calculation
	'in the form "1600 Breda Drive, Knoxville, TN, 37918".  If the address is invalid,
	'Google uses as much of the address as is valid, such as "Tennesee, USA" or 
	'"Knoxville, TN, USA".  So if a Google-formatted address has fewer than 3 commas,
	'the address entered was not valid for driving distance calculations
	
	originAddrSections = occursi(",",jsonBody.origin_addresses[1])
	if originAddrSections < 3
		return = "|Invalid Origin"		
	end if
	
	destAddrSections = occursi(",",jsonBody.destination_addresses[1])

	if destAddrSections < 3
		return = return + "|Invalid Destination"		
	end if
	
	if like("*|*",return) = .f.
		return = convert_type(round_up(val(remspecial(jsonBody.rows[1].elements[1].distance.text)),0),"C")
'		msgbox("","" + jsonBody.rows[1].elements[1].distance.text)
		if like("*ft*",jsonBody.rows[1].elements[1].distance.text) 'if less than 1 mile Google returns distance in feet
			if like("*1ft*",jsonBody.rows[1].elements[1].distance.text)
				return = "0"
			else
				return = "1"
			end if
		end if
	end if
else
	return = "|Invalid Origin|Invalid Destination"
end if

geoDriving = return

END FUNCTION

[christappan]

Re: Mileage Calculation

Thanks for sharing. No one in our company's driving that far, so I hadn't found that one.

[christappan]

Re: Mileage Calculation

Correction: remspecial() doesn't work here because Google returns things like 3.6 miles, which this turns into 36 miles. Replace
return = convert_type(round_up(val(remspecial(jsonBody.rows[1].elements[1].distance.text)),0),"C")
with
return = convert_type(round_up(val(stritran(jsonBody.rows[1].elements[1].distance.text,",")),0),"C")

[martinwcole]

Re: Mileage Calculation

thanks

[Ted Giles]

Re: Mileage Calculation

Wonder why the units is 'imperial' - sounds ilke imperial would be in kilometers, but the 29 it returned for me was I think accurate in miles.

In the UK.
Martin, we are still in Imperial - as in Miles - as far as road distances are shown on road signs.
New cars and satnavs have the option of displaying the distance in miles or kilometres.
The BBC quotes both kilos and miles, depending on who is doing the presentation. We also have Celsius and Farenheight temperature scales reported at the same time.

Weights are in kilogrammes, and it was illegal to also have pounds and ounces - which were imperial weights - but that was overturned and we now have kilos and Lbs/Ozs on things like meat in a butchers.
Packaged stuff in supermarkets is all in kg.

We drive on the left hand side of the road, drink warm beer, and have a 70 miles per hour speed limit which few people stick to.
Heating oil is about 85 cents a litre - yes a litre, not in gallons any more, and diesel is nearly $1 a litre.
My heating oil fuel bill averages at $400 a month - but then I have an AGA running full time.
Electricity is around $150 a month and going up by 10%.

My Motor Home is German made with right hand drive - for the UK market - so the door to get into the front cab is on the wrong side. Just the controls were moved.

Weather is most unpredictable.

Are we confused buy all this? What do you think?