How can I handle tables with 256+ variables?

Question

I'm working with census data and downloaded several CSV files, each with with 600ish columns/variables. I'd like to store them all in a query-able database, but everything I've tried so far (MS Access, Arc geodatabase table) truncates the table to 256 columns. Are there any solutions for handling large tables that are accessible to someone who isn't a DBA?

Answer 1

PostgreSQL has a column limit of between 250 and 1600 "depending on column types", and supports spatial data and queries with the PostGIS extension. So I would be inclined to do two things:

First, where a column represents a category rather than free text, create a separate table with those categories, and replace the column with an integer ID and foreign key constraint, referencing the category table.

Secondly, break Third Normal Form by splitting the big table into two or more in some logical fashion, and set up a one-to-one relationship between them. This isn't perhaps the most efficient, but if you rarely need some of the data, then the query can just be on the tables you want.

Another completely different alternative would be to use a "NOSQL" database such as MongoDB, CouchDB, and so on. There are no hard-wired limits to "row" size, and if data isn't present for a record, it needn't take up any space.

Spatial support isn't as good for these types of bigtable databases, but MongoDB supports 2D spatial queries and data, and CouchDB appears to have similar functionality.

Answer 2

Short:
My option for data with a lot of attributes or with variable atribute type for each object is to use the KEY/VALUE data model, it can be implemented, and works very well, in sql (i would recommend postgresql+postgis).

Description:
1) You have one table for features, let's say, points. This table holds an ID and the GEOMETRY for each point.

2) You have one more table for the 'attributes' which is the key/value pairs. This table has the columns ID, POINT_ID(FK), KEY(varchar), VALUE(varchar).

Now each point could have virtually infinite attributes stored like that:

ID   POINT_ID   KEY   VALUE
1        1      type     burger shop
2        1      name     SuperBurger
3        1      address  123, a ST.

OpenStreetMaps works like that and works very well, see here and here.

To import the data I would sugest a python script.

Answer 3

I recently dealt with the exact same issue with Statistics Canada census profile CSV files containing 2172 columns. You can import your csv into an ESRI File Geodatabase (FGDB) if you have access to ArcGIS. According to ESRI, the FGDB format can handle 65,534 fields in a feature class or table.

In my case, I was able to import my 2172 column wide CSV file into an FGDB table without any issues.

Once you get the entire table into the FGDB, you can slice it up any way you like (ex. logically or based on db limitations), making sure that you keep a unique id column, to ensure that you can join it back together as needed.