sql/combine-block-tables.sql

   1 /*
   2   Combine the sf1_blocks and tiger_blocks tables into one
   3   comprehensive "blocks" table. Once we're done, we drop
   4   sf1_blocks and tiger_blocks.
   5 */
   6 INSERT INTO blocks
   7        (SELECT sf1_blocks.id,
   8                sf1_blocks.state,
   9                sf1_blocks.county,
  10                sf1_blocks.tract,
  11                sf1_blocks.block,
  12                sf1_blocks.blkidfp00,
  13                sf1_blocks.pop100,
  14                sf1_blocks.total_area,
  15                sf1_blocks.population_density,
  16                tiger_blocks.the_geom
  17         FROM sf1_blocks INNER JOIN tiger_blocks
  18                         ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00);
  19
  20 DROP TABLE sf1_blocks;
  21 DROP TABLE tiger_blocks;
  22
  23 /* We have to re-create the GiST index on the new table as well. */
  24 CREATE INDEX idx_geometry_gist ON blocks USING gist(the_geom);
  25
  26 /* And generate some statistics. */
  27 ANALYZE blocks;