2

I'm working with data from the American Community Survey, provided by the U.S. Census. Specifically, I've downloaded gdb files from here.

I have the gdb file ACS_2016_5YR_TRACT.gdb on my server and I'm attempting to create a psql table with the following:

ogr2ogr -f "PostgreSQL" PG:"host=host port=5432 dbname=db user=user password=password" ACS_2016_5YR_TRACT.gdb -overwrite -progress --config PG_USE_COPY YES

The output I get when running the above is a CREATE TABLE command with over 1600 columns which it tries to execute but fails with:

ERROR:  tables can have at most 1600 columns

Has anybody had success loading ACS5 data onto PostGIS? How could I get around this?

I have to imagine this is a common use case for people working with census data... I'm wondering if there is a way to pass flags to ogr2ogr that would allow it to partition the data? Maybe there's a way to increase the column limit in postgresql? I've tried changing the db settings and recompiling, as suggested in other answers, but haven't had much luck. I'm not sure if that's the best way to go about it though.

  • I attempted this a while back but ran into similar problems. github.com/leehach/census-postgres this was somewhat helpful but also very confusing – ziggy Nov 2 '18 at 19:17
  • @ziggy, thanks for linking me to the code. Seems like it has potential, but I had a lot of questions too. I figured out another method using the gdb files. I added an answer that explains my solution. Please let me know what you think. Thanks. – alfonso Nov 5 '18 at 22:03
4

I would definitely re-think how you store your Census data in PostGIS.

The ArcGIS-ready tables are unnecessarily massive, as you're finding out.

We store our census tables separately with geometry living seperate from non-spatial data from the American FactFinder website.

Geometry tables and columns:

  • Tracts (GEOID and GEOM)
  • BlockGroups (GEOID and GEOM)
  • Blocks (GEOID and GEOM)

Non-spatial tables and columns:

  • Demographics at Blockgroup Level (GEOID plus all data fields)
  • Income at Blockgroup Level (GEOID plus all data fields)
  • American Community Survey 2015 updates (GEOID plus all data fields)
  • etc. etc.

We also created a seperate schema for the Census data tables.

This way, we can create our OWN derivative datasets when we need them, and customize as we need.

Additionally, PostGIS and a spatial/relational database has allowed me to make Peace with the Census data - no more worrying about truncating leading 0's, relating data easily on GEOID, and really understanding the FactFinder website and working with the CSV's.

| improve this answer | |
  • Could you talk a bit about how and from where you collected the data in the non-spatial tables? Earlier, I went about it by making calls to the census API, but I think going about it with ready-made gdb or shp files would be much easier and more reliable. About the spatial data, I collected mine from the Tiger geocoder as a PostGIS extra, as described in the documentation. Thanks. – alfonso Nov 2 '18 at 21:28
  • 3
    Really its a journey into the American FactFinder website - really digging into what you NEED vs. getting 'everything'. We haven't used the API, as manually fetching that data is enough for our planning purposes. Getting these tables in their 'raw' form has proven to be way easier than the pre-cooked GDB's. And yes, the spatial data we collected from TIGER. Let me know if you have any more questions! – DPSSpatial Nov 2 '18 at 21:35
  • I liked your suggestion, but was overwhelmed since I'm collecting data at the national level with cbsa, tract, and block granularity. I decided to go back to the gdb files and I think I have a good solution. Please check out my answer to the question and let me know what you think. I'll report back with how it went. Thanks. – alfonso Nov 5 '18 at 22:01
  • Ahhh an ArcGIS-user downvote... love it... – DPSSpatial Mar 20 '19 at 22:29
2

I figured out a way to get the gdb files into a database. Most of the process can be automated, but I'll have to manually prepare some files.

First, run the ogr2ogr command that turns the gdb file into database tables, but this time skip failures with the -skipfailures flag. Also, be sure to feed it a schema otherwise you'll be overwriting tables if you plan on storing data for multiple years and/or census geographies:

ogr2ogr -f "PostgreSQL" PG:"host=host port=5432 dbname=db user=user password=password" ACS_2016_5YR_TRACT.gdb -overwrite -progress --config PG_USE_COPY YES -skipfailures -lco SCHEMA=acs_2016_5yr_tract

After the command above is run, the gdb data should now mostly be in the database. Check the tables that were created by listing them. Because the tables are named sequentially, you can tell which tables failed to create:

geoservice=> \dt acs_2016_5yr_tract.*
                              List of relations
       Schema       |                Name                 | Type  |  Owner
--------------------+-------------------------------------+-------+----------
 acs_2016_5yr_tract | acs_2016_5yr_tract                  | table | geo_root
 acs_2016_5yr_tract | tract_metadata_2016                 | table | geo_root
 acs_2016_5yr_tract | x00_counts                          | table | geo_root
 acs_2016_5yr_tract | x01_age_and_sex                     | table | geo_root
 acs_2016_5yr_tract | x02_race                            | table | geo_root
 acs_2016_5yr_tract | x03_hispanic_or_latino_origin       | table | geo_root
 acs_2016_5yr_tract | x04_ancestry                        | table | geo_root
 acs_2016_5yr_tract | x06_place_of_birth                  | table | geo_root
 acs_2016_5yr_tract | x09_children_household_relationship | table | geo_root
 acs_2016_5yr_tract | x10_grandparents_grandchildren      | table | geo_root
 acs_2016_5yr_tract | x11_household_family_subfamilies    | table | geo_root
 acs_2016_5yr_tract | x12_marital_status_and_history      | table | geo_root
 acs_2016_5yr_tract | x13_fertility                       | table | geo_root
 acs_2016_5yr_tract | x14_school_enrollment               | table | geo_root
 acs_2016_5yr_tract | x15_educational_attainment          | table | geo_root
 acs_2016_5yr_tract | x16_language_spoken_at_home         | table | geo_root
 acs_2016_5yr_tract | x18_disability                      | table | geo_root
 acs_2016_5yr_tract | x21_veteran_status                  | table | geo_root
 acs_2016_5yr_tract | x22_food_stamps                     | table | geo_root
 acs_2016_5yr_tract | x26_group_quarters                  | table | geo_root
 acs_2016_5yr_tract | x99_imputation                      | table | geo_root
(21 rows)

Next, following the documentation, use ogr2ogr to turn the gdb file into an SQL dump. Remember to give it a schema also. Running the following command will create a SQL file called ACS_2016_5YR_TRACT.sql which contains all the statements to populate the database:

ogr2ogr --config PG_USE_COPY YES -f PGDump ACS_2016_5YR_TRACT.sql ACS_2016_5YR_TRACT.gdb -lco SRID=4269 -lco SCHEMA=acs_2016_5yr_tract

After the SQL dump completes, I found the following grep command useful for listing out all the tables in the resulting sql file:

$ grep -n 'DROP TABLE IF EXISTS' ACS_2016_5YR_TRACT.sql
2:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x00_counts" CASCADE;
74015:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x01_age_and_sex" CASCADE;
148742:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x02_race" CASCADE;
223183:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x03_hispanic_or_latino_origin" CASCADE;
297302:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x04_ancestry" CASCADE;
371975:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x05_foreign_born_citizenship" CASCADE;
447676:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x06_place_of_birth" CASCADE;
522905:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x07_migration" CASCADE;
598706:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x08_commuting" CASCADE;
675255:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x09_children_household_relationship" CASCADE;
749576:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x10_grandparents_grandchildren" CASCADE;
823957:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x11_household_family_subfamilies" CASCADE;
898710:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x12_marital_status_and_history" CASCADE;
973477:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x13_fertility" CASCADE;
1047888:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x14_school_enrollment" CASCADE;
1122675:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x15_educational_attainment" CASCADE;
1197398:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x16_language_spoken_at_home" CASCADE;
1272277:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x17_poverty" CASCADE;
1350226:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x18_disability" CASCADE;
1425127:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x19_income" CASCADE;
1502128:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x20_earnings" CASCADE;
1578319:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x21_veteran_status" CASCADE;
1652892:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x22_food_stamps" CASCADE;
1727143:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x23_employment_status" CASCADE;
1802776:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x24_industry_occupation" CASCADE;
1878891:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x25_housing_characteristics" CASCADE;
1957314:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x26_group_quarters" CASCADE;
2031325:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x27_health_insurance" CASCADE;
2106968:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."x99_imputation" CASCADE;
2181725:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."tract_metadata_2016" CASCADE;
2217196:DROP TABLE IF EXISTS "acs_2016_5yr_tract"."acs_2016_5yr_tract" CASCADE;

Comparing the database table listing and the output from the SQL file grep, we see the first table that failed is x05_foreign_born_citizenship.

Because the grep output above also lists line numbers, we see that lines 371975 through 447675 of ACS_2016_5YR_TRACT.sql contain all the commands to create x05_foreign_born_citizenship. (I confirmed this by opening the file, and you may want to confirm yourself a few times. The file is huge though, so it'll be slow going.)

Using the following sed command, I can write the lines to a separate file, x05.txt, that's smaller and easier to work with:

$ sed '371975,447675!d' ACS_2016_5YR_TRACT.sql > x05.txt

From here on out it's a matter of writing a script that manipulates the x05.txt file. What you'll need to do is break up the one large 1600+ column table into multiple tables. I would offer my own script to the community, but can't because my company owns it. I used Python and pandas to handle it. The files are kind of big, so the server I was on kept on having memory errors. I had to do the work locally on 16GB of RAM. Keep that in mind if you run into memory issues... I could have saved processing power if I didn't use pandas, but it simplified a lot of things. YMMV...


In general terms, here is how I wrote the script:

You'll see the first part of x05.txt is SQL DROP, CREATE, and ALTER statements. The ALTER statements are where the columns are added. My script counted the ALTER statements and applied the first 1000 to x_05_00 table, the second 1000 to x_05_01, etc. So update all the SQL statements and run them to create your tables and define the fields in the database.

One gotcha: Make sure all your subtables have the geoid field.

Next, in x05.txt, you'll come to an SQL COPY statement which lists the column names. You can parse that string into a columns list variable. Back to the file, the next set of lines immediately after the COPY statement is the data itself in tab-delimited form. I wrote all the lines of the data to a csv using the columns list variable as the csv header. (Originally I tried to read all the data into a dataframe, but it's too much to handle in memory - better to write it to disk.)

I then read the lines from the csv into a pandas dataframe in chunks. I was able to slice each chunk by the first 1000 variables, and using .to_sql was able to append the data to the x05_00 table. Similar for the next 1000 variables - those populate the x05_01 table, etc. Iterate through all the chunks, and you're done.

If your script is correct, then you'll have created something like the following. Also, if scripted correctly, the newly created tables will be indexed with default values, etc.

list tables:

geoservice=> \dt acs_2016_5yr_tract.*
                              List of relations
       Schema       |                Name                 | Type  |  Owner
--------------------+-------------------------------------+-------+----------
 acs_2016_5yr_tract | acs_2016_5yr_tract                  | table | geo_root
 acs_2016_5yr_tract | tract_metadata_2016                 | table | geo_root
 acs_2016_5yr_tract | x00_counts                          | table | geo_root
 acs_2016_5yr_tract | x01_age_and_sex                     | table | geo_root
 acs_2016_5yr_tract | x02_race                            | table | geo_root
 acs_2016_5yr_tract | x03_hispanic_or_latino_origin       | table | geo_root
 acs_2016_5yr_tract | x04_ancestry                        | table | geo_root
 acs_2016_5yr_tract | x05_00_foreign_born_citizenship     | table | geo_root
 acs_2016_5yr_tract | x05_01_foreign_born_citizenship     | table | geo_root
 acs_2016_5yr_tract | x06_place_of_birth                  | table | geo_root
 acs_2016_5yr_tract | x09_children_household_relationship | table | geo_root
 acs_2016_5yr_tract | x10_grandparents_grandchildren      | table | geo_root
 acs_2016_5yr_tract | x11_household_family_subfamilies    | table | geo_root
 acs_2016_5yr_tract | x12_marital_status_and_history      | table | geo_root
 acs_2016_5yr_tract | x13_fertility                       | table | geo_root
 acs_2016_5yr_tract | x14_school_enrollment               | table | geo_root
 acs_2016_5yr_tract | x15_educational_attainment          | table | geo_root
 acs_2016_5yr_tract | x16_language_spoken_at_home         | table | geo_root
 acs_2016_5yr_tract | x18_disability                      | table | geo_root
 acs_2016_5yr_tract | x21_veteran_status                  | table | geo_root
 acs_2016_5yr_tract | x22_food_stamps                     | table | geo_root
 acs_2016_5yr_tract | x26_group_quarters                  | table | geo_root
 acs_2016_5yr_tract | x99_imputation                      | table | geo_root
(23 rows)

table description:

geoservice=> \d acs_2016_5yr_tract.x05_00_foreign_born_citizenship
                                            Table "acs_2016_5yr_tract.x05_00_foreign_born_citizenship"
   Column    |         Type          | Collation | Nullable |                                       Default
-------------+-----------------------+-----------+----------+--------------------------------------------------------------------------------------
 objectid    | integer               |           | not null | nextval('acs_2016_5yr_tract.x05_00_foreign_born_citizenship_objectid_seq'::regclass)
 geoid       | character varying(19) |           |          |
 b05001e1    | double precision      |           |          |
  ...
  ...
  ...

 b05006pre12 | double precision      |           |          |
Indexes:
    "x05_00_foreign_born_citizenship_pk" PRIMARY KEY, btree (objectid)


geoservice-> \d acs_2016_5yr_tract.x05_01_foreign_born_citizenship
                                            Table "acs_2016_5yr_tract.x05_01_foreign_born_citizenship"
   Column    |         Type          | Collation | Nullable |                                       Default
-------------+-----------------------+-----------+----------+--------------------------------------------------------------------------------------
 objectid    | integer               |           | not null | nextval('acs_2016_5yr_tract.x05_01_foreign_born_citizenship_objectid_seq'::regclass)
 geoid       | character varying(19) |           |          |
 b05006prm12 | double precision      |           |          |
 ...
 ...
 ...
 b05014m19   | double precision      |           |          |
Indexes:
    "x05_01_foreign_born_citizenship_pk" PRIMARY KEY, btree (objectid)

table counts:

geoservice=> select count(*) from acs_2016_5yr_tract.x05_00_foreign_born_citizenship;
 count
-------
 74001
(1 row)

geoservice=> select count(*) from acs_2016_5yr_tract.x05_01_foreign_born_citizenship;
 count
-------
 74001
(1 row)

UPDATE:

All done - I migrated all the 2016 ACS5 gdb files to a PostGIS database. I had to split the tables into 500 columns each to avoid memory issues during processing.

geoservice=> \dt acs_2016_5yr_tract.*
                              List of relations
       Schema       |                Name                 | Type  |  Owner
--------------------+-------------------------------------+-------+----------
 acs_2016_5yr_tract | acs_2016_5yr_tract                  | table | geo_root
 acs_2016_5yr_tract | tract_metadata_2016                 | table | geo_root
 acs_2016_5yr_tract | x00_counts                          | table | geo_root
 acs_2016_5yr_tract | x01_age_and_sex                     | table | geo_root
 acs_2016_5yr_tract | x02_race                            | table | geo_root
 acs_2016_5yr_tract | x03_hispanic_or_latino_origin       | table | geo_root
 acs_2016_5yr_tract | x04_ancestry                        | table | geo_root
 acs_2016_5yr_tract | x05_00_foreign_born_citizenship     | table | geo_root
 acs_2016_5yr_tract | x05_01_foreign_born_citizenship     | table | geo_root
 acs_2016_5yr_tract | x05_02_foreign_born_citizenship     | table | geo_root
 acs_2016_5yr_tract | x05_03_foreign_born_citizenship     | table | geo_root
 acs_2016_5yr_tract | x06_place_of_birth                  | table | geo_root
 acs_2016_5yr_tract | x07_00_migration                    | table | geo_root
 acs_2016_5yr_tract | x07_01_migration                    | table | geo_root
 acs_2016_5yr_tract | x07_02_migration                    | table | geo_root
 acs_2016_5yr_tract | x07_03_migration                    | table | geo_root
 acs_2016_5yr_tract | x08_00_commuting                    | table | geo_root
 acs_2016_5yr_tract | x08_01_commuting                    | table | geo_root
 acs_2016_5yr_tract | x08_02_commuting                    | table | geo_root
 acs_2016_5yr_tract | x08_03_commuting                    | table | geo_root
 acs_2016_5yr_tract | x08_04_commuting                    | table | geo_root
 acs_2016_5yr_tract | x08_05_commuting                    | table | geo_root
 acs_2016_5yr_tract | x09_children_household_relationship | table | geo_root
 acs_2016_5yr_tract | x10_grandparents_grandchildren      | table | geo_root
 acs_2016_5yr_tract | x11_household_family_subfamilies    | table | geo_root
 acs_2016_5yr_tract | x12_marital_status_and_history      | table | geo_root
 acs_2016_5yr_tract | x13_fertility                       | table | geo_root
 acs_2016_5yr_tract | x14_school_enrollment               | table | geo_root
 acs_2016_5yr_tract | x15_educational_attainment          | table | geo_root
 acs_2016_5yr_tract | x16_language_spoken_at_home         | table | geo_root
 acs_2016_5yr_tract | x17_00_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_01_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_02_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_03_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_04_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_05_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_06_poverty                      | table | geo_root
 acs_2016_5yr_tract | x17_07_poverty                      | table | geo_root
 acs_2016_5yr_tract | x18_disability                      | table | geo_root
 acs_2016_5yr_tract | x19_00_income                       | table | geo_root
 acs_2016_5yr_tract | x19_01_income                       | table | geo_root
 acs_2016_5yr_tract | x19_02_income                       | table | geo_root
 acs_2016_5yr_tract | x19_03_income                       | table | geo_root
 acs_2016_5yr_tract | x19_04_income                       | table | geo_root
 acs_2016_5yr_tract | x19_05_income                       | table | geo_root
 acs_2016_5yr_tract | x20_00_earnings                     | table | geo_root
 acs_2016_5yr_tract | x20_01_earnings                     | table | geo_root
 acs_2016_5yr_tract | x20_02_earnings                     | table | geo_root
 acs_2016_5yr_tract | x20_03_earnings                     | table | geo_root
 acs_2016_5yr_tract | x20_04_earnings                     | table | geo_root
 acs_2016_5yr_tract | x21_veteran_status                  | table | geo_root
 acs_2016_5yr_tract | x22_food_stamps                     | table | geo_root
 acs_2016_5yr_tract | x23_00_employment_status            | table | geo_root
 acs_2016_5yr_tract | x23_01_employment_status            | table | geo_root
 acs_2016_5yr_tract | x23_02_employment_status            | table | geo_root
 acs_2016_5yr_tract | x23_03_employment_status            | table | geo_root
 acs_2016_5yr_tract | x24_00_industry_occupation          | table | geo_root
 acs_2016_5yr_tract | x24_01_industry_occupation          | table | geo_root
 acs_2016_5yr_tract | x24_02_industry_occupation          | table | geo_root
 acs_2016_5yr_tract | x24_03_industry_occupation          | table | geo_root
 acs_2016_5yr_tract | x24_04_industry_occupation          | table | geo_root
 acs_2016_5yr_tract | x25_00_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_01_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_02_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_03_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_04_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_05_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_06_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_07_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x25_08_housing_characteristics      | table | geo_root
 acs_2016_5yr_tract | x26_group_quarters                  | table | geo_root
 acs_2016_5yr_tract | x27_00_health_insurance             | table | geo_root
 acs_2016_5yr_tract | x27_01_health_insurance             | table | geo_root
 acs_2016_5yr_tract | x27_02_health_insurance             | table | geo_root
 acs_2016_5yr_tract | x27_03_health_insurance             | table | geo_root
 acs_2016_5yr_tract | x99_imputation                      | table | geo_root
(76 rows)
| improve this answer | |
  • 2
    Good that works! Would recommend planning for 2020 data!! – DPSSpatial Nov 6 '18 at 1:45
  • I like this workflow so far. I ran the 1st ogr2ogr command where it inserts the FileGDB into postgres with skip failures. I just created the sql dump file but how do you determine how many columns each TABLE is supposed to have? – ziggy Nov 6 '18 at 1:45
  • @ziggy, not sure what you mean. I'm using postgres which limits me to 1600 columns, so I'm going to break the large 4000+ column table into smaller tables of probably 1000 columns. The files are huge though, so I've been struggling. When I finish, I'll update my answer with some code samples of how I did it. – alfonso Nov 7 '18 at 3:58
  • @ziggy: I updated my answer with some more details. Sorry I couldn't give the exact script, but I leave things off at a good place to work off of. If you have specific questions, I can answer them here or in a separate Python question on StackOverflow. – alfonso Nov 9 '18 at 19:22
  • 1
    @alfonso awesome, this is very helpful good work – ziggy Nov 13 '18 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.