Parsing Google Location History .KML with Pentaho Data Integration

In the following example we will use Pentaho Data Integration to parse a KML file, that contains all the coordinates (latitude / longitude) that Google have been saving based on my location during the last year, and store it in a MySQL database.

Through the following url you can see your location history on a map of Google Maps has saved Google if you have given then the concerning permissions:

https://maps.google.es/locationhistory/b/0

Surely more than one didn’t know.

 

LH1

 

 

Considering the format of the .KML file that Google  provides us we ‘ve defined the following steps:

  1.  XML Input Stream (StAX) at first for select our .KML file as datasource.
  2. Using a filter rows for avoid null values and considering that Google doesn’t provide us a unique identifier for a pair of values (date/ coordinates) we will define two separate paths.

LH5

   3.  As we can see in the screenshot, the id for the When element is always less than unity element id coord. Then, the Calculator will add one unit to the object ID through a predefined constant.

   4.We sort the two data streams and make them converge with an object Merge Join 

   5. Finally, use the object Split Fields to separate the latitude and longitude in different fields

LH2

   6. We use the Table Output object to store the output with the desired format in a table in our MySQL database.

Finally I attached the .ktr file.LocationHistory

Hope you will find it interesting

 

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *