UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Google Maps Distance Matrix Results Into Access, Access 2013    
 
   
beanhasemail
post Oct 22 2016, 01:25 AM
Post#1



Posts: 23
Joined: 5-June 15



I apologize if this is the incorrect forum to place this, but I was not sure where else would be more important. Firstly I would like to state that I am not a programmer and I learn what I can on the fly by copying code samples and trying to adjust those to my needs. I am currently trying to use the Google Maps Distance Matrix to provide me with distance, duration, duration_in_traffic while providing departure time, travel mode, traffic assumption, unit of measure, and avoid tolls. I am trying to get results for a 1-3 origins with multiple destinations. I have looked at various examples that either are only for a single origin and destination which I cannot figure out how to alter or far outside of my reach (use programming language that I do not even know where to start or methods I simply do not understand {javascript, java, PHP, XPath, etc.}.

I am trying to get the data from JSON or XML into a tabular format (rows being records and columns being fields) that I can use in Access. I do not know anything about the structure of JSON or XML, but the Google results are throwing me for a loop because they are not formatted like all of the examples I see. All of the examples show all of the parent and child nodes grouped to together which makes intuitive sense on how they are related and fleshing them out, but Google's results do not follow this example. Google gives all of the origins and destinations at the beginning of the results and the details below with no ID being provided. This makes it unclear as to what origin-destination combination goes with what child records.

I have been doing searches the last three days trying to find something that I can work with, but everything I find does not fit my application or goes instantly to the deep end where I lose all footing. Any help anyone could give would be much appreciated.

Thank You
Go to the top of the page
 
jleach
post Oct 22 2016, 04:27 AM
Post#2


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


Hi - can you give an example of the results you're getting? I've worked with this in the past but don't recall the details well enough offhand (I thought this came back in as JSON - unformatted, usually, whereas examples on the web tend to be formatted - maybe that's the difference?)

In any case, if we can get a look at what you're receiving back that'd help a bit.

Cheers,
Go to the top of the page
 
MrHans
post Oct 22 2016, 09:17 AM
Post#3



Posts: 2
Joined: 31-July 16



Take a look at this post:
http://www.access-programmers.co.UK/forums...5339&page=5

It has a very clear and fully functional sample using the Google Distance Matrix API
Go to the top of the page
 
beanhasemail
post Oct 22 2016, 11:45 PM
Post#4



Posts: 23
Joined: 5-June 15



MrHans: Thank you for the link. I have looked at this post before, but it does not have the information that I am looking for. I am trying to import multiple origins and destinations and then pull the results out of what Google provides in JSON or XML. Thank you.
Go to the top of the page
 
beanhasemail
post Oct 22 2016, 11:52 PM
Post#5



Posts: 23
Joined: 5-June 15



Jleach:

Here is an example of the data that I am getting back from the Google Maps Distance Matrix API. I am having problems with the structure of the data and pulling out the results I need. The examples I have looked at for JSON and XML files show the child nodes being directly underneath the parent but for reason these results do not follow that pattern. If you can provide any guidance, it would be much appreciated.
Attached File(s)
Attached File  Google_Map_Distance_Results_JSON___XML.txt ( 2.58K )Number of downloads: 10
 
Go to the top of the page
 
jleach
post Oct 23 2016, 06:37 AM
Post#6


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


In Google's documentation, about 3/4 of the way down the page (https://developers.google.com/maps/documentation/distance-matrix/intro):

QUOTE
Rows are ordered according to the values in the origin parameter of the request. Each row corresponds to an origin, and each element within that row corresponds to a pairing of the origin with a destination value.


Let's format the XML a little easier (XML is far easier to work with in VBA, usually):

CODE
<DistanceMatrixResponse>
    <status>OK</status>
    <origin_address>Chicago, IL, USA</origin_address>
    <origin_address>Peoria, IL, USA</origin_address>
    <destination_address>Detroit, MI, USA</destination_address>
    <destination_address>Gary, IN, USA</destination_address>
    <row>
        <element>
            <status>OK</status>
            <duration>
                <value>15430</value>
                <text>4 hours 17 mins</text>
            </duration>
            <distance>
                <value>463032</value>
                <text>288 mi</text>
            </distance>
        </element>
        <element>
            <status>OK</status>
            <duration>
                <value>2356</value>
                <text>39 mins</text>
            </duration>
            <distance>
                <value>49840</value>
                <text>31.0 mi</text>
            </distance>
        </element>
    </row>
    <row>
        <element>
            <status>OK</status>
            <duration>
                <value>22078</value>
                <text>6 hours 8 mins</text>
            </duration>
            <distance>
                <value>675462</value>
                <text>420 mi</text>
            </distance>
        </element>
        <element>
            <status>OK</status>
            <duration>
                <value>9297</value>
                <text>2 hours 35 mins</text>
            </duration>
            <distance>
                <value>273560</value>
                <text>170 mi</text>
            </distance>
        </element>
    </row>
</DistanceMatrixResponse>



According to the docs, each row is corresponds to an Origin, so the first Row is shown for the first Origin: Chicago.

Within that row, we have two elements, the first showing information about the first destination (Detroit), the second element showing information about the second (Gary, IN). You can confirm these times roughly through google maps. Likewise for the next row (Peoria).

In any case, the order in which the origin and destination nodes are given correspond to the order in which the rows and elements are arranged.

Because they don't provide an actual ID, we have to take a step back and work with something that we can maintain the order in. Considering XML and an XML Parser, consider this psuedo-code:

CODE
arrayOfOrigins
originNodes = xml.GetNodes("origin_address")

foreach o in originNodes
   arrayOfOrigins.add(o.Value)
next o

'note that we didn't change any orders here, so array should
'now have the origin addresses listed in the same order as they
'are in the xml

'do the same for destinations, putting them into an array

destNodes = xml.GetNodes("destination_address")

foreach o in destNodes
   arrayOfDestinations.add(o.Value)
next o

'now we have two arrays, one with origins, one with destinations
' we can use their index as an identifier
debug.print arrayOfOrigins(0)
debug.print arrayOfOrigins(1)
'etc

'now get the row nodes
rowNodes = xml.GetNodes("row")

'loop all the rows, use i to keep track of position
for i = 0 to rowNodes.Count - 1

    elementNodes = rowNodes(i).GetNodes("element")
    
    'then loop nested elements, using j to keep track of position
    for j = 0 to elementNodes.Count - 1

        durationText = elementNodes(j).GetNode("duration/text").value
        distanceText = elementNodes(j).GetNode("distance/text").value
        
        'now use i and j to correlate back to which origin/destination we're on

        debug.print "For Origin " & arrayOfOrigins(i) & ", destination " & arrayOfDestinations(j) & " " & _
                          "we have duration and distance: " & durationText & ", " & distanceText

    next j

next i


So, we map the origin and destination values into an array without changing the order, then we loop the rows and nested elements while maintaining loop iterators (e.g., i, j), then use the loop iterators to determine which origin and destination we're currently working with.

Again, that's pure psuedo-code, but hopefully will give a bit of an understanding as to how we'd approach this.

I haven't looked at the demo MrHans provided - maybe it already does this, or maybe it uses an older version of the API. In any case, that should hopefully give you a push in the right direction.
Go to the top of the page
 
jleach
post Oct 23 2016, 06:50 AM
Post#7


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


With the previous reply showing the general approach, now if you really want to put together something yourself, you'll be using the MSXML2 as your XML parser.

Some official info here: https://msdn.microsoft.com/en-us/library/aa468547.aspx

Do a search for XML with VBA and you'll get a bunch of results and examples. The key points are to a) load the file/xml into a DOM object, b) use SelectNodes or SelectSingleNodes to get a node or group of nodes accordingly, and c) deal with the screwy syntax for looping with the XML parser.

For selecting nodes, you'll use SelectNodes or SelectSingleNode in conjunction with an XPath query. XPath is simple once you can find a decent reference for it: https://msdn.microsoft.com/en-us/library/ms...(v=vs.110).aspx

Example:

CODE
myIXMLDOMNodeList = someDocOrNode.SelectNodes("DistanceMatrixResponse//row")

(per XPath, the above says "get me all row elements that is one level deep in a DistanceMatrixResponse element)

For there, you can loop them, but the syntax is odd. I forget offhand, but I don't think you can do the usual "for integer" loop, so you may have to switch that up a bit (aircode below, it's been a while so the function/method names are probably incorrect):

CODE
Dim i As Integer
Dim rowNode As MSXML2.IXMLDomNode

Set rowNode = myRowNodesList.NextNode()    'which is really the first one
i = 0

While Not rowNode Is Nothing

    ' your logic here, using rowNode and i as your key and context

    rowNode = myRowNodesList.NextNode()
    i = i + 1
Wend


That should give you a start on the the technical means to pull together something like I listed in the psuedocode from the last reply.

Cheers,
Go to the top of the page
 
beanhasemail
post Oct 23 2016, 08:24 PM
Post#8



Posts: 23
Joined: 5-June 15



Jack:

Thank you for all of the guidance. I have been searching and muddling around with this for a while so most of what you reference sounds familiar. However the missing link is definitely how to address the strange syntax that Google uses. I will work through all of the information that you provided and see if I can getting some together, which may take me a bit given I learn as I code in regards to code. If I come up with something workable, I will be sure to reply showing what I came up with.

Thank you for taking the time to help me.
Go to the top of the page
 
jleach
post Oct 24 2016, 09:19 AM
Post#9


UtterAccess Editor
Posts: 9,812
Joined: 7-December 09
From: Staten Island, NY, USA


Glad to help. Unfortunately there's a fair bit of fundamental knowledge requirements to get through it, so it'll keep you busy for a few minutes working out the details.

In terms of the correlations, just remember that the order in which the origin and destinations are first listed correspond to the order that the row and elements (respectively) are listed. I agree, it's an odd setup: they could have easily put in some correlation IDs to make the job a bit easier, but nevertheless all the required info is there, just a bit more of a pain to work out.

It occurs to me also that a VBA.Collection or array of User Defined Types would also work rather than separate arrays for pulling the orders back together. In any case, there's a number of ways to do the job, keeping in mind that the critical piece is maintaining the order in which the results are listed.

Where we on a different language than VBA, this would have been a lot simpler, as we could parse the JSON result directly into an object and do something like this:

CODE
var results = JSON.parse(resultsFromGoogle)

var origin1dest1 = {}
origin1dest1.origin = results.origin[1];
origin1dest1.destination = results.destination[1];
origin1dest1.distance = results.row[1].element[1].distance.text
origin1dest1.duration = results.row[1].element[1].duration.text

// origin1dest1 now contains all the details for that pair


Unfortunately XML doesn't have such a nice parsing system, and VBA doesn't handle JSON very well at all.

Good luck!
Go to the top of the page
 
beanhasemail
post Oct 24 2016, 11:55 AM
Post#10



Posts: 23
Joined: 5-June 15



Jack:

Thank you for the additional information and I have been muddling through this for a while so I have been able to make the request to Google and get the data in to VBA and display as a depug print ( which was a huge step for me). The information you have should at the very least allow me to test fail repeat until I get something put together that will least give me a debug-print from which I will then try to populate my fields. I will see how I do.

Thank you again for taking the time and I will be sure to upload when I have something workable.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:59 AM