Added the framework for the PostGIS database integration.

[dead/census-tools.git] / sql / create-sf1_blocks-table.sql

diff --git a/sql/create-sf1_blocks-table.sql b/sql/create-sf1_blocks-table.sql
new file mode 100644 (file)
index 0000000..71d87b5
--- /dev/null
+++ b/sql/create-sf1_blocks-table.sql
@@ -0,0 +1,24 @@
+/*
+  We need to create a unique index on the referenced
+  foreign key column before we can create the foreign key
+  constraint.
+*/
+CREATE UNIQUE INDEX idx_tiger_blkidfp00_unique
+       ON tiger (blkidfp00);
+
+CREATE TABLE sf1_blocks (
+       id                 SERIAL           PRIMARY KEY,
+       state             varchar(2)       NOT NULL,
+       county             varchar(3)       NOT NULL,
+       tract             varchar(6)       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),
+       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);