Tree inventory data helps municipalities create urban forest management plans, allocate funding and proactively manage trees to ensure their long-term health. Most tree inventory and mapping software platforms require data to be geocoded, yet many municipalities and nonprofit organizations only track the postal addresses of their trees.

In this post, we will outline how you can geocode your address-based tree data without an expensive geographic information system (GIS) or technical expertise. Geocoding refers to the process of assigning longitude and latitude information to addresses so they can be placed as points on a map.

Why geocode your address-based tree data?

Having address data on your trees is important in order to find the general tree location. We plug addresses, not coordinates, into our GPS in order to find a place. However, geocoded data is important for identifying trees once you’re at a specific location. In both urban and rural settings it is common to find multiple trees of the same species at one address, which can make it difficult to locate a specific tree without additional identifying information.

Not only do maps make it easier to locate a tree in the field, they also help us identify actionable insights and make more informed management decisions. Unlike a spreadsheet of tree data, a map of your trees can help you track the spread of pests and disease, visualize how mature trees are dispersed across your city and identify which areas have the highest tree mortality rates. Additionally, the more people involved in maintaining street trees, the more helpful maps are in coordinating volunteers and municipal employees, and updating key information.

After you go through the process of geocoding your address data, all trees listed at the same address will have identical longitude and latitude. You will need to update this data either in the field or using satellite data as a reference to reflect the exact location of a tree at a particular address.

How does geocoding work?

Most simply, geocoding is performed using a reference layer. The process involves matching the to-be-geocoded addresses from your spreadsheet to the street names and address ranges in a street network file. The system matches the street name in your spreadsheet to a reference table and map. Once the street name is matched, all address ranges for this street are examined to identify the specific segment of a street where the address is found. Since the geocoder knows the coordinates of the endpoints of each street as well as the range of street numbers for a given segment, the software can estimate the address coordinates. Most geocoding services place trees at the front and center of the parcel with the associated address. However, some more advanced services allow you to choose how far off the center point of the adjacent road you want to place a given point.

Once you have geocoded tree data you can upload your data to mapping platforms like Carto, QGIS or OpenTreeMap. Carto and QGIS are not industry-specific; however, OpenTreeMap was designed specifically for mapping urban trees and green infrastructure.

Using Texas A&M’s Geocoding Service

We’ll walk through the steps for using Texas A&M’s geocoder, which allows you to geocode 2,500 records for free. There are numerous other services available, however, many require technical expertise and/or software licenses. Texas A&M’s geocoder allows you to upload a database (access file) or text file (csv, tsv) of address data to their website and generate latitude and longitude values. The system can geocode thousands of records in minutes.

Geocoding Instructions

1. Create an free account with Texas A&M GeoServices.
2. Navigate to the Batch Geocoding page of their website. Click “Start – Step 1>>.”
3. Click “Add New Database.”
4. Click “Upload New Database.”
5. Choose the file from your computer and designate the type and click Upload. For this example, we used a comma separated values (.csv) file. Make sure to follow the file naming notes listed on their website and include column names in the first row of your spreadsheet or database.
6. Once you validate that the geocoder can open and read your file, choose the columns from your file that want to process. The required fields (“Address”, “City”, “State”, and “Zip”) must be present in your database or file even if these fields are blank. The system will not process records without these fields present.
7. Use the dropdown lists to identify the fields in your table that correspond to the input fields the geocoder expects to see. Make sure to only select each of your fields in a maximum of one dropdown.
8. Choose your processing options and Click “Start Process.” Rather than wait to view your results you can opt-in to receiving status notifications via email. You will receive an email with a link to download your geocoded data once the process is complete.

In the spreadsheet above, we have highlighted the four columns added after the geocoding was completed. You can reference Texas A&M’s website for additional technical details on how the longitude and latitude results were generated and explanation of the values for the “MatchType” column.

We took the newly geocoded data and uploaded it to the three aforementioned mapping platforms: Carto, QGIS and OpenTreeMap.

The less accurate information you have on tree location, the higher the chance the wrong maintenance task is performed on the wrong tree. At best, this results in the misallocation of finite resources and at worst potentially removing an otherwise healthy tree. With geocoded inventory data you are on your way to making more informed management decisions that ensure you are allocating resources as efficiently as possible.

While it is much easier and less expensive to build a map with existing data that requires some modifications than reshoot your entire inventory using a GPS device, moving forward we recommend you use a mobile mapping application or portable GPS device so that you can capture detailed location information at the time of planting, and don’t have to rely on a third party to maintain your database. We also recommend checking and adjusting tree locations as part of routine fieldwork.

Run into snags following our geocoding instructions? Want to learn more about different mapping options? Drop us a line at [email protected]. We’d love to hear from you.

I just wrote up a meaty Labs post on my idea to visualize tree, species, and user edits over time within exported data from PhillyTreeMap.org, and already covered all the joining, formatting, converting, and uploading necessary to get to this point, along with some simple visualizations at the end. If you haven’t read it, go ahead. I’ll wait here. Because with this post I’m diving straight in to the temporal visualization features of CartoDB’s Torque.

Briefly, though, to reiterate: What are my goals for visualizing the 2 years of PhillyTreeMap user edits over time? I wanted to create something parallel to Mark Headd’s homicide data visualization (also done with Torque) but that told a story over time that was more uplifiting. (What’s more uplifting than trees?) I also hoped my visualization would give us a rough idea of what neighborhoods and areas around Philadelphia have the most active PhillyTreeMap user edits, as well as what times of year seem most active. One could use that knowledge to determine and plan where or when to do outreach about PhillyTreeMap or the programs of our partners, like PHS Tree Tenders. What neighborhoods don’t have many user edits? When does participation drop off? On the flip side, where and when are urban forestry efforts succeeding in engaging the community? A time based spatial visualization can help us answer those questions – and look really cool in the process!

Update 12:30pm, 8-16-2013: CartoDB is working on a fix for the WKT issues I stumbled upon in this blog and tweeted a workaround. Thanks Javier!

Many months ago, after the City of Philadelphia released some of its Part 1 Crime Incident data on OpenDataPhilly, I read a blog post by our very own Chief Data Officer Mark Headd where he visualized 6 years of homicides in the City of Brotherly Love on a temporal map using CartoDB’s Torque library. While the story the map tells is an important one, it is also depressing and sad – every second, as you watch, more dots appear on your screen representing way too many homicides in our city.

I was talking with a friend outside Azavea about Headd’s visualization, and posed a question: “What positive, uplifting change over time in our city could we tell the story of?” I sometimes get the feeling that so much data and visualizations of it are negative or otherwise shock us: from our struggling education system, to stolen bikes, to the disparate impact of voter ID laws. While visualizations like these uncover important stories to tell, so much sad news (for me at least) can sap my motivation to help fix it all. We need to visualize the good and give praise for what’s working, as much as we should analyze the bad and criticize what still needs to be done.

Hearing my frustration, my friend asked, “What about tree plantings or something?”, I assume without even realizing the connection she had just made in my mind.

Of course! That’s it! I happen to work for Azavea, where we craft OpenTreeMap, the best open source public tree inventory software around! I knew I could easily export data from PhillyTreeMap.org for almost two full years worth of ongoing, crowdsourced tree inventory and edits to the map in Philadelphia. We know that having more green, leafy trees and nature around make people happier psychologically, increase property values, clean our air and water, and save electricity and our environment. This was going to be a fun project.