Question on Using Distance Calculations / Google Maps

[lidocayne]

Good morning all. I have an interesting problem I was hoping to get some direction on.

I have a fixed list of addresses. I was hoping using something such as Google Maps, I could find a way for Filemaker to parse/extrapolate the driving distance between Address1 and Addresses 2,3,4,5. Addresses 2,3,4,5 will not change, but Address 1 will depending on the record that I am accessing. I just want a simple table to display the distances between branches that I frequent and Address1, so I can see which is closest to them quickly.

For example, Joe lives at address1. When I access joe's record, I would like a table (I don't need to see a map) to display distance to the branches (driving distance, thus why I use google maps) so I can quickly see and choose which would be closest for Joe to use.

I tried to search to see if someone had done this before, but didn't have any luck finding a request of this nature before. I would think since Google Maps does this for you when putting in the addresses there would be some way to extract the distance... but admittedly this is beyond my level of expertise at the moment. Extensive searching has not yielded anything like this as of yet.

Company Name Address Distance from Address

XYZ 312 N 8th 4 miles

TMC 3111 Plum 8 miles

etc.

Thank you for your help/guidance!

[Lee Smith]

See if this helps http://www.filemakerhacks.com/?p=3802

[lidocayne]

Thanks Lee! I appreciate that link. I had found that. The issue with that example is that it pops up a browser window with a map of the various waypoints in it. It doesn't though extract any information about the trip. It appears I could get a JSON parse of the google map information based on what I'm reading from the API info, but I don't know how to have filemaker parse the output such that it will display the information in the table.

Any thoughts?

[Lee Smith]

it's using web viewer?

Did you look at the part 2?

[lidocayne]

Lee-

I did. I'm going to have to delve in to this. It seems that Part2 offers much more complexity than I need, yet I see no where in there an attempt to extrapolate distances based on results from the Google Maps XML/JSON. I surprised no one before me has ever needed this feature.

[John Nolan]

I wrote this, and THEN checked your profile; it says you're on FM 11.

IF you're using FM 12, the "Insert from URL" script works pretty well.

I created a calculated field "directions_googleAPI":

"http://maps.googleapis.com/maps/api/distancematrix/json?origins=

" & Map start bookings ID::directions_google

&

"&destinations="& Institution::directions_google)

&

"&sensor=false"

(Where "directions_google" is my address fields formatted for google, with a couple of relations to identify the start and end for the trip )

The I used a trigger to set the "directions_googleAPI_inserted" field:

Insert from URL(Bookings::directions_goggleAPI)

Then a calculated field gives me the number I want:

Filter ( Parse between (directions_googleAPI_inserted; "distance"; " km" ); "0123456789." )

I get duration with a similar calc:

Filter ( Parse between (directions_googleAPI_inserted; "duration"; " min" ); "0123456789." )

(Parse between is a custom function from Brian Dunning's site:)

Edited May 20, 2012 by John Nolan

[lidocayne]

I actually do have 12 Advanced. THANK YOU! Will check it out and report back...

[Tim Falconer]

hi folks,

I was searching for an answer to a much simpler question, and ran across this thread. Cool stuff, but here's the (hopefully) much simpler question: I have an address book, basically the "Contacts" demo that comes with recent FMP (11 and 12 at least). It's got a map, nice.

We've got a warehouse, and I'd like to know the driving distance from our warehouse to each address. I'd love it if the driving distance were automatically put into a field in the address book that I could use for, well, the obvious: how much do we charge for a delivery to this client or that client, based on the distance and the price of the order? So all I need is the driving distance from the warehouse and I'm set.

The map in the Contacts demo is just a web viewer with this custom query in it: GetMap ( "Map1" ; 14 ; Contacts::Work Address 1 ; Contacts::Work Address 2 ; Contacts::Work City ; Contacts::Work State ; Contacts::Work Postal Code ; Contacts::Work Country ) ... and GetMap being a Custom Function that is included in the demo, which is pretty straightforward, I guess. I don't have any questions about it, just including it here for completeness:

Let  (

[

address =  Substitute  (

       address1 & " " &

       address2 & " " &

       city & " " &

       state & " " &

       postalCode & " " &

       country ;

       " " ; "+"

       ) ;

size =   GetLayoutObjectAttribute ( webViewerName ; "width" ) & "x" & GetLayoutObjectAttribute ( webViewerName ; "height" )

] ;

"http://maps.google.com/maps/api/staticmap?center="

   & address &

    "&zoom=" & zoom &

     "&markers="

      & address &

       "&size="

        & size &

         "&sensor=false"

)

I realized that simply typing "distance" and the two addresses into Google Search returns the driving distance with other information as the top hit. Would parsing that be a good place to start?

thanks

tzf

(I've worked with FileMaker since the late 90's, but have never spent a lot of time with it, until recently. I now drive FileMaker Pro Advanced on both Windows 7 and Snow Leopard, support 6 users, and have a Lion server running FileMaker Pro Server).

p.s. I'm pretty sure I could code up a solution- for instance, Google publishes their API which would be fine: https://developers.google.com/maps/documentation/distancematrix/ - I'm asking if someone else has already done something that would work for me and would be willing to share. thanks again. tzf

[John Nolan]

If I understand what you're asking, that's pretty well what I've done in the example above:

One has to construct a calculation that formats the addresses correctly for google.

Then I calculate another field, "directions_googleAPI" which is formed to work with the google API. I then set a field with Insert from URL(Bookings::directions_goggleAPI)". I parse out the distance and duration of the trip from that.

By the way, to be legal you should note that the Google EULA stipulates that the API must be used in conjunction with the display of a map.

[Tim Falconer]

John,

Thank you kindly for your response- quite informative. Sorry I didn't respond earlier, been working on other things...

Also, thanks for the pointer on the EULA!

Thanks,

tzf

[Orsini]

Thanks for this url.

[koolit5665]

I wrote this, and THEN checked your profile; it says you're on FM 11.

IF you're using FM 12, the "Insert from URL" script works pretty well.

I created a calculated field "directions_googleAPI":

"http://maps.googleapis.com/maps/api/distancematrix/json?origins=

" & Map start bookings ID::directions_google

&

"&destinations="& Institution::directions_google)

&

"&sensor=false"

(Where "directions_google" is my address fields formatted for google, with a couple of relations to identify the start and end for the trip )

The I used a trigger to set the "directions_googleAPI_inserted" field:

Insert from URL(Bookings::directions_goggleAPI)

Then a calculated field gives me the number I want:

Filter ( Parse between (directions_googleAPI_inserted; "distance"; " km" ); "0123456789." )

I get duration with a similar calc:

Filter ( Parse between (directions_googleAPI_inserted; "duration"; " min" ); "0123456789." )

(Parse between is a custom function from Brian Dunning's site:)

Hi John,

 

I know I'm resurrecting an old thread here but could you help out here?

I've followed your instructions and every things working apart from when the time to travel is over 59 minutes as google returns in the format of "3 hours 37 mins" and the custom function mentioned returns back 337.

 

See screenshot below:

 

 

Any ideas?!

 

If I could extract the "value:" 12999 figure (which I believe is a seconds count) then this would be great as I can convert it to hours then...

 

Cheers in advance...

Rick.

[jbante]

koolit, it looks like you already have the right idea. Just extract the duration>value and convert it to time, such as Time ( 0 ; 0 ; $durationValue ).

 

Your screenshot looks like the parsed mileage is turning out wrong. I believe the distance>text element can change units based on the URL parameters you set, but the distance>value should always be in meters, and may be the more reliable value to parse and convert from.

 

To reiterate John's point, Google's license terms stipulate that you can only use the APIs in their mapping functionality in conjunction with a Google map displaying associated data. There are also some serious restrictions on what kinds of applications you can use the Google APIs in connection with. MapQuest's APIs based on open data are much more permissive. The documentation for MapQuest's equivalent to the Google Distance Matrix API is here: http://open.mapquestapi.com/directions/#matrix

[koolit5665]

koolit, it looks like you already have the right idea. Just extract the duration>value and convert it to time, such as Time ( 0 ; 0 ; $durationValue ).

 

Your screenshot looks like the parsed mileage is turning out wrong. I believe the distance>text element can change units based on the URL parameters you set, but the distance>value should always be in meters, and may be the more reliable value to parse and convert from.

 

To reiterate John's point, Google's license terms stipulate that you can only use the APIs in their mapping functionality in conjunction with a Google map displaying associated data. There are also some serious restrictions on what kinds of applications you can use the Google APIs in connection with. MapQuest's APIs based on open data are much more permissive. The documentation for MapQuest's equivalent to the Google Distance Matrix API is here: http://open.mapquestapi.com/directions/#matrix

Hi Jbante,

 

Thanks for your input... The mileage is showing wrong because my calculation field doubles the actual figure to get the a return (there and back) figure...

 

What I'm struggling with is how to extract the time value (in seconds) from the data contained in the returned data ("value" line). The original example only extracted the numbers out of the 'hours and minutes' line whereas I'm just trying to get one figure...

 

I'm sure it's possible using the custom function mentioned above but I don't know how!

 

For example (using the code returned from Google); this:

 

Filter ( Parse between (directions_googleAPI_inserted; "duration"; " min" ); "0123456789." )

 

Returns:

 

337

 

Which is, in essence, 3 hours 37 minutes with all the text removed.

Cheers,

Rick.

[jbante]

What I'm recommending is that you should be parsing out a different value from the JSON than the duration text:

 

Let ( [

_duration = ParseBetween ( directions_googleAPI_inserted ; "duration" ; "status" ) ;

_value = ParseBetween ( _duration ; "value" ; "}" ) ;

_seconds = GetAsNumber ( _value ) ;

_time = Time ( 0 ; 0 ; _seconds )

] ;

_time

)

[koolit5665]

 

What I'm recommending is that you should be parsing out a different value from the JSON than the duration text:

 

Let ( [

_duration = ParseBetween ( directions_googleAPI_inserted ; "duration" ; "status" ) ;

_value = ParseBetween ( _duration ; "value" ; "}" ) ;

_seconds = GetAsNumber ( _value ) ;

_time = Time ( 0 ; 0 ; _seconds )

] ;

_time

)

 

Perfect! Thank you, thank you!

 

Works a treat now!