The City of Fresno is required by Municipal Code and State Law to keep the population of the council districts evenly balanced. At the decennial census, the area and boundaries of each district are checked against the new population count and, if necessary, changes are made to bring the populations back in to balance. The 2010 Census population count for the City of Fresno was 494,665 people. Therefore the ideal population of each of the seven districts will be 494,665 / 7, or about 70,666 people. This process was most recently completed by the City Council in response to the 2010 Census. You can view the Ordinance adopting the new Council District boundaries on the Fresno City Clerk’s Web site.

This exercise will verify the new council district boundaries by mapping the 2010 Census population counts of each district. To do this, we will use Free Open Source GIS tools. Quantum GIS (QGIS), a Free Open Source GIS desktop tool that can display ESRI Shapefiles and perform complex spatial analysis. Download and install QGIS if necessary. We will also use ogr2ogr to project the Census shape files. I’m on OSX, so I downloaded the latest (1.9) framework from KyngChaos.

City of Fresno maintains a Web site of publicly available GIS data. In addition to downloadable shape files, you can browse an online interactive map of City data. Click on Layers/Legend and turn on the City Council Districts Layer to see the current City council districts based on the Federal 2010 Census. The Official Council District Map is also available as a PDF file in the City of Fresno Planning Department Map Gallery. The PDF file is interesting because it shows the official color for each district, and the legend contains the actual population count for each district based on the 2010 Census.

The Federal Census provides a wealth of public information. Tabular data for the most recent (2010) census can be accessed through the American FactFinder. To use this data in GIS, we’ll need to download the appropriate Census shape files and join the tabular data to the spatial location in QGIS.

Where to get the data we need:

• City of Fresno Council Districts
• American FactFinder for Population table in CSV format for all block in the City of Fresno
• Census 2010 shapefiles for shape file of all blocks in Fresno County, California

After installing our tools and acquiring the appropriate data, we will follow these steps to find the population of each City of Fresno Council district:

• Project the Census Blocks layer to State Plane, and add to the map
• Add the Council Districts layer to our map
• Join the population data to the Census Blocks layer
• Join the Blocks to the Districts
• Dissolve the Blocks by District, and sum the population

Projecting the census data using ogr2ogr is necessary because local GIS data from the City of Fresno is projected into State Plane (the EPSG code is 2228 - NAD_1983_StatePlane_California_IV_FIPS_0404_Feet) and the Census data is not projected. Census data is GCS NAD 83 (EPSG 4269 - GCS_North_American_1983). Opening both of these shape files in a  QGIS project window will not work–they will not overlap correctly.

We will use ogr2ogr to project the Census shape file to State Plane. Here is the ogr2ogr command we will use:

ogr2ogr  -s_srs EPSG:4269 -t_srs EPSG:2228 outfile.shp infile.shp

We should now have both shape files displayed in QGIS. Zoom to the extents of the council districts shape file. In order to add the population data as an attribute of the census blocks shape file, add the CSV file to the map and create a join. After successfully joining the data, only the blocks inside the City of Fresno will contain information (because this is the only population information we downloaded). Therefore we can export the blocks with population data to a new shape file in order to create a shape file for only the City of Fresno, comprised of blocks attributed with population data.

In order to attribute each of the blocks with the council district, create a spatial join to your new blocks layer with the council districts layer. The result of this will be a new shape file of census blocks within the city of Fresno, attributed with the population count for the block and the council district it is in.

The final step is to dissolve the blocks back to council districts. However in QGIS when we do this we lose the other attribute information. Ideally, we’d be able to sum the population of the blocks as we dissolve on the council district field, but this is not possible in QGIS. One solution, described on the QGIS user mailing list, is to dissolve to districts in a new shape file, then import both shape file tables into a database, where grouping of population data by district is accomplished.

A slightly easier method, described on another mailing list, is to copy all the rows from the attribute table in QGIS and paste them into Excel. You can then filter on each district, and sum the population.

That element of tragedy which lies in the very fact of frequency, has not yet wrought itself into the coarse emotion of mankind; and perhaps our frames could hardly bear much of it. If we had a keen vision and feeling of all ordinary human life, it would be like hearing the grass grow and the squirrel’s heart beat, and we should die of that roar which lies on the other side of silence. As it is, the quickest of us walk about well wadded with stupidity.

–George Eliot, as quoted in Why Read George Eliot?

In his booklet “Road Vogue” on bicycling safely, Wayne Pein quotes Sir Arthur Conan Doyal from an article published in Scientific America in 1896. Now, when you mix the author of Sherlock Holmes with science and cycling, I get all happy. The quote itself is worth repeating here:

“When the spirits are low, when the day appears dark, whenwork becomes monotonous, when hope hardly seems worth having, just mount a bicycle and go out for a spin down the road, without thought on anything but the ride you are taking.” Sir Arthur Conan Doyle, in an 1896 article for Scientific America.

Based on a Google Spreadsheet.

I almost didn’t get up in time, but I managed to out the door with just enough time to make it to the dentist by bicycle. When I got here, I realized it would have saved me no more than 10 or 12 minutes to come by car.

The latest from Gadzooks, aka godzooks:

View Larger Map

Red Rackham’s Treasure

I’ve had this idea in the back of my mind for the longest time. I think downtown Fresno is actually pretty cool. There are lots of interesting buildings and fun places to explore. Wouldn’t it be great to have a knowledgeable docent guide you around town, stopping at interesting places to talk about the history or significance of a certain building? Of course, the best way to do this is by bike, because there’s no way you can cover enough ground on foot. And doing this by car would be a comical exercise in frustration; hop in the car, drive, park, hop out, look, repeat.

This year I am going to develop bicycle tours for three specific events:

1. In May, we’ll have a fun tour of downtown, highlighting Armenian history.
2. In September, the second annual Tour de Fresno will include a fun tour of Old Fig Garden.
3. And in October, the City of Fresno is celebrating it’s anniversary with many fun activities, including a bike tour with official docents.

Next week I’m meeting with representatives from City of Fresno’s Traffic Engineering, Parks and Recreation, and Historic Preservation departments. I’ve already reserved a domain name for Cycle Fresno Urban Bicycle Tours, and when I meet with the City I intend to present a proposal for how this all can work. So stay tuned!

I was recently asked how a user could publish dynamic content to an otherwise static Web page. The user wanted to show a list of meeting minutes on his Web page that automatically updates from a remote source. My answer was to use Yahoo! Pipes to create an embeddable widget. Once the widget code is created and inserted into the static web page, it will remain automatically updated whenever the source of the pipe changes. So far, we have created two pipes. One of them parses a Blogger RSS feed and presents relevant items in a list view. The content expert can update the blog, and the changes are reflected in the Web site without requiring a full author-edit-publish cycle. Here is the process:

1. New information is published on the blog.
2. When the pipe is next run, this change is detected via its feed.
3. The pipe badge is embedded in the target Web page, so it is run whenever the page is loaded.