Shortened the name of the "tiger_blkidfp00" method/column to just "blkidfp00".
-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)
"""
cursor.execute(query)
arealand,
areawatr,
total_area,
arealand,
areawatr,
total_area,
pop100,
population_density)
pop100,
population_density)
b.arealand,
b.areawatr,
b.total_area(),
b.arealand,
b.areawatr,
b.total_area(),
b.pop100,
b.population_density())
b.pop100,
b.population_density())
$(PG_BINDIR)/shp2pgsql -I \
-s $(TIGER_SRID) \
data/census2000/maryland/block/tl_2009_24_tabblock00.shp \
$(PG_BINDIR)/shp2pgsql -I \
-s $(TIGER_SRID) \
data/census2000/maryland/block/tl_2009_24_tabblock00.shp \
| psql -U $(DB_USER) -d $(DB_NAME)
psql -d $(DB_NAME) \
| psql -U $(DB_USER) -d $(DB_NAME)
psql -d $(DB_NAME) \
foreign key column before we can create the foreign key
constraint.
*/
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,
CREATE TABLE sf1_blocks (
id SERIAL PRIMARY KEY,
block varchar(4) NOT NULL,
arealand double precision NOT NULL,
areawatr double precision NOT NULL,
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
);
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);
query = """
SELECT population_density
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));
"""
ST_SetSRID(ST_Point(%.6f, %.6f), %d));
"""
#
query = """
SELECT SUM(sf1_blocks.pop100 *
#
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)
) AS covered_population
""" % geometric_object
sql_params = (well_known_text, self.srid)
# Join our two block tables, so that we have both the demographic
# and geometric data.
query += """
# 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 += """
"""
# 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.
""" % geometric_object
# geometric_object hasn't been substituted yet, so we need
# to add the sql_params twice.
cursor = self.connection.cursor()
query = """
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,)
"""
sql_params = (blkidfp00,)
self.coordinates.longitude = (float(geo_record.intptlon) / (10**6))
self.coordinates.longitude = (float(geo_record.intptlon) / (10**6))
- def tiger_blkidfp00(self):
# From the Tiger/Line shapefile documentation:
#
# Current block identifier; a concatenation of Census 2000
# From the Tiger/Line shapefile documentation:
#
# Current block identifier; a concatenation of Census 2000
fixtures = grp.parse_blocks(fixtures_path)
for b in fixtures:
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)
contained_population = self.cdb.find_contained_population(block_boundary)
error = abs(b.pop100 - contained_population)
self.assertTrue(error <= error_threshold)