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?

  • 2
    With any amount of DB Normalization I suspect that these huge tables should be separated into several (or many) smaller tables relating back to their Census unit (block maybe?) UID. – Roy Aug 15 '12 at 17:04

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.

  • 4
    +1 The join solution (paragraph 3) actually can be extremely efficient, because Census data tend to have groups of related fields and for any particular analysis one often needs only a small number of these groups. In this fashion thousands of fields (I do not exaggerate: this is common) can be broken logically across dozens of tables and only a small number of those tables need to be accessed for any particular map or analysis. – whuber Aug 15 '12 at 17:03
  • @MerseyViking, How could him (@scoball) split tables or do the other mentioned operations if he can't import the data into any program that manipulates tables? the data is in CSV. – Pablo Aug 15 '12 at 22:30
  • 2
    @Pablo, I think you're being unfair to MerseyViking: if you are allowed to write a script to import tables--which you essentially are compelled to in order to implement your solution--then so is he, and there is no difficulty in writing one that is completely general and flexible. (I know this from experience because I have done it for extremely large Census databases.) Moreover, he suggests many alternatives that work around the 256 field limitation. – whuber Aug 15 '12 at 22:50
  • "where a column represents a category rather than free text" You have to manually map those columns. – Pablo Aug 15 '12 at 23:13
  • 2
    @Pablo Only if you're using inadequate software :-). The workflow in paragraphs 2-3 can be done with just a few commands using almost any modern statistical program, for instance. (Of course I'm not advocating employing such a program in lieu of a database; I'm just pointing out that with the proper suite of tools, everything in this answer can be accomplished easily and efficiently.) – whuber Aug 16 '12 at 14:14

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.

  • 1
    Interesting! I tried to do an import from csv to file geodatabase. When I was setting it up I looked at the list of variables it was going to import and it stopped listing them after 256 variables, so I didn't proceed. I will take another look. – scoball Aug 15 '12 at 19:28
  • 2
    Check out this link: assets.nhgis.org/How_to_Import_256_Columns_GIS.pdf – Brent Edwards Aug 16 '12 at 13:40
  • File Geodatabases have high limits, so it's possible something happened in the import. – nicksan Aug 21 '12 at 20:53

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).

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:

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.

  • This is often called the "long" form of the data and is good to know about. Although it's ok for flexible storage, it's useless for any kind of multivariate analysis (which would be any analysis comparing two or more attributes). – whuber Aug 15 '12 at 19:36
  • @whuber, it's not useless for multivariate analysis, but indeed you need a very structured software or good programming skills because the data needs to be prepared, specifically, transferred to a table. Here I use the combination of postgis+django (python web framework) to work soil data (ph, al, clay, etc) when I need I put excerpts of the data into tables before processing. This model was chosen because the same structure will process other arbitrary punctual data. – Pablo Aug 15 '12 at 20:00
  • Fair enough: I should have said "useless as is." Provided all the information is retained--and it is--you can always process the data into any format you want. The processing is relatively easy using @MerseyViking's methods compared to the key/value approach. Also, when tables get really large we start getting concerned about total size. The redundancy in key/value storage is so great that it is rarely used for analysis of very large datasets (I can't speak to the frequency of its use purely for storage.) – whuber Aug 15 '12 at 20:08
  • I don't agree with his solution because It's not easy, not to say impossible, to split or manipulate tables if you can't open the data in a database. The user need to send data directly to the database trough a scrip, and with the key/value model you may use the same scrip for any data without the need to map the columns or categorize the attributes. – Pablo Aug 15 '12 at 22:45
  • Your solution seems, by your own admission, to be as programmatically complex as mine - needing "good programming skills". I merely advocated keeping the data in a form that is most efficient for a RDBMS such as PostgreSQL. Besides, it appears to be a moot point because Brent's answer shows the 256 column limit is bogus. – MerseyViking Aug 16 '12 at 21:10

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.