MyMaps to Spreadsheet, KML to CSV and back

I'm sharing a couple Python scripts that I used to update my Google MyMaps map for a website that I manage.

Know your data

On the website I manage for the Friends of Silverbrook Cemetery, I created a Map of Sections page with an iframe of a Google MyMaps page.

Each of these colored sections is a map layer of polygons. The major sections are Old Grounds, City Addition, Bond Addition, New Addition, Garden of Memory and Garden of Eternal Peace. 

Each of those polygons was hand drawn in MyMaps.

In addition to the polygon, I have pins in a separate layer called "Points of Interest". 

Those pins have a slightly different data structure than the polygons.  For now I'm just going to focus on the polygons.

As an example, I'm going to take that large yellow triangle in section New-63 and distort it.  First, I need to export the New layer of the map to a KML file. 

I select "Export to KML/KMZ".

I just need the KML file.  A KMZ is a zip archive of KML files and a file of meta data.  I don't need all that.  I just want to work with one layer at a time.

As you can see, a KML file is an XML file with a schema defined by OpenGIS.

Searching for New-63, I see this section.

A triangle polygon has four points.  The first and fourth points are the same.  In a "line" type shape, the first and last points do not have to match.

I want a spreadsheet that contains all the coordinates, grouped by section and ordered by their sequence around the polygon.  The first line will be a header.

Code to CSV

Here's the whole Python script.

 

import xml.etree.ElementTree as ET

 

# KML namespace

ns = {"": "http://www.opengis.net/kml/2.2"}

 

# Where is "coordinates" in each Placemark?

wherecoords = 'Polygon/outerBoundaryIs/LinearRing/coordinates'

 

kmlfile = 'New Addition.kml'

# kmlfile = "Bond's Addition.kml"

# kmlfile ='City Addition.kml'

# kmlfile = 'Garden of Memory.kml'

# kmlfile = 'Old Grounds.kml'

 

csvfile = kmlfile.split(sep='.')[0] + '.csv'

fout = open(csvfile,'w')

 

print ('Section,RingOrder,Long,Lat')

print ('Section,RingOrder,Long,Lat',file=fout)

 

tree = ET.parse(kmlfile)

root = tree.getroot()

 

docelem=root.find('Document',ns)

 

 

for placemark in docelem.findall('Placemark',ns):

    Section = placemark.find('name',ns).text

    coords = placemark.find(wherecoords,ns).text

  

    RingOrder = 0

    for ln in coords.splitlines()[1:-1]:

        RingOrder += 1

        cols= ln.split(sep=',')

        print(Section,RingOrder,cols[0],cols[1],sep=',')

        print(Section,RingOrder,cols[0],cols[1],sep=',',file=fout)

       

fout.close()

The only module I imported is the ElementTree API.  One thing I struggled with is that the Namespace for your XML schema needs to be specified in most of the calls to API.  You can specify your namespaces as an array of dicts (dictionary pairs).  I'm just using one namespace, so I left its name a null.

The coordinates for the polygon are several layers of elements deep.  Rather than have nested find functions on the Placemark node, I just define the find string once.

I wanted to work with one KML file at a time.  Rather than parameterize it, I just comment out the layers I'm not working with at the moment.

"fout" is the output file handle to the CSV file.  I print the header to the file and the screen.

The parse function reads the file and puts it into an XML data structure.  This is definitely labor-saving magic.

"root" is a pointer to the top of the XML tree.

"docelem" points to the Document element.

Each polygon is an element contained in the Placemark element.  Therefore, there is a for-loop on Placemark.

The name of each polygon ("Section") is the text of the name element.

The "coordinates" element is three elements in.  The "wherecoords" string hops into it.

Each coordinate pair is terminated with a comma, a zero and a newline.  The for-loop assigns each coordinate line to "ln", skipping over that first empty line feed.

Distort and Import

Now I have a CSV file of the polygons in the New Addition.

For demonstration, I'm going to take the bottom point of New-63 and make it "droopy" down to the bottom of New-62.

All I have to do is copy the yellow cells to the green cells, and then save the CSV file.

The following Python script reads the KML file into an XML memory structure as before.  Then it reads the CSV file and updates each of the coordinates elements.  The script is below.  You will notice some code reuse.

 

import xml.etree.ElementTree as ET

 

# KML namespace

ns = {"": "http://www.opengis.net/kml/2.2"}

 

# Where is "coordinates" in each Placemark?

wherecoords = 'Polygon/outerBoundaryIs/LinearRing/coordinates'

 

def update_tree():

    # print("Update tree here for section " + CurrentSection)

    FindPath = "./Document/Placemark/[name='" + CurrentSection +"']"

    # Find Placemark of currect Section

    placemark = root.find(FindPath,ns)

    # Dig down to find the coordinates element

    coordelem = placemark.find(wherecoords,ns)

    #Set coordinates text

    coordelem.text = coords

 

 

# Define a KML and CSV files

kmlfile = 'New Addition.kml'

# kmlfile = "Bond's Addition.kml"

# kmlfile ='City Addition.kml'

# kmlfile = 'Garden of Memory.kml'

# kmlfile = 'Old Grounds.kml'

 

print('kmlfile: ' + kmlfile)

 

filebase = kmlfile.split(sep='.')[0]

csvfile = filebase + '.csv'

print('csvfile: ' + csvfile)

 

kmloutfile = filebase + '_out.kml'

print('kmloutfile: ' + kmloutfile)

 

# Open and read KML file into a tree

 

tree = ET.parse(kmlfile)

root = tree.getroot()

 

docelem=root.find('Document',ns)

 

# Open a CSV file

fin = open(csvfile,'r')

 

 

lineoftext = fin.readline()

 

#Discard the header, if there is one

 

if (lineoftext.startswith('Section')):

    lineoftext = fin.readline()

 

cols = lineoftext.rstrip().split(sep=',')

#cols: [Section,RingOrder,Long,Lat]

 

#First line setup

coords = "\n"

CurrentSection = cols[0]

print(CurrentSection)

 

#Read until end of file

while lineoftext !="" :

    if cols[0] != CurrentSection:

        coords += "%12s" % " " # Indent 12 chars after last newline

        #Update tree

        update_tree()

        #Set new CurrentSection

        CurrentSection = cols[0]

        print(CurrentSection)

        #Reset coords variable

        coords ="\n"

       

    # Append to coords variable 

    coords += '%14s' % " " # Indent 14 chars

    #Long,Lat,Height newline

    coords += cols[2] + ',' + cols[3] + ",0\n"

    lineoftext = fin.readline()

    cols = lineoftext.rstrip().split(sep=',')

 

   

else:

    coords += "%12s" % " " # Indent 12 chars after last newline

    #Update Tree

    update_tree()

   

print("done")   

 

fin.close()

 

xmlstr = ET.tostring(root,encoding='utf-8',)

xmlstr_nons0 = xmlstr.decode('utf-8').replace("ns0:","").replace(":ns0","")

 

fout=open(kmloutfile,"w")

fout.write("<?xml version='1.0' encoding='UTF-8'?>\n")

fout.write(xmlstr_nons0)

fout.close()

 

As before, import the ElementTree API, parse the file names, read in the KML file to the XML structure.

This time we loop on each line read from the CSV file.  If the coordinates are in the same section, add those coordinates to the "coords" string.  If the section changes, update the XML structure and then reset the coords string.  The else block takes care of the last read of the CSV file.

I used the global "root" node pointer and CurrentSection string in the function update(tree) instead of passing them in as parameters.  It's a little less portable, but works fine here.

One annoying feature of ElementTree is that is wants to prefix a namespace tag when you write out the structure.  MyMaps hates this.

"xmlstr" strings gets a dump of the XML structure.  "xmlstr_nons0" removes the "ns0:" and ":ns0" strings.

When everything works, the CSV file looks like this:

To test this out, I'll create a new map and import the file.

Back in MyMaps, I click on "+Create a New Map" button.

Now I'll import the file 'New Addition_out.kml'.  Here's the map after the import.

Yes, it does need formatting.  But the bottom of section New-63 is now the same as New-62.

If you want to replace the existing layer, delete the old layer and replace it with your new one.

 

Helpful Links:

·        The ElementTree API

 

Bob's Tech Corner

"MyMaps to Spreadsheet, KML to CSV and back"

https://bobstech.rvnllc.com

Bob Nightingale, info@rvnllc.com

Created 02-NOV-2020