How to obtain Census block data in SQL format?

Question

I am trying to create a web app where users can query a certain type of data that is offered by the Government. Unfortunately, the government prefers "Census Tract/Blocks" as opposed to Zip codes (it makes sense, but still unfortunate)

There are approximately 11 million Census blocks, 73k Census tracts versus 54,000 Zipcodes (these are rough numbers) I was thinking I could simply find a database with census blocks and tracts, and do the mapping myself.

So I found the Census block database, it is a shapefile.

However...I am using a Mac, so I cannot use ArcGIS to view the TIGER shapefiles offered by census.gov (https://www.census.gov/geo/maps-data/data/tiger-geodatabases.html)

If I had ArcGIS, I was thinking I could be able to import the shapefiles and make sure it was the data I needed, but I can't.

I then found a tool called ogr2ogr, but it appears it prefers a database called PostGIS, or SQL Server.

I also keep trying to find maybe someone already went through this craziness, but am unsuccessful.

Given a proprietary dataformat - ERSI Shapefiles - are the only source offered by the Census, how should I go about translating this data to SQL somehow?

This would help me a lot because on my other dataset, there is a field called "FULLFIPSID", which is a FIPS code that the Census uses in it's geography references. Or maybe there is another way without having to use the shapefiles?

Answer 1

You can install PostgreSQL (opensource) and the PostGIS and TIGER extensions to load the TIGER data. This post has a lot of good info that I used to load the census data.

Then you can run SQL queries to get at the data.

I loaded all US Data into PostgreSQL on a Win 7 (64 bit) with 8 GB ram and a 230 GB hard-drive (with other apps installed).... I am just about of of space though with the US data taking up around 100 GB.

Answer 2

If you want to interact with this data using SQL expressions without loading into a database server (PostgreSQL/PostGIS or SQL Server), try loading the shapefiles into QGIS and use the DB manager plugin. There is a node called "Virtual Layers" > "QGIS Layers" then you will see the shapefiles in your map. You can hit the "SQL Window" button and start writing SQL expressions against your shapefile.

However - if you're doing to do more with this, I would highly suggest getting PostgreSQL / PostGIS running and load the data into your database and interact with the data that way... performance will be better than the shapefiles, and you'll be able to do much more complex SQL analysis.