Extracting Text Using ArcMap’s Field Calculator

There are a lot of tools in Microsoft Excel I use on a regular basis. I use the LEFT(), RIGHT(), or MID() formulas in Excel often to extract sections of text. For example, I may have a column with addresses, like below, but all I need is the street name.

Click on any image for higher resolution version.

In Excel, it is fairly simple to do that. In this case, we need to find the first space between the number, then return everything after that. Here’s the formula:

=MID(E2,FIND(“ “,E2)+1,256)

This command tells Excel to go to cell E2, find the first space “ “ in that cell, move over one character, and then return that character and all other characters up to 256 characters after it (the maximum for an Excel cell). Here’s what it looks like:

Fairly quick and painless to do.

But what if you want to do the same thing in ESRI’s ArcMap?

You could do what I used to do. Export the attribute table, open that in Excel, work that formula magic, and then import/join the table back to the original layer. It was a bit cumbersome. And I would end up with all these interim files and layers that needed to be cleaned up.

Wouldn’t it be nice to do the text extraction right in ArcMap?

Since around 2004, ArcMap has been released with Python capabilities. And while it hasn’t taken me over 10 years to realize that, it has taken me at least five years to start using Python regularly to do the same task above within ArcMap.

So, while this is old hat to a lot of folks, I’ve only been taking advantage of Python for the past five years. I’ve learned that even if I have to go learn a new function, it is always quicker to go the Python way. Let me show you.

Python .split() function and the re module (and some good old VBA, too)

In ArcMap, you can use Python functions in a number of ways. And like Microsoft Excel formulas, there are several ways we can go at this with Python in ArcMap.

Here’s the problem: We have addresses that contain numbers and text, separated by spaces. All we want is the text part, with its clarifying spaces (but no leading space – the space between the numbers and text).

We could use the .split() function. This is probably the simplest function you can use. Like all Python functions, it operates by assigning the input (our field name surrounded by exclamation points) followed by a period, then calling out the function, then specifying the parameters to base the split on within the parentheses (in our case, a space). If you need to, you can add a number in brackets to specify the index position of the text string to return. In our case, we’d want it to start returning from the second position in the text block (after the numbers), which would be 1 (remember, in Python, the first position of any list is number 0). It would look something like this:

!NAME_OF_FIELD!.split(“ “)[1]

So, for an address like:

71 BOWSPRIT CLOSE (0 position is ‘71’, 1 position is ‘BOWSPRIT’, 2 position is ‘CLOSE’)

We would get this:

BOWSPRIT

Yeah! Success! We got just the street name!

But that’s not what we want. We want:

BOWSPRIT CLOSE

You could make this cumbersome and separate out each word and then join them back up with the Python function .join(). But that would mean creating extra fields and clean up work afterwards. And that’s exactly what we are trying to avoid! While .split() is not the right tool for this particular exercise, it is a very functional function (pun intended) that does work well to extract text so I wanted to go over it here. Keep this in your back pocket for other instances where only one line of code is needed.

However, for this exercise, we’ll want to use a python sample I snagged from the internet. Three simple lines of code takes advantage of the Python module ‘re’ and gets us (almost) what we need.

Let’s go through it step-by-step.

Step 1 – Create a new field

Add the layer you want to alter into ArcMap and open its Attribute Table (right click on layer, then select Open Attribute Table).

Add a new field (column) (click on the Table Options drop down menu, then select Add Field).

Name your new field and select the type needed. For this example, we’ll name it StrtNm and select Text for the type. We can leave field length at the default of 50 characters. If it is not long enough, we can re-do this whole process.

Step 2 – Calculate the field contents

Now we can start the Python magic. Right click on the heading of your new field (column). From the pop-up menu, click on Field Calculator.

At the top of the Field Calculator pop-up dialog box, choose the Python Parser. The functions on the right side of the dialog box will change. Since we want to alter text data, click on the String type to filter the list of functions.

For this exercise, we’ll need the Codeblock, so click on the Show Codeblock option. It should look like this:

NOTE: If you don’t know how to use a particular Python function, you can click on the link About calculating fields. This will open ArcMap’s Help documentation samples. In addition, you can always look it up online. The GIS Stack Exchange has a wealth of samples, which is where I got the one I’m about to show you.

Next, you’ll want to copy/paste the following into the Pre-Logic Script Code box.

import re

def strip_digits(s):

return re.sub(“\d+”, “”, s)

The first line imports a module called ‘re’ which will allow us to use regular expression matching operations similar to Perl. In the second line, we define our new function strip_digits. The magic happens in the third line, which will strip out all the digits (d) from whatever we feed it. I’m not a programmer, so don’t ask me to explain it. Suffice to say it is calling on a re subroutine that strips out digits (\d+).

Trust me and put the following in the result box (the box beneath the Pre-Logic box):

strip_digits(!SitusFrm1!)

This last line calls on the function we just created and applies it to the contents in the address field of interest (in this case SitusFrm1).

Your Field Calculator dialog box should look something like this:

Once you click on OK, your results in your table should look like this:

Woohoo! We’re done!

Right?

Actually, we have one more step. If you look closely to the image above, there’s a leading space on the street names. Let’s get rid of it.

Step 3 – Clean Up Work using VBA

I know, I know. We want to eliminate clean up work, but this is probably the simplest cleanup task you’ll have to do today. Besides, it involves using good old VBA, a programming language on its way out. Might as well use this ancient language while we still can!

Right-click on the field name again and choose Field Calculator again. This time, click on the VB Script Parser. We don’t need the Codeblock for this, so you can click that off. Then click on the String for the function type to filter the function list. Since the space we want to get rid of is on the left leading side, you’ll want to double-click on the LTrim() Function. With your cursor in the parentheses, next double-click on the name of the field we want to trim (in this case, the same one we’re altering). It should look like this:

Click OK and you should have your street name with no leading space.

Viola! All done!

And there are no messy interim files to re-discover months from now and wonder if you should save them or not. 😉

Live long and python.


Looking for more informative and interesting content like this? Click here.

 

Pros and Cons to OpenStreetMap

OpenStreetMap is a map of the world, created by people like you and free to use under an open license.

That’s OpenStreetMap’s (OSM) opening lines to their free-to-anyone spatial database.

Yes. You read that right. Free spatial data!

If you’re looking for spatial data you can’t get from an authoritative source (i.e. city, county, state, or federal agency) for free, then OSM data may be worth using, especially if you’re working in a part of the world where getting local data would be difficult due to language barriers or just simply there is a lack of data.

But before you do so, let’s go through the pros and cons of this popular spatial data source.

Pros

  1. It’s free! Complete and truly free to use. You can download a whole world of data (approximately 803GB as of June 2017) by snagging the weekly copy of Planet.osm. Once on your computer, you are free to do with it what you like. Load it into a QGIS or ArcMap document and symbolize as you see fit. You can even edit the data. It is truly all yours.
  2. Related to the first point; the data comes with no strings attached. When you read my first point, you may have thought: “What’s the big deal? Google Maps, Bing Maps, and other web map services are free, too.” True, but Google Map data is *not* available for download. You can contribute edits to Google Maps, but that doesn’t mean Google will give data back to you.
  3. OSM often has richer data than other free map sources. For example, someone might have digitized all the hydrants in your city. Or all the best dog-friendly businesses. Or, if you are like me and ride your bike everywhere, all the barriers a bike-rider might encounter when getting around town.

Cons

  1. Data quality is spotty. Because OSM depends on volunteers, literally located all around the wold, data quality and consistency is haphazard. In places where a community of local mappers make a concerted effort to improve the database, you can get some stunningly detailed and accurate base maps (i.e. San Francisco area).  Where they don’t…well, let’s just say, you get what you pay for.
  2. Very little, if any, metadata is associated with OSM layers. Are the bus routes current? Who knows.
  3. Data is not authoritative, obviously. In the United States, OSM’s road network was derived from U.S. Census data. So on the national level, the road network is fairly accurate, complete, and authoritative. You can rest assured that the data went through some sort of data quality check with the U.S. Census and comes with a statement of accuracy. However, most other OSM layers are not. While someone may have digitized every tree in your town’s parks, there’s no way to tell if they accurately identified a walnut tree from a spruce.

In a nutshell, OSM data is great to have when you’ve got nothing else. Even when you do have access to something better, you may want to download the latest OSM layers. For example, I’ve used OSM’s building outline layer for the San Francisco Bay area, because, frankly, those industrious volunteers have already merged the nine SF Bay area county databases for me.

Another great reason to use OSM is that they support mapping efforts throughout the world. With their Humanitarian OpenStreetMap Team (HOT), they even try to help folks with efforts like their Malaria Elimination Campaign. While some tech companies claim to do no evil, HOT actually does.

Here’s a nifty info graphic of the Pros and Cons to OSM.


Did you like this blog post? Then please consider signing up to my email list. You’ll never get more than one email per month from me, but you will get my insights on the geospatial industry, GIS tips and tricks, and maybe even something a little fun.

SMART

We recently moved to Rohnert Park, a small (north of San Francisco) community of roughly 41,000 people.

I love it here. The town is bikeable and walkable. By bike, I can get to the local grocery store in less than 10 minutes and to my bank in less than 15.

And in less than 20, I can walk to Cotati’s newest SMART train station.

SMART

SMART Train at the Rohnert Park train station 6-29-2017

The Sonoma Marin Area Rail Transit (or SMART) started special service this summer, transporting folks from Santa Rosa to San Rafael. Though a start date for full, regular service has yet to be announced, if you were lucky, you might have managed to snag a ride during one of SMART’s free ride days.

On the first day SMART was open to the public, I wasn’t so lucky. But I still wanted to take the ride down to San Rafael. So, I decided to do it virtually – via a map.

Go ahead – click on it! You know you want to…

Of course, I had to add some spatial analysis and data.

Using both ArcMap Desktop and ArcGIS Online geoprocessing tools, I determined a (driveable) 5 minute service area around each currently-to-be-opened station and then added some basic demographic data to those areas (shown in green blobs, dark shades indicate a larger population served).

The summary tab at the bottom tallies the total potential population served (within 5 minutes of each station), the average household income of that population, and averages the population diversity index (the larger the number, the more likely two random folks are not of the same race).

Feel free to zoom in and explore the map. It’s interesting (to me, at least) to see the difference of service areas surrounding each station. For example, Rohnert Park’s 5 minute service area is smaller than Cotati’s (presumably because it’s faster to get around Cotati than Rohnert Park). This leads to a lower population served by Rohnert Park (9,873 people) versus Cotati (29,449 people) even though they are only 4 minutes (1.24 miles) apart.

Some thing tells me the Cotati train station might be more popular than Rohnert Park’s station.

Until we all get a chance to ride the train, what other insights might you find on this map?

 

Upcoming Fire Training

For all you fire bugs in California, there are three upcoming training sessions you may not want to miss. Take note:

2017 Klamath River Prescribed Fire Training Exchange (TREX)

Slated for October 2nd, 2017 to October 15th, 2017 in Orleans, California, this training will provide participants with experience on how to plan and implement controlled burns, protect communities from wildfires, and prepare for managing wildfire for resource benefits. Complete the application here.

Please note: you’ll have to pass the arduous pack test (3 miles, 45 minutes, 45 pounds). S-130, S-190, FEMA IS-100b, and FEMA IS-700a are also required prior to attending the training. However, these can be completed online. And don’t forget your full PPE!

Courtesy of Lenya Q. Davidson

NorCal TREX – 2017

Another similar training (with the same requirements as above) will be held October 17th, 2017 to October 28th, 2017. This one will be held throughout Northern California depending on personnel availability and the weather. The aim of this training will be to work together to share and build experience in prescribed fire practices, fire effects, and other conservation efforts affecting forest and grassland in northwestern California. NOTE: This training will be managed as an incident using the Incident Command System.

If interested, apply here.

Courtesy of Chris Ferner

Women-in-Fire Prescribed Fire Training Exchange (WTREX)

For all the awesome ladies in fire, there will be a women-centric training in Yosemite National Park from October 9th, 2017 to October 21st, 2017.

The training will be organized as an incident, using the Incident Command System. Participants will serve in qualified and trainee firefighting positions on a burn team and will assist with preparing, scouting, briefing, igniting, holding, mop-up, and patrol on numerous controlled burns in the area. The training team will also complete pre- and post-fire monitoring, train with equipment, practice fireline leadership skills, and learn about local fire ecology and fire management. The work will take place in diverse ecosystems in and around Yosemite National Park. The training will include field trips to areas burned in recent wildfires and to prescribed fire and fuels treatment project sites, as well as presentations from local scientists, land managers, and practitioners, and women who are leaders in various aspects of fire management. In addition, participants will practice preparing for media interviews.

Yes, all the same requirements for the normal TREX training apply to this one, too.

Male or females can apply here (though a higher portion of females will be selected from the pool of applicants).

Sadly, I will not be able to attend this year’s WTREX as I will be in New Zealand. But next year, I’ll be there!

All training sponsored by the Northern California Prescribed Fire Council, National Park Service, The Nature Conservancy, BLM, CAL FIRE, UC Cooperative Extension, California Fire Science Consortium, USFS, and FLN.

Infographics

If you hang out on LinkedIn or any other social media platform, you’ve seen them: infographics – an amalgamation of graphics and text that tells a story or delivers a witty message.

Here’s one you might mistaken for a map:

Boulder is for Fun Map/Infographic

This example definitely tends more towards an infographic rather than a map:

Benefits of Regulated Marijuana

As a GIS Specialist, I don’t often think of my maps as “infographics”, but, essentially, that’s what they are. If I am more careful with my map designs, I might be able to convey more information than just want’s on the map.

Recently, I needed a map to convey the evolution of national and local fire planning policies and plans. Rather than try to color-code temporal data on a single map, I created a timeline:

While simple and not as elegant as the previous examples, it conveys some dense information clearly.

Conference Season!

Most years, I usually watch the conference season from afar.

But this year I get to join in the fun. Below are the two I shall be attending. How about you?

CalGIS/LocationCon 2017

  • May 22 – 24, 2017
  • Oakland, California

This year’s theme is “Building Bridges” and is co-hosted by CalGIS (URISA) and LocationTech. This year’s conference will host a diverse schedule and attendees. The CalGIS community tend to represent GIS best practices in government, non-profit, education and consulting in California. The LocationTech community represents folk who develop advanced location aware technologies via collaboration with commercially-friendly open source software.

This conference will meld the “old” with the “new”. I’m excited about learning more. The three-days are packed with lots of talks, sessions, and trainings.

ESRI’s UC in San Diego

  • July 10 – 14, 2017
  • San Diego, California

ESRI’s User Conference (UC) is probably the largest gathering of GIS professionals anywhere in the United States. The last one I went to was over 10 years ago.

This year’s UC boasts over 300 hours of moderated sessions and over 450 hours of training.

Yikes!

I won’t be able to attend all five days of the conference, but I do intend to be there for the bulk of the week and learn as much as I can from my peers and industry professionals.

I’ll report back on each. Until then, enjoy all the opportunities to travel.

Nor Cal Rx Fire Council Annual Meeting – 2017

It’s that time of year again! Local firebugs are congregating in Petaluma, California.

March 7-8, 2017

Lucchesi Park Community Center, Petaluma, CA

I’ll be there, will you?

Check out the particulars here.

The organizers have a full agenda with some amazing speakers lined up. Carl Skinner (USFS, retired) will talk about historical fire regime shifts; Christine Olsen from the Oregon State University will clue us all in on what the public really think about smoke from Rx fires; and Sasha Berleman will highlight some local resources folks here in the North Bay can utilize for cooperative burns. There are more speakers, but those are the three I’m looking forward to hearing from.

Don’t forget to register here.

See you there!

Living Atlas

Late last year, I attended one of ESRI’s webinars about their relatively new data product: Living Atlas (click on the link to access the archived recording).

It be honest, I’m not exactly sure how it is different from all the other online content they provide. However, during the webinar, they promised more precise (and better) aerial imagery. They called it DigitalGlobe Basemap +Vivid.

I haven’t seen that data product – yet. I imagine it will be folded into their World Imagery layer?

Regardless, the webinar did remind me of all the great content available on ESRI ArcGIS Online. While some data layers are suspect and should be avoided, those from trusted sources with complete metadata provide easy access to information I would otherwise have to go hunting for from original sources.

If you are like me, often you just need a reference layer to orient yourself to a place or discover things nearby. Adding layers from ArcGIS Online to my desktop working document can help with that process.

To learn more about all the data ESRI offers online, click on the picture below.

What happened to 2016?

My last post was almost a year ago. What happened to 2016? It went by in a flash.

Regardless of how much I’ve neglected my website this past year, I thought I’d take this opportunity to update it with… stuff.

GIS/Software Advancements… or not

ESRI continues to lead in the desktop-GIS environment. As much as QGIS remains the go-to open source alternative, ArcMap along with ArcGIS Pro, remain the industry standard for government and private use. No matter how much folks would like to disrupt the status quo, that’s pretty much what we have to look forward to in 2017.

Web Mapping

Just about EVERYONE is jumping on the web mapping bandwagon. In addition to ESRI’s ArcGIS Online services, there are a host of other companies offering the same (and sometimes better) web mapping services. This past year I tried Carto (was CartoDB). You can check out my public profile here. I haven’t added very much; just a couple of boundary layers. I can say that the interface is very easy to use and the base maps as accurate as Google Maps. The free account version automatically makes all your data and maps public. One aspect of web mapping that I like is that many of the services have tools to help you interpret your data and not just symbolize it. It is definitely taking GIS to ‘the masses’.

Drones

To drone or not to drone… that was the big question for 2016. And while many folks have purchased a drone (they are nifty), they are not as ubiquitous as we all thought they would be by now. One of the biggest draw backs of owning a drone is what to do with all that data. Software to accurately process all the imagery can be expensive. While there are many applications where drone surveys are reducing costs, if you aren’t just going out to make a quick movie, gathering data via a drone can end up being more complicated and costly than you might expect.

And the commercial use of drones is still not without controversy. The new FAA rules (effective August 29th, 2016) mandates some limitations that might make gathering the super accurate data you were after a little more difficult.

Women in GIS

My colleagues and I continue to bring together women working in the field of GIS and geospatial industries. Recently, we’ve had an influx of interest to pursue our causes. Together, we came up with this cool graphic to show what Women in GIS means to us. Check it out:

As a fund-raising effort, we hope to have products emblazoned with this graphic in time for the 2017 conference season.

Cool Map

Throughout the year, I am exposed to some pretty nifty maps. Some are static maps, while others are interactive (in real life!).

Here’s one that I thought worth sharing:

An Atlas of Electricity

Until next time, enjoy the end-of-year festivities and may 2017 bring you contentment and success.

 

Women in GIS

Last year, I joined Women in GIS, an advocacy group geared towards helping women and minorities navigate their careers in geospatial industries.

Actually, it is more accurate to say that I got (willingly) sucked into an administrative role with the organization. I’m managing their website and I’ve conducted a few interviews. While we don’t have a lot of content on the site – yet – I have high hopes. Right now, a group of women are working hard to develop an organization that will be useful to their members and inspiring for an entire field.

However, we are just a bunch of volunteers. We don’t exactly have a mission other than to network and support each other. Kind of like a big, global therapy group. Sometimes, I wonder if Women in GIS is even needed.

So…why are we form a group?

Good question

Like many tech oriented fields, women tend to get marginalized where men have traditionally dominated.

GIS – Geographic Information Systems – is a field born from the proliferation of computers. In 1962, Roger Tomlinson, often touted as the father of GIS, began the first steps of cataloging natural resources in Canada. In doing so, he created the first GIS.

It is important to note that GIS has a father, but not a mother. Why is that?

In the 1960s, though women were starting to make strides, only 38% of us went to work and we traditionally occupied positions less cerebral than men. Many of us working at that time just didn’t hold the kind of jobs that would put us in place to do the kind of work to develop an entirely new computer system. It wasn’t just that we weren’t working, but we weren’t professors, surveyors, or engineers.

By 2004, women accounted for 60% of the labor market, and we started holding more tech-oriented, managerial, and professional positions.

Woohoo! Good for us, but last year, in California, Governor Brown did something astounding. He passed a law prohibiting employers from forbidding their workers to compare wages and the law also required equal pay for similar work regardless of gender.

Let’s back up a bit and think about that.

In the year 2015, just about 100 years after the women’s suffrage movement started in United States, a governor of one of the largest states in said union had to pass a law to ensure women were compensated equally for their work.

We should be embarrassed. After so many years of fighting for equal rights in the work force, women shouldn’t still have to be protected by a law. It should be standard business practice.

But it is not just about equal pay. It’s also about who is doing the work. Women remain under-represented in the information technology industry. Just take a look at this handy graphic put together by National Center for Women and Information Technology:

btn_04032015_webWhile women make up more than 50% of the educated work force, we hold less than 30% of the jobs in computing. The numbers get worse the higher you go up the corporate ladder, with only 6% of Chief Information Officer positions being held by women.

As an Hispanic woman, I’m especially appalled by the last statistic on this graphic: Only 1% of the computing workforce are Hispanic women.

One percent!

There is no reason other than sexism and racism (either overt or unintentional) for these numbers to exist. While I like to think we are all playing on the same field, with the same resources, and similar connections, we are not.

One is a lonely number

So, why form a group advocating for women and minority voices in GIS?

Because if we don’t, our voices will not be heard. Like other tech-oriented industries, GIS relies heavily on computers and it is easy to not see us. Anyone working in GIS is somewhat annoymous. Though metadata helps to attribute data to individual GIS professionals, industry maps rarely attributed to their creator. It is not easy for a girl, trying to figure out what she wants to do with her life, to see us – to see women in GIS and geospatial roles. They see women as models, actresses, waitresses, nurses, but when are they going to see a female GIS professional? Unless we go out and actively recruit young women and minorities, our industry will remain white, male-dominated.

So, let’s stand together and be seen. Join me in bringing the world to every girl’s attention.