Map Something! – Collaborative Mapping


Along the lines of Draw Something, I present Map Something, a collaborative, live-updating mapping site. Not a finished project, but pretty cool so far. You can navigate around (i.e. pan/zoom), add points, and delete all points. The really cool thing is that when you do any of that, it updates automatically in everyone else’s browser. Want to show someone where to meet? Navigate there and show them!

Future updates would include tackling line/polygon geometries, selective deleting, and chat.

The live-update is courtesy of Firebase, and the map is, of course, Google Maps API. Give it a try!



As an exercise in file format juggling, I made this example using the UTM grid installed with ArcGIS (a shapefile).

Convert Shapefile to JSON

This is fairly simple at ArcGIS 10.2 (new at 10.2), using the Features to JSON tool. Output is .json file.

Load JSON into Firebase

If I have an excuse to use Firebase to host data, I will (no server code, free unless this goes viral, etc.). I suppose I could host the JSON directly in my web hosting account, but Firebase is more fun. The JSON text itself is entirely embedded in the JavaScript, which I certainly wouldn’t do in a production environment. Loading JSON into Firebase is as easy as setting a reference to your Firebase location, and calling .set() to overwrite, or .push() to append.

Plot Firebase data in Google Maps

Get the JSON response from Firebase using a URL like: Firebase Location + ‘.json?callback=’ + Some Function. This requests JSON from Firebase, then triggers Some Function. Inside my function, I parse the JSON into polygons, add to the map, set up infoWindows, etc.

At its worst, this is a UTM zone map. At its best, this is a way to get complex(ish) spatial data into Firebase to take advantage of the live data side of things. Have fun.


Multiplayer Hangman


Still on my Firebase kick. Here is a live, multiplayer game of hangman I came up with. There are unlimited guesses, and really no amazing payoff to winning – it was more of a personal exercise in JavaScript/jQuery/Firebase than anything else.

The cool thing about it is you can see live changes other players are making. Since there will hardly ever be two people playing at once, you can convince yourself that it’s live by opening two browser windows to the page. Enjoy.



EDIT: this map was once boringly called “Point Recorder” but is now called CinnaMap.

One of my coworkers is famously obsessed with cinnamon buns and I thought it would be fairly simple to set up a map on which to log cinnamon bun ratings.


  • Add points
  • Attribute points
  • Save points
  • Free
  • No onsite server

I’m sure there is a solution out there that will do this, but after a while of searching and finding a few options that didn’t quite fit my needs (like, say, Fulcrum or ArcGIS Online), I bit the bullet and made my own.

The APIs I used are Google Maps API (the map), Firebase (the database-type thing), and Maptiks (analytics). As my expectations for this map going viral are low, I should be well within the usage limits for free access to each.

To add a point: start editing (checkbox at bottom-right), click where you want to add a point, and fill out the data prompts. That’s it. Have fun. Feel free to add as many points as your heart desires.

Near Analysis: ArcPy vs. NumPy/SciPy

I’ve been slowly exploring the NumPy Python library. Without delving into the technical details, which I don’t understand, suffice it to say that using NumPy arrays allows certain speed advantages over nested Python lists, and presumably, ArcPy cursors.

Take the Near Analysis. For every point in a feature class, calculate the distance to and record the object ID of the nearest point. Given 1,000 points and no fancy indexing algorithms, this means calculating 1,000,000 distances (or 999,000 – we don’t care about distance to self). 10,000 points? That explodes or distance calculations to 100,000,000.

Some options:

  1. We could run the out-of-the-box ArcGIS Near tool, but that requires Advanced licensing, which I don’t have.
  2. We could make our own Near tool, using two arcpy.da cursors.
  3. We could load our points into a NumPy array, using FeatureClassToNumPyArray, and use NumPy/SciPy matrix operators for the analysis.

Here’s the code I came up with for options 2 and 3:

>>> import arcpy, numpy, numpy.lib.recfunctions, scipy.spatial, random, cProfile
... fc = "bc_geoname_albers"
... def numpyNear():
...   npArray = arcpy.da.FeatureClassToNumPyArray(fc,["OID@","SHAPE@XY"])
...   distArray = scipy.spatial.distance.pdist(npArray['SHAPE@XY'],'euclidean')
...   sqDistArray = scipy.spatial.distance.squareform(distArray)
...   nearFID = npArray['OID@'][numpy.argsort(sqDistArray)[:,1]].transpose()
...   npAppend = numpy.lib.recfunctions.append_fields(npArray,'NearFID',nearFID)
...   nearDist = numpy.sort(sqDistArray)[:,1].transpose()
...   npAppend = numpy.lib.recfunctions.append_fields(npAppend,'NearDist',nearDist)
...   outFC = r'in_memory\outPts'+str(random.random()*1000)
...   outFeat = arcpy.da.NumPyArrayToFeatureClass(npAppend,outFC,"SHAPE@XY")
...   arcpy.CopyFeatures_management(outFC,r'in_memory\numpy_Pts')
... def arcpyNear():
...   lyr = 'arcpy_Pts'
...   arcpy.MakeFeatureLayer_management(fc,lyr)
...   arcpy.AddField_management(lyr,'NearFID','LONG')
...   arcpy.AddField_management(lyr,'NearDist','DOUBLE')
...   with arcpy.da.UpdateCursor(lyr,['SHAPE@','OID@','NearFID','NearDist']) as cursor1:
...     for row in cursor1:
...       dist = float("inf")
...       with arcpy.da.SearchCursor(lyr,['SHAPE@','OID@']) as cursor2:
...         for row2 in cursor2:
...           if row[1] != row2[1]:
...             curDist = row[0].distanceTo(row2[0])
...             if curDist < dist:
...               dist = curDist
...               nearFID = row2[1]
...       row[2] = nearFID
...       row[3] = dist
...       cursor1.updateRow(row)
... cProfile.runctx('numpyNear()',None,locals())
... cProfile.runctx('arcpyNear()',None,locals())

Even though the result of each function is essentially the same, using 1,000 points the NumPy function completes in 0.516 seconds, while the ArcPy takes 202.062 seconds.

Side note: see use of cProfile for benchmarking method.

Here is the NumPy method, using three points as an example.


1. Create the NumPy Array (FeatureClassToNumPyArray):

npArray = arcpy.da.FeatureClassToNumPyArray(fc,["OID@","SHAPE@XY"])

[(0, [0.0, 0.0])
 (1, [3.0, 0.0])
 (2, [3.0, 4.0])]

2. Calculate all distances using SciPy pdist function (result = distance from 0 to 1, 0 to 2, and 0 to 3):

distArray = scipy.spatial.distance.pdist(npArray['SHAPE@XY'],'euclidean')

[ 3.  5.  4.]

3. Create a squareform of the distance array (row 1 is all distances from point 0, row 2 is all distances from point 1, etc.):

sqDistArray = scipy.spatial.distance.squareform(distArray)

[[ 0. 3. 5.]
 [ 3. 0. 4.]
 [ 5. 4. 0.]]

4. For each object ID in the original points, find the second nearest value’s index (so, not the distance to itself) – result is list of positions, by row, in squareform distance array holding the second-nearest value:

nearFID = npArray['OID@'][numpy.argsort(sqDistArray)[:,1]].transpose()

[1 0 1]

5. Append the array of NearFIDs to original point array, using numpy.lib.recfunctions.append_fields:

npAppend = numpy.lib.recfunctions.append_fields(npArray,'NearFID',nearFID)

6. Get second-nearest distance value for each row in squareform distance array:

nearDist = numpy.sort(sqDistArray)[:,1].transpose()

[ 3.  3.  4.]

7. Append the array of NearDist values to original point array:

npAppend = numpy.lib.recfunctions.append_fields(npAppend,'NearDist',nearDist)

8. Convert back to feature class and save (NumPyArrayToFeatureClass):

outFC = r'in_memory\outPts'+str(random.random()*1000)
outFeat = arcpy.da.NumPyArrayToFeatureClass(npAppend,outFC,"SHAPE@XY")

Final attribute table, including original object ID, Near object ID, and Near Distance:


The Real PG Snow Priority Routes?

PG_roadsThe City of Prince George publishes an annual Snow Operations Map, however, according to it my street (lowest priority) should not get plowed nearly as often as it does (almost always within 24 hours of snowfall). The accompanying, confusing infographic (which does not seem to differentiate between Priority 1 and 2, by the way), makes a passing reference to Bylaw 8625, which does shed some light on the mystery. My street is listed in the bylaw, amongst almost all other highest priority streets in PG.

I spent some of the morning modifying the PG Street Centrelines file (downloaded from the PG Open Data Catalogue) and you can inspect the resulting map here.

Don’t get me wrong: I’m not complaining that my street gets plowed regularly (thank you, plow operators!), I just think the City could use some clarification on the snow operations policy.

Word tables to Excel: Python

Question: I’ve got a folder of 100+ Word documents, each containing a table or two. Can you format that into an Excel spreadsheet for me?

Answer: Yes – bask in the power and glory of Python.

Using the Python for Windows Extension (specifically the win32com.client library), you can drive both Word and Excel with Python. Knowing that this functionality exists is half the battle.

# import libraries
import win32com.client as win32
import os

myDir = r'C:\Projects\ProjectX'

# open invisible Excel app
XL = win32.Dispatch('Excel.Application')
XL.Visible = 0
# load pre-made workbook
XLbook = XL.Workbooks.Open(os.path.join(myDir,'ProjectX_Spreadsheet.xlsx'))
# navigate to first worksheet
XLsheet = XLbook.Worksheets(1)
# counter to keep track of Excel row
XLrow = 2

# loop through files in directory
for myFile in os.listdir(myDir):
 filepath = os.path.join(myDir,myFile)
 filename = os.path.splitext(myFile)[0]
 ext = os.path.splitext(myFile)[1]

 # check if *.docx {optional}
 if ext == '.docx':
 # open invisible Word app
 word = win32.Dispatch('Word.Application')
 word.Visible = 0
 # open Word doc to read
 doc = word.ActiveDocument

 # access first table in Word doc. For subsequent tables, increase index.
 table = doc.Tables(1)

 # get (Word) and set (Excel) some data
 XLsheet.Cells(XLrow,1).Value = table.Cell(Row=1, Column=1).Range.Text

 # get (Word) and set (Excel) some more data
 XLsheet.Cells(XLrow,2).Value = table.Cell(Row=2, Column=3).Range.Text

 # move to next row
 XLrow = XLrow + 1
 # close the current Word doc

# exit the Word app
del word
# save and close Excel app
del XL