From: Michael Orlitzky Date: Sat, 3 Oct 2009 17:26:14 +0000 (-0400) Subject: Renamed the "tiger" table to "tiger_blocks" in preparation for the addition of the... X-Git-Url: http://gitweb.michael.orlitzky.com/?p=dead%2Fcensus-tools.git;a=commitdiff_plain;h=0e8428f00f565a3e1791e19098ab59442972fa09 Renamed the "tiger" table to "tiger_blocks" in preparation for the addition of the TIGER road data. Shortened the name of the "tiger_blkidfp00" method/column to just "blkidfp00". --- diff --git a/bin/pg2kml b/bin/pg2kml index 1fddabe..68d5e6f 100755 --- a/bin/pg2kml +++ b/bin/pg2kml @@ -128,9 +128,9 @@ for style in red_styles: query = """ -SELECT tiger.blkidfp00, population_density, AsKML(the_geom) as geom - FROM (sf1_blocks INNER JOIN tiger - ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00); +SELECT tiger_blocks.blkidfp00, population_density, AsKML(the_geom) as geom + FROM (sf1_blocks INNER JOIN tiger_blocks + ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00); """ cursor.execute(query) diff --git a/bin/sf1blocks2sql b/bin/sf1blocks2sql index 80c516c..42ea739 100755 --- a/bin/sf1blocks2sql +++ b/bin/sf1blocks2sql @@ -42,7 +42,7 @@ INSERT INTO %s (state, arealand, areawatr, total_area, - tiger_blkidfp00, + blkidfp00, pop100, population_density) @@ -60,6 +60,6 @@ for b in blocks: b.arealand, b.areawatr, b.total_area(), - b.tiger_blkidfp00(), + b.blkidfp00(), b.pop100, b.population_density()) diff --git a/makefile b/makefile index 36543d4..155910a 100644 --- a/makefile +++ b/makefile @@ -77,7 +77,7 @@ db: data $(PG_BINDIR)/shp2pgsql -I \ -s $(TIGER_SRID) \ data/census2000/maryland/block/tl_2009_24_tabblock00.shp \ - tiger \ + tiger_blocks \ | psql -U $(DB_USER) -d $(DB_NAME) psql -d $(DB_NAME) \ diff --git a/sql/create-sf1_blocks-table.sql b/sql/create-sf1_blocks-table.sql index 71d87b5..80f3c79 100644 --- a/sql/create-sf1_blocks-table.sql +++ b/sql/create-sf1_blocks-table.sql @@ -3,8 +3,8 @@ foreign key column before we can create the foreign key constraint. */ -CREATE UNIQUE INDEX idx_tiger_blkidfp00_unique - ON tiger (blkidfp00); +CREATE UNIQUE INDEX idx_tiger_blocks_blkidfp00_unique + ON tiger_blocks (blkidfp00); CREATE TABLE sf1_blocks ( id SERIAL PRIMARY KEY, @@ -14,11 +14,11 @@ CREATE TABLE sf1_blocks ( block varchar(4) NOT NULL, arealand double precision NOT NULL, areawatr double precision NOT NULL, - tiger_blkidfp00 varchar(15) NOT NULL REFERENCES tiger (blkidfp00), + blkidfp00 varchar(15) NOT NULL REFERENCES tiger_blocks (blkidfp00), pop100 integer NOT NULL, total_area double precision NOT NULL, population_density double precision NOT NULL ); -CREATE UNIQUE INDEX idx_sf1_blocks_tiger_blkidfp00_unique - ON sf1_blocks (tiger_blkidfp00); +CREATE UNIQUE INDEX idx_sf1_blocks_blkidfp00_unique + ON sf1_blocks (blkidfp00); diff --git a/src/Census.py b/src/Census.py index a69ff58..da9283d 100644 --- a/src/Census.py +++ b/src/Census.py @@ -30,9 +30,9 @@ class Database: query = """ SELECT population_density - FROM (sf1_blocks INNER JOIN tiger - ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00) - WHERE ST_Contains(tiger.the_geom, + FROM (sf1_blocks INNER JOIN tiger_blocks + ON sf1_blocks.blkidfp00=tiger_blocks.blkidfp00) + WHERE ST_Contains(tiger_blocks.the_geom, ST_SetSRID(ST_Point(%.6f, %.6f), %d)); """ @@ -71,8 +71,8 @@ class Database: # query = """ SELECT SUM(sf1_blocks.pop100 * - ( ST_Area(ST_Intersection(%s, tiger.the_geom)) - / ST_Area(tiger.the_geom) ) + ( ST_Area(ST_Intersection(%s, tiger_blocks.the_geom)) + / ST_Area(tiger_blocks.the_geom) ) ) AS covered_population """ % geometric_object sql_params = (well_known_text, self.srid) @@ -81,15 +81,15 @@ class Database: # Join our two block tables, so that we have both the demographic # and geometric data. query += """ - FROM (sf1_blocks INNER JOIN tiger - ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00) + FROM (sf1_blocks INNER JOIN tiger_blocks + ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00) """ # We only need to calculate the covered population for the blocks # that actually intersect our object. query += """ - WHERE (ST_Intersects(%s, tiger.the_geom)) + WHERE (ST_Intersects(%s, tiger_blocks.the_geom)) """ % geometric_object # geometric_object hasn't been substituted yet, so we need # to add the sql_params twice. @@ -121,9 +121,9 @@ class Database: cursor = self.connection.cursor() query = """ - SELECT ST_AsText(tiger.the_geom) - FROM tiger - WHERE tiger.blkidfp00 = %s; + SELECT ST_AsText(tiger_blocks.the_geom) + FROM tiger_blocks + WHERE tiger_blocks.blkidfp00 = %s; """ sql_params = (blkidfp00,) diff --git a/src/SummaryFile1.py b/src/SummaryFile1.py index 7105ff4..18164ed 100644 --- a/src/SummaryFile1.py +++ b/src/SummaryFile1.py @@ -60,7 +60,7 @@ class Block: self.coordinates.longitude = (float(geo_record.intptlon) / (10**6)) - def tiger_blkidfp00(self): + def blkidfp00(self): # From the Tiger/Line shapefile documentation: # # Current block identifier; a concatenation of Census 2000 diff --git a/src/Tests/Unit/CensusTest.py b/src/Tests/Unit/CensusTest.py index 3f56afb..dd18e7c 100644 --- a/src/Tests/Unit/CensusTest.py +++ b/src/Tests/Unit/CensusTest.py @@ -30,7 +30,7 @@ class DatabaseTest(unittest.TestCase): fixtures = grp.parse_blocks(fixtures_path) for b in fixtures: - block_boundary = self.cdb.get_block_geometry_as_wkt(b.tiger_blkidfp00()) + block_boundary = self.cdb.get_block_geometry_as_wkt(b.blkidfp00()) contained_population = self.cdb.find_contained_population(block_boundary) error = abs(b.pop100 - contained_population) self.assertTrue(error <= error_threshold)