Is there a free or cheap way to code a large number of addresses and return or append the census tract and block data?
There are a number of ways to geocode an address and get the lat long, but I really need to get the census tract and block data.
Ok Ben, here are my assumptions:
1) You've already got your data (I had some address points in a shapefile, and I downloaded census tract and census block shapefiles for Missouri).
2) You've already geocoded your address points and you're comfortable projecting the data.
Here are some install notes if you don't have these software: How to install PostGREs with PostGIS support. (By BostonGIS. Please don't take offense to their title, I just think it's the best how-to out there.) Also, here's one, two, and three sites describing how to install GDAL/OGR with Python bindings.
Caveat: Before performing the actual analysis (i.e. the
With those givens, this is how I appended tract and block attriutes to some address points data using PostGIS:
First I used
Import addresses using ogr2ogr:
Import census tracts (Missouri) using ogr2ogr: The
Import blocks data (Missouri): This one took awhile. In fact, my computer kept crashing and I had to put a fan on it! Oh also,
Once the data imports are accomplished, launch PgAdmin III (the PostGREs GUI), browse into your database and throw some quick maintenance commands so that PostGREsql will run faster using these new data:
Next, I was curious how many raw address points I imported, so I did a quick
In the next phase, I created two new tables, gradually adding the tracts attributes, and then the blocks attributes, to my original address points table. As you'll see, the PostGIS
Note! For brevity, I'm only taking a handful of fields from each table. You'll probably want almost everything. I say almost because because you'll need to omit the
(P.S. I did some snooping around here while figuring this out: http://postgis.refractions.net/docs/ch04.html#id2628637)
Create a new table of address points with tracts attributes: Note I'm prefixing each output column with a hint disclosing which table it started in (I'll explain why below).
Maintain the table so PostGREs continues to run smoothly:
Now I had two questions..
Did the ST_Contains actually work? ..and.. Does the number of addresses returned make sense given the data inputs I used?
I was able to answer both using the same query:
A quick reflection on the losses: First, I checked in ArcGIS (you could also do this in QGIS) and it returned the same count. So, why the difference? First, some addresses fell outside of Missouri, and I only compared against a Missouri tracts polygon. Second, on closer analysis, it seems there were some examples of bad digitizing in the addresses data. Specifically, many of the points not caught by
Moving on, the next step was appending the address/tracts table with attributes from the blocks data. Similarly, I did this by creating a new table, once again prefixing each output field to indicate the table it came from (the prefixing is quite important you'll see):
Of course, maintain the table:
The reason I prefixed each output field was because if I didn't, some fields would have the same names, and it would be impossible to distinguish them from one another in the final product (also.. PostGREs may have complained midway into this, but since I was renaming, I didn't give it the chance). Consider, for instance, the following two fields from both steps, above. You can see why I renamed them..
Now that we have an addresses with tracts and blocks dataset, dwe still have the same number of points?
Yes, we do! If you want, you can go ahead and delete the first table we created,
As a last action, you may want to export your data from PostGREs into an ESRI shapefile so that you can view it with other programs, like ArcGIS (of note, QGIS can read the PostGIS data without issue). If you're interested, here's how you could perform the conversion using ogr2ogr:
Finally, when you run this command, you'll likely get some warnings like this:
Warning 6: Normalized/laundered field name: 'tr_statefp10' to 'tr_statefp'
This just means OGR had to shorten that field name, because the field name in a shapefile can only be so long.
Of course, this is only one of many ways to accomplish this job. I hope it's helpful for you. I'm apologize if I lost the plot somewhere!
Best of luck. Elijah
The FCC has an API: http://www.fcc.gov/developer/census-block-conversions-api
We're looking for long answers that provide some explanation and context. Don't just give a one-line answer: please explain why you're recommending it as a solution. Answers that don't explain anything will be deleted. See Good Subjective, Bad Subjective for more information.