Azavea Atlas

Maps, geography and the web

Celebrate Geography Week 2015 in Philadelphia!

Geography Week

Geography Awareness Week, November 15th-21st 2015, features activities and events all over the world related to geography and mapping.  Geography Awareness Week was originally established by National Geographic to promote to Geography in American education and to excite people about geography as both a discipline and a part of everyday life.

In Philadelphia, alone, there are numerous events relating to Geography, urbanism and mapping during this special week.  Below is a round up!

Monday, November 16th

Open Streets Film Screening and Panel Discussion

Celebrate the potential of open streets with this film screening and panel discussion. Since the Pope visit in Philadelphia which inadvertently created a haven for pedestrians and cyclists, Open Streets Philly is building the public and political support for future open streets events. Beer provided. $5 registration.

Tuesday, November 17th

City of Philadelphia presents: Map, Measure, Manage 2015

Join us for a showcase of place-based apps used by Philadelphia City government for data-driven decision making. Learn how your city government uses geodata to analyze and address problems like vacant buildings.  Drop-in for Q&A with city staff from Office of Innovation, Streets, L+I, Philadelphia Water, etc.  See demos of geo-tools used to improve city operations and services

Wednesday, November 18th

Penn GIS Day: The Intersection of Geography, Real Estate, and Civil Rights

Penn GIS Day, held in conjunction with the National GIS Day celebration, focuses on real-world applications and innovations stemming from uses of Geographic Information Systems. The forum examines the use of GIS both at Penn and more broadly, offering an opportunity for professional and academic interaction. This year, speakers will discuss how the role of spatial analytics is advancing in the fields of real estate and housing. Also consider registering for the Technical Workshop from 2:00-4:00 pm.

Temple GIS Day: Making a difference in communities with GIS and spatial analysis.

Temple University is pleased to announce that we will once again be hosting geographic information systems (GIS) Day for 2015.  GIS Day provides an international forum for users of geographic information systems (GIS) technology to demonstrate real-world applications that are making a difference in our society.

This FREE EVENT will include lightning talks by many groups at Temple University and around Philadelphia on how GIS is used to make a difference, an employment workshop featuring Temple alumni working in the GIS field, and a keynote presentation by John Duchneskie (Assistant Managing  Editor/ Design and Graphics, The  Philadelphia Inquirer).

Drexel GIS Day

Drexel University is holding a GIS Day Event this year with the sponsorship of the Drexel University GIS Users Group, School of Public Health, and University Libraries. The day’s events will include a morning of 15 minute presentations on GIS use in a variety of disciplines ranging from public health, ecology, computing and informatics, and environmental science. The presentations are geared toward a public audience and meant to inform the campus community of the potential uses of desktop and web GIS applications. The event is scheduled for 10 am – noon, Wednesday, November 18 in Nesbitt Hall.

Philadelphia Map Society Explores the Maps of Philadelphia Parks and Recreation

Philadelphia Map Society appreciates the opportunity to explore maps in the Philadelphia Parks and Recreation Archive at 1515 Arch St, 10th Floor to be selected by Rob Armstrong, Preservation and Capital Projects Manager, and Alina Josan, Archives Specialist.  A stunning 12 foot map completed in 1909 with the express purpose of placing Pennypack Park on the City Plan will be laid out for intense scrutiny. 5:30-7:00pm.

Thursday, November 19th

Urban Geek Drinks

Cap off Geography Week with a beer and a pretzel at this months Urban Geek Drinks at Frankford Hall. Urban Geek Drinks is a monthly happy hour for a diverse group of civic do-gooders in Philadelphia. It’s an opportunity for you to connect with other people who care deeply about Philadelphia and are working incredibly hard to make this city really great.
Can’t make it to any events this week? Consider joining GeoPhilly, a meetup for map enthusiasts, the Philadelphia Map Society or MaptimePHL, a meetup for folks that want to build their mapping skills in a collaborative, supportive environment.

Editing Your Address Detail Page in the Nonprofit Starter Pack

Cicero’s integration with the Salesforce Nonprofit Starter Pack last November allowed users to verify their addresses and match them to legislative districts. This tool has been extremely helpful for many nonprofits who want to improve their postage automation rate ahead of a large mailing, or match their constituents to legislative districts in order to advance an advocacy campaign.

cicero hearts Salesforce


While Cicero will append Congressional, State Upper, and State Lower legislative districts to address records, your organization may not have a use for all three. Below, we will walk you through the steps of editing your address layout page so you can get exactly what you need out of the Cicero integration:

1) After logging in to your Nonprofit Starter Pack account, navigate to “Setup” at the top of the screen

2) On the left hand side scroll down to “Create” (under the Build Heading) and then choose “Objects” in the menu underneath

3) Select the label titled “Address”

A snapshot of the Custom Objects page in the Nonprofit Starter Pack

4) Scroll down to “Page Layouts” or use the shortcut at the top

5) Click “Edit” on the Page Layout you’d like to modify

A snapshot of how to find and edit your custom page layouts in the Nonprofit Starter Pack

6) Using the Address Layout Widget at the top, you can choose to add or remove the Congressional District, State Upper District, or State Lower District fields from your Address Detail page

A snapshot of the Address Layout Widget in the Nonprofit Starter Pack

If you’d like to add a field, simply drag and drop into your Address Detail. To remove a field, mouse over it in the Address Detail example and click the “remove” button.

A snapshot of the Address Detail editor in the Nonprofit Starter Pack

Warning! Be careful not to remove other fields that are essential to the address itself, such as Mailing Street, City, State, Zip, and Country. Turning on Address Verification with Cicero also automatically appends a “Verified” field to your detail page. You’ll want to leave that there to indicate whether or not a new address has been verified.

7) Once you’ve added or removed your desired Legislative District fields, you can change their location on the Address Detail page just by clicking and dragging

A snapshot of how to click and drag to rearrange your address detail page in the Nonprofit Starter Pack

8) When you’re done, either choose to preview your new Address Detail page at the top, or click Save

It’s that easy! And you shouldn’t feel bad about adding or removing legislative districts, because regardless of how many districts you match to an address, it still only costs one credit. Cicero offers a 10% discount on all credit purchases for nonprofits, and our partnership with Techsoup allows 5,000 credits for a small $30 donation. Have questions about pricing? Drop us a line or tweet us @CiceroAPI. We’re happy to help!


Announcing the FOSS4G-NA Conference Call for Proposals

Logo for 2016 Foss4gNA conference in Raleigh, NC

My colleague Rob Emanuele and myself are very proud to be involved in the organization of the 2016 FOSS4G-NA Conference in Raleigh, NC. Rob will be serving as Program Chair again this year and I will be serving as the Committee Chair. You can meet the rest of the organizing team here.  

If you’ve never been to a FOSS4G conference, they are a fun and informative gathering of the smart and dedicated people that make up the FOSS4G (Free and Open Source for GeoSpatial) community. At this year’s FOSS4G NA, you will also have the opportunity to learn skills that will help you in your work, hobby, or whatever your FOSS4G passion might be.  Everyone is welcome! If you are new to FOSS4G, that is even better. We have many beginner sessions, hands on workshops, and a mentor program to help you feel comfortable and get started.

This is truly a hands-on, collaborative conference and we want to invite you to share your knowledge and talent by submitting a proposal for the conference!  If you are already part of the community, we are calling on you to share your stories, update us about the things you have learned, tell us about your amazing work, and meet with old and new friends from the community.

We’d like to extend a special invitation to traditionally underrepresented groups in FOSS4G to submit a proposal. We are looking to build a great program that reflects all of the brilliant people and work that make up our community, but that can only happen if you submit your proposals! The success of the conference is directly dependent on the quality and diversity of the submissions, so please help us make FOSS4G NA 2016 the best possible conference it can be.

Postcard reading "Greetings from North Carolina", source: conference will take place at the Raleigh Convention Center, which is a great space with a number of rooms that we are excited to fill with amazing talks. In the Call For Proposals, we have laid out our topics of interest, but if you have something great to talk about that doesn’t fit to that mold, please still submit!

The conference runs from May 2nd to the 5th in the heart of downtown Raleigh. There are many restaurants, shops, bars, and other points of interest located within easy walking distance of the convention center and hotel. You can explore the city in this 1872 birds-eye view map of Raleigh

Please get your proposals in before January 22 for a chance to be selected as part of our early bird program selection. The final deadline is February 8th. Visit the link above and submit your proposal today. Good luck!

Hope to see you in Raleigh!

Calculating Daytime Population at the Census Tract Level for the Entire US

What is daytime population?

Photo of Chicago Loop elevated trains

Chicago’s Loop

Despite official estimates, many parts of the US have populations that vary wildly from day to night. Think of the the repeating cycle of sleepy suburbs emptying onto highways at 8AM every morning and the city centers emptying back out at 5. There are really two different population measurements then–nighttime population consisting of permanent residents (where people live), and daytime population consisting of those who spend all day in a given location for work. This is an important difference, especially for organizations that want to find out where people are during the day, rather than where they call home.

While the Census Bureau provides estimates of daytime population at the county level through the American Fact Finder, anything smaller requires manual processing. County level daytime population data isn’t all that helpful, either. For instance, Cook County encompasses the entire city of Chicago and its nearest suburbs. This large geographic area obscures movement patterns of the population within Chicago.

Calculating Daytime Population at the Census Tract Level

In 2014, Summer of Maps alum Tim St. Onge detailed how he calculated daytime population for the city of New Haven, Connectictut. We’ll be using the same principles with the Census Transportation Planning Product’s Census Tract Flows database. Because the data file is too large for a standard spreadsheet program, we’ll be working in a PostGRES database. Check out our previous blog on getting started with PostGRES / PostGIS using the OpenGeoSuite.

The Census Bureau has two recommended methods for calculating daytime population. We’ll use the second method because it’s a little more simple:

Daytime Population = Total Resident Population + Total Workers Working in Area – Total Workers Living in Area

Step 1: Download Census Transportation Planning Product’s (CTTP) Census Tract Flows (Total Workers Commuting In, ie Daytime Workers)

  1. Download the 151mb Access Database of CTTP Census Tract Flows
  2. Export the data from the Access Database as a CSV. The resulting database should have around 4 million rows (see why we don’t use Excel?)
  3. Import the data into PostGRES. I used the following code to create and format an empty table with fields for each column in the CSV:
   CREATE TABLE work_pop 
    (res_st_fips integer, 
    res_cty_fips integer, 
    res_tract_fips integer, 
    work_state_fips integer, 
    work_cty_fips integer, 
    work_tract_fips integer, 
    est integer, 
    moe double precision);

I then used this code in the terminal to copy the CSV to the database (requires PSQL add-on which usually comes with OpenGeoSuite): sql COPY work_pop FROM '/Users/user/ACSdata/Tract-flows.txt' DELIMITER ',' CSV;

Step 2: Combine the Daytime Worker Counts

The CTTP data set stores the uniqe GEOIDs for each tract, county, and state as numbers. This means that any leading zeros necessary to create a valid 11-digit GEOID field that will match downloaded data from the ACS have been stripped out. We need to add them back in. We’ll do this by converting the numbers to text, adding leading zeroes, and concatenating the state, county, and tract GEOID fields together. Learn more about how to construct an 11-digit GEOID for tracts here.

This Census Tract Flows database accounts for every single unique instance of tract-to-tract commutes. This is why the database has 4 million rows, despite the fact that there are only about 74,000 Census Tracts in the US. We’ll need to sum the counts of all the workers flowing into a given tract as a work destination. For example, tract 839100 in Chicago receives commuters from a whopping 2,202 other tracts.

  1. Run the query below on the PostGRES table you created in Step 1.3 and and export the results as a new table to save the result. The query will convert the numeric FIPS columns to text, add the leading zeros where necessary, and concatenate them together into an 11-digit geoid2wk(for workers) and geoid2rs (for residents), SUM the counts of workers heading to each destination tract, and then group the tracts by their common id, geoid2wk.
        strstwk||strctywk||strtractwk as geoid2wk,
        strstrs||strctyrs||strtractrs as geoid2rs
            to_char(res_st_fips,'FM00') as strstrs,
            to_char(res_cty_fips,'FM000') as strctyrs,
            to_char(res_tract_fips,'FM000000') as strtractrs,
            to_char(work_state_fips,'FM00') as strstwk,
            to_char(work_cty_fips,'FM000') as strctywk,
            to_char(work_tract_fips,'FM000000') as strtractwk 
        FROM work_pop) as sub
    ) as sub2
    GROUP by geoid2wk

    This will result in a table that has about 74K records. We’ll be using the resulting table with additional ACS data to calculate daytime population.

Step 3: Download Census Bureau Data (Total Workers Living in Area, Total Resident Population)

  1. Go to
  2. Choose “Download Center”, hit Next
  3. Choose the top option, “I know the dataset or table(s) that I want to download”, hit Next
  4. Select American Community Survey from the dropdown, select “2013 ACS 5-Year Estimates” from the list. Hit Next
  5. Select “Census Tract 140″ from the geography dropdown, select “All Census Tracts within United States” in the selection box, hit “Add to Selections”, hit Next
  6. In the search box type: B08007, hit Go, click the checkmark. This will download Total Workers Living in Area.
  7. Click download, hit OK.
  8. Repeat steps 1-7 to get Total Resident Population, but search for dataset B01003 in Step 6 instead.

Step 4: Join The Data Tables

  1. Load the ACS tables from Step 3 into your database or a GIS so you can get to work. Now that we’ve pared down the daytime workers data to a more manageable 74,000 records, we can join the tables in a spreadsheet program or a GIS program. Otherwise, the next steps assume you’re using PostGRES.
  2. Add all the data from both ACS tables (Total Workers Living in Area, Total Resident Population) and the Daytime Worker table into the same table. Join the tables based on their matching 11-digit GEOID columns. Adapt the following PostGRES query to your specific tables:
            UPDATE censustracts
            SET destinationcolumn = (
              SELECT sourcecolumn
              FROM sourcecensusdata
              WHERE geoid10 = censustracts.geoid10)
  1. Once all three fields (Total Daytime Workers, Total Resident Population, Total Workers Living in Area) are in the same table add a new column to store the result of calculation. Maybe something like “poptotal_day”.
  2. Perform the daytime population calculation: Daytime Population (Census Bureau Method 2) = Total Resident Population + total workers – total resident workers

Here’s an example PostGRES query:

UPDATE censustracts
SET poptotal_day = (
        population_total + workers_daytime - workers_resident

If everything has gone well, the poptotal_day column should now have an estimate of the daytime population for every single census tract in the united states! We’re just missing one thing: spatial data! We’ve been using Census Tracts and Census Bureau estimate tables this whole time, but we have no way to display the data on a map without using spatial representations of the Census Tracts.

Download US Census Tract Shapefiles

To display the results on a map, you need Census Tract spatial data. Unfortunately, geographic data for all Census Tracts in the entire country are difficult to find in bulk. The Census Bureau offers TIGER shapefile downloads, but they’re broken down by state, so a user would have to download a file for each state and then merge them afterwards. Assuming you can find the data or combine the files from the ACS, you can join the calculated data sets to geographic shapefiles or geoJSONs with SQL or a GIS program like QGIS or ArcGIS. The join should be based on the matching 11-character GEOIDs in the calculated daytime population, and the Census Tracts shapefiles.

OR, Just Download the Finished Project from Our CartoDB Account:

Visit this link to download both the finished daytime population data for 2013 and the geographic files for all Census Tracts in the US, and check out the results below:


Wrapping up NACIS 2015

Last week, the Data Analytics team attended the North American Cartographic Information Society (NACIS) conference in beautifulMilwaukee_Road_train_depot_Minneapolis Minneapolis. The NACIS conference, in its 36th year, is an annual gathering of cartography professionals, students, and other map-minded individuals. Over 325 attendees, this year gathered in the historic Milwaukee Road Depot, a truly unique venue.

The conference started out with Practical Cartography Day (PCD), which focuses on the real-world uses of cartography and analysis in the industry and academia. One of the interesting tools announced on PCD was Dropchop. Developed by Sam Matthews of Code for America and powered by Mapbox.js and Turf.js, it’s a browser-based application that enables users to simply drag-and-drop shapefiles into the browser and execute spatial analysis tools without the need for desktop GIS. Dropchop is meant to be a data-first tool, as opposed to operation-first (traditionally most desktop GIS software) in that the spatial analysis operations are contextually based what can be done with the data.

Here is just a sampling of some of the many great presentations:

  • Daniel Huffman presented the methodology and design considerations for his linear map of the Lake Michigan shoreline.
  • Alan McConchie talked about Stamen’s work to digitize the Atlas of the Historical Geography of the United States, including some valuable advice about mapping where boundaries change over time. Stamen used hexagonal bins to aggregate county populations to show change over time where county boundaries frequently changed in the 19th century. The project is on Github and will be launched soon.
  • Amy Lee Watson of Mapbox talked about her experience as a designer at Mapbox, rather than cartographer and gave some great designs tips. She also talked about her experience working on the Alltrails project and designing the awesome blueprint Mapbox basemap.
  • Code for America Fellow Patrick Hammons gave a Halloween-themed presentation about his experience making maps for nonprofits and community organizations with some good lessons learned.globe_yoga_ball
  • Azavea’s own Sarah Cordivano took part in a hiring and career panel, and you can find all the questions and notes here.
  • And then there’s this awesome globe yoga ball created by Hans van der Maarel of Red Geographics.

In addition to the conference every year, the all-volunteer staff at NACIS also publish the Atlas of Design, available for $35. All in all, another fun and informative gathering of map-lovers, NACIS continues to get better every year.